0

While exploring the data from system health extended events for event so server diagnostics I see for column query processing XML as below:

Max workers - 2944 ( based on 80 schedulers on my server)

Max workers created - 3097

Max workers available/idle - 2262

Pending tasks - 89

MS reports this as warning if there is any pending tasks greater than 1.

I am not able to understand this data from system health events that why there would be pending tasks when worker threads are already available as shown above and how are workers created more than maxworkers available as 2944 compared to created as 3097

And when should it be a concern or I should say better to interpret this information from query processing.

Update--

Wait stats since 04/20

CXPACKET  - 26.8%
PREEMPTIVE_OS_ENCRYPT_MESSAGE - 18.24%
LATCH_EX  -  17.9%

For threadpool

waiting task count=10407
wait time ms=29989ms
max wait time=641ms
signal wait time =1ms

Thanks

3

Your numbers don't totally make sense. In particular, the value for "workersCreated" should not be higher than the value for "maxWorkers." Here's an example of a normal-looking entry:

<queryProcessing maxWorkers="576" 
                 workersCreated="86" 
                 workersIdle="46" 
                 tasksCompletedWithinInterval="950" 
                 pendingTasks="0" 
                 oldestPendingTaskWaitingTime="0" 
                 hasUnresolvableDeadlockOccurred="0" 
                 hasDeadlockedSchedulersOccurred="0" 
                 trackingNonYieldingScheduler="0x0">

In your case, you've hit the max worker threads limit (as evidenced by the THREADPOOL waits in sys.dm_os_wait_stats). Investigating those is a pretty big task, and probably tough to cover in this Q&A format. This is what's causing tasks to be pending though. Generally, you'll need to

  • Fix blocking issues (if that's what's causing threads to tie up schedulers)
  • Reduce parallelism (by reducing MAXDOP or CTFP) - this is kind of a band-aid fix
  • Change the app to run less queries at a time

Based on other questions you've asked on the site, I imagine that blocking and long-running transactions are the reason you're hitting this. You'll need to continue to work with the vendor.


By the way, you can double check that the "max worker threads" setting hasn't been modified by running:

EXEC sp_configure @configname = 'max worker threads';

It should show the run_value as 0 if it hasn't been changed.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.