I am tuning the DB activity for an application that sends a high number of transactions in a very short space of time via a large number of threads.
When activity is highest I am seeing:
"SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems."
and related connection errors on the application. Eventually the DB becomes unresponsive and I need to use the DAC to get a connection. Investigating DMVs I find that the highest WAIT stat is THREADPOOL (at 50% of resource WAITS) and SIGNAL WAITS are only < 8%
This led me to investigate sys.dm_os_schedulers, as it looked like possibe worker starvation, but I am struggling to understand the results I get:
'SELECT scheduler_id ,
WHERE scheduler_id < 255
AND runnable_tasks_count > 0'
Shows only about 120 active workers, but 78 queued (see below)
0 255 VISIBLE ONLINE 1 0 97 9 61 61 36 0 188
1 255 VISIBLE ONLINE 1 0 100 20 58 55 42 2 189
in fact 'select count(0) from sys.dm_os_schedulers' is never more than 130
Can anyone explain why the DB seems to become starved of workers when only half of the workers are allocated?
This environment is a 2 vCPU VM running Windows Server 2003 and SQL 2005 SP3, it is running on a HOST with 4 cores.