January 20, 2011 at 6:33 am
Hi all
Using the techniques described here:
http://technet.microsoft.com/en-us/library/cc966540.aspx
I have been looking at wait stats and can see we have very high CXPACKET waits. I am now wanting to figure out what queries are being run that cause these waits over the course of the day (as an example) such that I can look at their execution plans and start to tune them.
My question is I have not been able to find anything that takes snapshots or fires to record the infromation about the SPID when a particular wait type occurs. Is there anything out there already? Is my investigation technique incorrect?
Thanks
January 20, 2011 at 7:29 am
You basically can do two things, sample the waiting processes regularly during the day to see if you can catch a query waiting because of cxpacket or set up a server-side trace and capture long running queries and start tuning them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 20, 2011 at 8:15 am
Oh right yes if I try to capture those queries running longer than the cost threshold then I know which ones will be running parallelism, correct?
January 20, 2011 at 8:22 am
I actually just, just found another way you can do it. You can use extended events to capture all wait states on procedures as they occur. I don't have the link handy, but lookup MCM video's from Paul Randal. He shows exactly that. I'm going to be going through it myself.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 20, 2011 at 2:13 pm
ooo ok, I will have a look thanks 🙂
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply