SQL Server 2012 Not Using Parallel Cores for Parallel Connections - Scheduler Problem?

Question

I have a web system using SQL Server 2012 Enterprise Edition as a backend.

One of our queries is particularly heavy taking around 60 seconds. I have analysed this query heavily and know that the time is spent in a INSERT INTO tablevariable SELECT FROM table. This means that there should be a shared lock on the table but parallel processes should be able to run this query in parallel.

One web endpoint calls 3 of this procedures in parallel on separate processes. Since we have 4 cores I would expect that SQL server should run one process on a core so 3 run simultaneously giving us query times of approximately 60 seconds.

However most of the time it will switch between running 2 of the processes and then 1 of the processes around every 20-30 seconds. I can see the CPU jump between 25% and 50% usage (since a 4 core machine).

The processes that aren't running are in the RUNNABLE state so it seems they are just waiting on CPU time, no table locks causing the issue.

Occaisionally it will actually only run 1 at a time, and occasionally all 3 simultaneously so something is causing some decision making.

I have checked that the licensing sees all 4 cores (it does) and the processor affinity is set to use all. Since this isn't within a query I don't expect MAX_DOP to impact this but I have tried it at 0 and 2.

Any idea what influences SQL servers decision making here? and if we can change this behaviour?

Answer 1

First, inserting data into a table variable is single-threaded (not parallel). You can learn more about that in Paul White's epic post Fording a Parallel Execution Plan.

Second, when you're running multiple queries, you can't predict which queries will end up on which cores. An incoming query gets assigned to a worker, which is pinned to a scheduler, which runs on a CPU core. SQL Server doesn't know in advance how complex the query (or queries) will be, so it can't predictively load balance. If you truly want to get the CPU to 100%, you'll want to run a lot more than 3 queries, or you'll want to check DMVs to abort your queries if they end up on the same scheduler.

Or do what I would do - stop using table variables and switch to a temp table instead.