CPU Vs schedulers Vs Workers Vs threads in SQL server

Question

Hi , I m bit confused about schedulers Vs Workers Vs threads in SQL server . Can any one please help me to understand ?.

Answer 1

For each logical CPU shown in Windows the SQLOS will create a SOS_Scheduler in SQL Server.  Workers are related to threads in the system and process tasks inside of SQL Server.  The number of workers created is dynamic and based on the load, but the maximum number of worker threads that SQL Server will use is based on the number of schedulers by default.  On a 64 bit system the max_worker_threads value is calculated if the number of schedulers exceeds 4, otherwise a base value of 512 is used.  If you have more than 4 schedulers, then the value is ((NumSchedulers-4)*16) +512.  You can see the value for this in the sys.dm_os_sys_info DMV.

---

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

Answer 2

Thanks for short and clean answer.

I might need bit more about this..How SOS_Scheduler separates and associate workers among all schedulers.

If One scheduler can have multiple worker threads .

Why not Two workers within the same scheduler  can not run parallel (parallelism)?  (might be one worker only active at the same time?).

May be I totally understood wrongly .. Please share the posts or articles which explains this things.

Answer 3

First start off by reading the Execution Model (simplified) section of this Whitepaper:

http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc

Then you will need to read through pretty much everything Slava Oks wrote about scheduling:

http://blogs.msdn.com/b/slavao/archive/tags/sql+server+scheduling/

specifically:

http://blogs.msdn.com/b/slavao/archive/2005/07/20/441058.aspx

and some background on where this all originated by Ken Henderson:

http://blogs.msdn.com/b/khen1234/archive/2005/11/07/489778.aspx

and to go beyond this, you will need to purchase books that cover the internals of SQLOS and SQL Server execution like:

http://www.amazon.com/SQL-Server-2005-Practical-Troubleshooting/dp/0321447743/ref=sr_1_1?ie=UTF8&qid=1322507206&sr=8-1

http://www.amazon.com/Professional-Server-2008-Internals-Troubleshooting/dp/0470484284

---

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

Answer 4

Thanks Jonathan for your inputs.

---

Mahadevan N

Answer 5

Hi Jonathan,

Great post.  

Can you clarify something for me?  I have a box running SQL Server 2008 R2.  sys.dm_os_info returns  a cpu_count of 2 and a scheduler_count of 2.  However, when I query sys.dm_os_schedulers I get 15 rows returned, with the results being pretty much evenly divided between cpu 0 and 1.  I had thought the ratio of scheduler to cpu is 1 to 1 (confirmed by sys.dm_os_info), so shouldn't sys.dm_os_schedulers only return 2 rows?

Answer 6

Hi Jonathan,

Great post.  

Can you clarify something for me?  I have a box running SQL Server 2008 R2.  sys.dm_os_info returns  a cpu_count of 2 and a scheduler_count of 2.  However, when I query sys.dm_os_schedulers I get 15 rows returned, with the results being pretty much evenly divided between cpu 0 and 1.  I had thought the ratio of scheduler to cpu is 1 to 1 (confirmed by sys.dm_os_info), so shouldn't sys.dm_os_schedulers only return 2 rows?

No.  VISIBLE ONLINE schedulers will map 1 to 1, but you have background tasks like Resource Monitor that run on a HIDDEN ONLINE scheduler, to provide dedicated resources to that system task.  You can see this by joining dm_exec_requests to dm_os_schedulers by scheduler_id:

SELECT 
er.session_id, 
er.command,
es.is_user_process,
s.scheduler_id,
s.cpu_id,
s.status
FROM sys.dm_exec_requests AS er
INNER JOIN sys.dm_exec_sessions AS es
ON er.session_id = es.session_id
INNER JOIN sys.dm_os_schedulers AS s
ON er.scheduler_id = s.scheduler_id

---

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

Answer 7

All clear, thanks!