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

Stop Profiling

Wayne Sheffield concludes his two-part series on converting a trace to use Extended Events: This query extracts the data from XE file target. It also calculates the end date, and displays both the internal and user-friendly names for the resource_type and mode columns – the internal values are what the trace was returning. For a […]

Read More

Breakpoint Extended Event

Arun Sirpal is a dangerous man of mystery and danger, but mostly danger: I did a dangerous thing, and I want to make sure that YOU DO NOT do the same. I was creating a couple of extended events sessions and was playing around with some actions. I ended up with the following code where I […]

Read More

Categories

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