How To Spot Parallelism In SQL Server

What does parallelism look like in SQL? How would you know when SQL is doing it parallel style? When looking at sys.sysprocesses you might see something like the below,

CXPACKET_SYSSYSPROCESSES

Here we see many instances of the CXPACKET waittype running seemingly from one spid. The CXPACKET waittype pops up whenever a thread running in parallel is waiting on another thread to complete before the overall process can complete. If you’re seeing CXPACKETs then you’re seeing parallelism. If you see CXPACKETs persist, then there might be an issue slowing down your query, but thats for another story . . . (more info on this waittype here)

The most obvious way to see parallelism is in the query plan. As shown below,

parallel_query_plan

You see this, you’re seeing parallelism :) Painless.

Leave a Reply

Your email address will not be published. Required fields are marked *