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:
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.
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.
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.
In the third example, isn't the number of schedulers 8, not 4, as MIN(8,8) is 8?
PingBack from http://paidsurveyshub.info/story.php?id=75998