SQL SERVER – OS Threads Used by SQL Scheduler

Today we will answer the question asked by my client during  Comprehensive Database Performance Health Check, about OS Threads Used by SQL Scheduler. Well, let us see a simple script for the same, which I had earlier blogged SQL SERVER – Simple Script to Match Session ID to OS Thread ID.

SELECT osTask.session_id, osThreads.os_thread_id,
osTask.scheduler_id, osTask.task_state
FROM sys.dm_os_tasks AS osTask
INNER JOIN sys.dm_os_threads AS osThreads
ON osTask.worker_address = osThreads.worker_address
WHERE osTask.session_id IS NOT NULL
ORDER BY osTask.session_id;
GO

When you run the script above it will give you a result similar to the following image.

Using this simple script you can easily figure out which OS Threads are used by SQL Server. Additionally, you can know the scheduler_id and session_id along with it. Well, a short blog post for today. Let me know your thoughts about it in the comments section. I try to reply to each comment.

Let me know if you are interested to know more about this topic. I will write more blogs as well as create an SQL in Sixty Seconds video.

Here are my few recent videos and I would like to know what is your feedback about them.

• Bitwise Puzzle – SQL in Sixty Seconds 160
• Find Expensive Queries – SQL in Sixty Seconds #159
• Case-Sensitive Search – SQL in Sixty Seconds #158
• Wait Stats for Performance – SQL in Sixty Seconds #157
• Multiple Backup Copies Stripped – SQL in Sixty Seconds #156

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