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?