Your queries could be going parallel, but because of limitations in SQL Server, they staying serial: single-threaded. A lot of different things can cause this, including running SQL Server Express Edition (which only uses 1 CPU core), but the most common are Scalar UDFs.
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.
Don’t Use Scalar User-Defined Functions in Computed Columns.
Scalar Functions ALWAYS cause any queries using them to run single-threaded.
Scalar functions in computed columns cause all queries that hit that table to execute serially.
Scalar functions in computed columns cause index maintenance to go single-threaded.
They cause DBCC CHECKDB to go single-threaded, too.
Comments