Multiple Core and Hyper-Threading enabled CPUs and SQL Server 2005
One colleague recently asked me, is the information in this article 914278 (How SQL Server 2005 Express Edition determines the CPU count and uses the CPUs during processing) was also applicable to the Standard Edition of SQL Server 2005?
And following is what I answered to him.
The short answer is: Yes, it is applicable to the Standard Edition as well, but Standard is not limited to one physical CPUs but four. And moreover, this is applicable to all Editions of SQL Server 2005.
The long answer is the following:
At startup time, one of the things the server does is loading its configuration settings. As part of that process, it calculates the maximum number of schedulers SQL Server can create. In order to calculate that value, it follows these steps:
It looks for the number of processors in the system (SYSTEM_INFO.dwNumberofProcessors), which includes all processors whether they are physical or logical.
ValueFromStepOne = SYSTEM_INFO.dwNumberofProcessorsThen it resolves the following expression:
ValueFromStepTwo = min (MaxCPUsPerEdition * LogicalProcessorsPerPhysicalProcessor, MAXCPU)
where the different values for MaxCPUsPerEdition are enumerated in a table below, LogicalProcessorsPerPhysicalProcessor is obtained by using the Windows API GetLogicalProcessorInformation (in platforms supported by SQL Server 2005, but where this OS API is not supported, we directly CPUID), and MAXCPU equals 32 if it's SQL Server for x86 platform or 128 if it's for 64 bit platforms.
And, finally, the following expression is calculated:
MaxNumberOfVisibleSchedulers = min (ValueFromStepOne, ValueFromStepTwo)
| Edition | MaxCPUsPerEdition |
| Express | 1 |
| Personal/Workgroup | 2 |
| Standard/Small Business Server | 4 |
| Enterprise/Developer/Evaluation | 32 (if x86 build) / 128 (if x64 build) |
Now let me illustrate it with three examples:
First Example: SQL Server 2005 Enterprise Edition 32bit, running on a Windows Server 2003 32-bit with two physical processors with Hyper-Threading enabled.
ValueFromStepOne = 4
ValueFromStepTwo = 32 --> min (32 * 2, 32)
MaxNumberOfVisibleSchedulers = 4 --> min (4, 32)
Second Example: SQL Server 2005 Express 32bit, running on a Windows XP 32bit with two physical processors with Hyper-Threading enabled.
ValueFromStepOne = 4
ValueFromStepTwo = 2 --> min (1 * 2, 32)
MaxNumberOfVisibleSchedulers = 2 --> min (4, 2)
Third Example: SQL Server 2005 Standard 32-bit, running on a Windows Server 2003 32-bit with four physical processors with Hyper-Threading enabled.
ValueFromStepOne = 8
ValueFromStepTwo = 8 --> min (4 * 2, 32)
MaxNumberOfVisibleSchedulers = 4 --> min (8, 8)
To simplify the explanation, I haven't talked about the affect of "affinity mask" in all this story. But, to put it simple, it can only restrict even more whichever number we calculated for MaxNumberOfVisibleSchedulers.
If you're interested in determining the actual number of visible schedulers (excluding the one dedicated to DAC) you're instance of SQL Server has created, you can use the following DMV:
select count(*) [Visible Schedulers] from sys.dm_os_schedulers where status like '%VISIBLE%' and scheduler_id < 255
Visible Schedulers
------------------
2
I hope you found this post interesting.
Comments
Anonymous
September 22, 2008
I have a SQL 2005 standard running in a 4 CPU dualcore and the engine actually sees the 8 cores. In fact, I ran the query you provided and it shows 8 visible schedulers. This means the standard version is limited to 4 physical CPUs and not to just 4 cores. cheers ChrisAnonymous
October 29, 2008
In the third example, isn't the number of schedulers 8, not 4, as MIN(8,8) is 8?Anonymous
June 01, 2009
PingBack from http://paidsurveyshub.info/story.php?id=75998