SQL SERVER – Threads Not Counted for Max Worker Threads

I am often surprised how I must evolve to support my clients at Comprehensive Database Performance Health Check. I had a very interesting situation where I had to find all the threads which are not counted for max worker threads in SQL Server. Let us see the script today.

Here is the script:

SELECT r.command,
w.last_wait_type,
COUNT(*) TotalCount
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
INNER JOIN sys.dm_os_tasks t ON r.task_address = t.task_address
INNER JOIN sys.dm_os_workers w ON t.worker_address = w.worker_address
WHERE s.is_user_process = 0
GROUP BY r.command,
w.last_wait_type
ORDER BY TotalCount DESC

When you run the script above it will list all the commands and wait for types with the thread counts. However, this thread count is not counted against your Max Worker Threads as they are primarily system processes and not user processes.

Remember it is not always the case when your user queries takes up the resources in the SQL Server, there have been cases when system processes also taking the critical resources away from your system.

Here are my few recent videos and I would like to know what is your feedback about them. Do not forget to subscribe SQL in Sixty Seconds series.

• MAX Columns Ever Existed in Table – SQL in Sixty Seconds #182
• Tuning Query Cost 100% – SQL in Sixty Seconds #181
• Queries Using Specific Index – SQL in Sixty Seconds #180
• Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
• One Scan for 3 Count Sum – SQL in Sixty Seconds #178
• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176

Reference: Pinal Dave (http://blog.SQLAuthority.com)