Sql-server – Concurrent requests not running in parallel

concurrencysql serversql server 2014statistics

I am trying to run update statistics in parallel.

Say I start with 2 sessions: S1, S2

In S1 I run
Update Statistics A with fullscan and time it to take 2 minutes.

In S2 I run Update Statistics B with fullscan and time it to take 2 minutes also.

Now I run both in parallel and I can see that both finish in around 2 minutes as expected.

Now I add a new session S3 (this issue doesnt just happen with the 3rd session or whatever, i'm not really sure when exactly it occurs but I have noticed it)

In S3 I run Update Statistics B with fullscan and time it to take 2 minutes also.

I run S1 AND S3 in parallel, and now notice both queries are taking 4 minutes to complete. If I run S1 and S2 in parallel instead, now it takes 2 minutes for both queries. If I run S2 and S3 in parallel, it takes 2 minutes. Basically it seems like there is some kind of restriction where it does not let S1 and S3 execute requests at the same time, regardless of the query. I have verified this by checking sys.dm_exec_requests while the two queries are executing, S1 and S3 keep alternating between running and runnable/suspended with only one query running at once. Any other combination of sessions has both sessions running at the same time.

I can't see any kind of blocking as well going on here.

What can cause this issue?

Best Answer

Think back to the bad old days when everyone's computer had a single CPU with just one core. If you ran a program that took 2 minutes on its own at the same time as another one that took 2 minutes on its own then both of them combined might take 4 minutes. There's only so many CPU cycles to go around after all and the two programs would be competing for resources. The same thing can happen in SQL Server if you throw too much work at it relative to your server size or if you get unlucky.

Based on your description of the problem, the most likely explanation is that session 1 and session 3 are running at MAXDOP 1 and they end up on the same CPU core. Session 2 is on a different CPU core. That could be why performance degrades when you run sessions 1 and 3 at the same time but running 1 and 2 at the same time leads to no degradation. To validate that, the wait type that you want to look for is SOS_SCHEDULER_YIELD. While the queries are running, you can also use the sys.dm_exec_requests DMV:

SELECT session_id, scheduler_id
FROM sys.dm_exec_requests;

If your two sessions end up on the same scheduler_id (a scheduler maps back to a logical core) then it's reasonable to expect that both sessions will take twice as long as they did when you were just running one query at a time. This is a bit more complicated if you have hyperthreading enabled and exposed to SQL Server, but the same kind of behavior can be observed. You would want to look at sys.dm_os_schedulers as well because you could see a similar degradation on different schedulers if those schedules are on the same physical core.

If you want to get into the details for scheduling, you could start by reading this blog post. A small quote:

Scheduling assignment starts at the NUMA node level. The basic algorithm is a round robin assignment for new connections. As each NEW connection arrives it is assigned to a scheduler based on round robin, NUMA node connection assignment.

If your server has NUMA nodes that consist of just one scheduler then you could easily get yourself into a situation where queries get doubled up unnecessarily. The fix would be to change your NUMA configuration or to run your queries in different sessions until you get the desired behavior.

Related Question