SQL Server Performance Top Tip: SQL Server User Mode Scheduler (UMS) – understanding it can explain some odd performance
When performing batch jobs where you wish to run processes in parallel it is important to understand how the scheduler works.
You can get a description of what the scheduler does by looking under the title ‘SQL Server Task Scheduling’ in Books on Line. However what you need to know is not at all obvious from reading this description. What you need to know are these five facts.
When an application talks to SQL Server it gets assigned a System Process ID (SPID)
1) SPID gets assigned to a UMS
2) There is one UMS per processor
3) A UMS schedules across Processors
4) A SPID stays on UMS for life of thread
5) Two busy SPIDS may fight on the same UMS
You now have the five essential facts, but do you have a useful conclusion? I’m not going to tease you, I shall merely illustrate the implied gotcha.
You have a 4 processor box and a bcp job to run. The job takes 10 processor minutes to run – how long does it take 4 bcp processes (each bound to a separate UMS) to process the job? Pretty easy isn’t it - 2.5 minutes (were obviously in an ideal world here).
So now imagine you’re a very enthusiastic DBA and realize the processors aren’t working to the max. You decide next time you’ll set everything up so that you can run five bcp processes and hopefully make better use of your processes. How long do you think the job will take with five bcp processes? Two minutes? Nope, more like 4 minutes.
Why? Well two of the bcp processes are going to get permanently stuck on one processor. Thus if the work is divided between each spid this will give two minutes of work to each spid. The two spids sharing one processor are now only going to get half the processor time, therefore it will take them twice as long to run – four minutes. QED, five spids thus take considerably longer to run than four
To get a deep understanding of the UMS have a look at the following:
http://msdn.microsoft.com/SQL/sqlarchitecture/default.aspx?pull=/library/en-us/dnsqldev/html/sqldev_02252004.asp?frame=true