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)