There are lots of great 3rd party monitoring tools on the market from a variety of vendors. I want to start this post by saying up front that I’m not bashing them, or throwing stones. That said…
Some of the time these third party apps don’t handle reporting CXPACKET waits very well. The reason for this, usually, is that the third party apps typically show the waittype of the thread which has been waiting the longest for that session. Often this will be the CXPACKET thread. Because of this it can be hard to use the 3rd party apps to find the actual root cause of the problem.
To find the root cause of these problems which look like CXPACKET problems you need to find the threads which aren’t waiting on CXPACKET. There are a few ways you can do this, probably the easiest is to query the sys.sysprocesses dynamic management view. While this view is deprecated it isn’t going anywhere yet and when it does you can still get this information from the other DMVs. Simply query sys.sysprocesses (or dbo.sysprocesses if you are on SQL 2000) using the problem SPID as the WHERE clause.
[sql]SELECT *
FROM sys.sysprocesses
WHERE SPID = {Some Session ID}[/sql]
You can then look at all the wait types that are being reported for that SPID and what other the other wait types are is the problem. Typically when I see this it’s usually an IO wait type or SOS_SCHEDULER_YIELD. If the problem is SOS_SCHEDULER_YIELD then (in my experience) you’ve probably got an indexing problem that needs to be resolved. You’ll then want to grab the execution plan for that query (sp_whoisactive is a great way to get the execution plan using the @get_plans=1 parameter) and figure out the missing index problem.
Now there can be other reasons that you’ll be CXPACKETed, so please don’t think that this will work every time. But it should work a lot of the time.
Denny
