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

Data-Driven Fill Factors

Tracy Boggiano has an Extended Event and a procedure to track page splits and then modify index-level fill factors accordingly: So what is the solution?  Well it really depends.  But the solution I wrote walks you through Setting the default fill factor Tracking page splits Lowering the fill factor Read on to learn more.

Read More

How Long Was That Event?

Taiob Ali shows that some extended event times are in milliseconds, some are in microseconds, and some are in unknownaseconds: There was a question in dba.stackexchange.com titled “module_end extended events duration in microseconds?” that I answered and it was Microsecond in that instance. Later on I questioned myself if the duration is always in Microseconds for extended […]

Read More

Categories

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