Your queries could be going parallel, but because of limitations in SQL Server, they staying serial: signle-threaded. A lot of different things can cause this, but the most common are Scalar UDFs.

Paul White’s excellent blog post “Forcing a Parallel Query Execution Plan” has a nice list of things that frequently cause serial plans, or serial zones in a plan. These include T-SQL scalar functions, multi-statement table-valued functions, TOP, and more.

For more information, check out the Functions module of my Mastering Query Tuning class.

Generally, there’s not a server-level fix for these: you’re stuck rewriting the query in a way that will let it go parallel, like:

  • Refactoring scalar UDFs by putting the code directly into your query
  • Rewriting multi-statement table-valued UDFs into single-statement ones (this gets tricky)
  • Breaking a large query up into portions where a small part stays serial, but the bulk of the work can go parallel

This is hard work, so before you start, take a quick look at the query’s metrics to see whether it merits this kind of rewrite. For example, if it’s taking less than 1 second of CPU time each time it runs, and it runs less than 2 seconds, and you only call it every few minutes, the rewrite may not be worth the effort. On the other hand, if it runs frequently and takes tens of seconds of CPU time, you can probably get faster completion by going multi-threaded.

Menu
{"cart_token":"","hash":"","cart_data":""}