MAXDOP-Related Extended Events

Paul Randal looks at a couple of Extended Events which help you find CXPACKET waits and degrees of parallelism:

Now I’ll set up a simple Extended Events session to track down the offending code (based on the query from here). It’s very important that you query the sys.dm_xe_map_values DMV to find the correct number to use in the query for the CXPACKET wait, as these numbers often change from release to release, and even in Service Packs. For instance, CXPACKET was 191 in SQL Server 2014 RTM, but is 190 in the 2014 build I’m using.

Be very careful about running this in production, as the event will fire for *every* wait that occurs and so will likely affect your workload throughput, even though it’ll short-circuit if the wait isn’t CXPACKET. I’ll show you a better event to use lower down.

These are good events to know.

Related Posts

Finding Failed Queries

Andrew Pruski shows how to use extended events to find queries with errors: What this is going to do is create an extended event that will automatically startup when the SQL instance starts and capture all errors recorded that have a severity level greater than 10. Full documentation on severity levels can be found here but […]

Read More

Extended Event Handler Application

Dave Mason whipped up an application to track when a particular extended event fires: Event Responses: when an event is handled, specify how you want to respond to it. In the “Event Responses” section, there are two options: play a system sound or send email (you must choose at least one option). Pick the system […]

Read More

Categories

February 2017
MTWTFSS
« Jan Mar »
 12345
6789101112
13141516171819
20212223242526
2728