Looking out for… Parallelism
While closely monitoring a query that I was running, I observed some strange behaviour by another query. This other query quickly beat mine in terms of Pages read, which is fine, no competition. It went on to exceed it 4 x. The thing that stuck out was the “Degree of Parallelism” that it used – “1”.
For such a “large” query, this struck me as being a bit odd. My query used “12”. Again, not that it mattered.
Now, I will say that I don’t normally take much notice of how many Processors that are used by running queries because… because that information isn’t generally readily available. The fact is that I was monitoring a different query specifically to see what the Degree of Parallelism was and I was using SentryOne as it shows that detail. The Server that I was working on had 48 Cores, so Parallelism should have certainly been a thing.
Upon examination, the “rogue” query was using an inline scalar function. This is one of a series of gotchas that prevent SQL Server from running a query in parallel.
In this case, the function was just returning a single value every time it ran. This meant that the query could be modified to calculate this value at the beginning and store it in a variable. A simple action like that meant that the Query was then able to go parallel, utilise more of the resources available on the Server and ultimately return results far more quickly.
I often use the excellent “sp_whoisactive” for checking running queries. This provides lots of useful information about what’s running, the resources being used and any blocking issues while filtering out a lot of noise. Unfortunately, it doesn’t provide information about the number of Processors that a query is using. I’m sure that there are good reasons for this information not being included as I would imagine it would change over the lifetime of the query.
There are lots of really good articles about parallelism – how it works, the mechanics of how the Query Processor determines when a query should go parallel and also reasons why a query would not go parallel. I’ve provided links at the bottom of this page – please feel free to suggest more.
The main reason for this article is just to raise awareness.
- Just because a Server has many Processor Cores doesn’t mean that a big query will use them
- Keep a check on how your query runs, not just how long it takes
- SQL Server DBAs can be useful – they spot issues and raise them (politely)
Many years ago, I worked with a chap called Gareth. He mysteriously worked for “systems” and to be honest no-one understood what he did. Someone asked him one day what he was up to and he said “just strolling around WAS” (WAS was one of the systems that he looked after) and these days, more and more I understand.
Links
Reasons why SQL Server may not parallelize a Query
Pinal Dave – how to tell if Queries are run in parallel
