About a week ago, I started receiving alerts from our SQL monitoring tool indicating heavy blocking on the dbo.SysLog table. The blocking exists because of a parallel query which is doing a table scan:
SPID at Head of Blocking Chain:
SPID [ecid]: 135 
Client Machine: (Our Solarwinds Server)
Application: Orion Syslog Service
Login Name: SolarWinds
Last Batch: 9/7/2010 6:55:29 PM
Wait Type: CXPACKET << Indicates that the parallel query is waiting on one of the threads to finish
Wait Time: 00:00:00 << Ignore this; it represents how long *this* query has been waiting.
UPDATE SysLog SET Hostname = @Hostname
WHERE (DateTime >= @DateTime)
AND (IP = @IP)
AND (Hostname = @IP)
AND (EngineID = @EngineID )
We're not aware of any changes made in the last week, and so we suspect that one of two things is going on here:
- The query is running again and again for some reason, and wasn't running previously, or
- The query has always run on a regular basis, and the data volume in the dbo.SysLog table has caused the SQL Server query optimizer to select a different query plan (a parallel one, which isn't always the best choice).
I'm leaning towards the latter. I have a few ways that I can deal with it:
- I can set the MAXDOP setting on the SQL instance to 1, prohibiting parallel query plans altogether. This is a bit extreme, so I'd rather avoid it.
- I can create a covering index so the query runs much more efficiently. Probably the best option, but this is a big table, and I'd rather not add an extra GB of index bloat. Also, a new index can occasionally adversely affect other queries which aren't currently running into trouble
- I can more frequently delete history from the SysLog table and hope my Network Engineer doesn't notice ;)
- I can break out the table and/or indexes into multiple file groups, so the table scan runs more quickly. It'll still be doing a table scan though, so this is probably just delaying the inevitable.