How many CPU cores can SQL Server use?
SQL Server and CPU core usage
How many CPU cores can SQL Server support should be a simple question, you might think. However, judging by how often this question is asked on the SQL Server forums its clear that there is a lot of confusion about exactly how many CPUs a particular edition of SQL Server can use.
The problem is due mainly to incorrect assumptions about Microsoft’s licencing policy, combined with the prevalence of multi-core CPUs. This has led to some incorrect responses to postings about CPU licencing on the SQL Server forums, so I’ve decided it’s worth blogging about to try and clarify the matter.
This post covers SQL Server 2005 – SQL Server 2008R2 editions with a brief update on the significant licencing policy changes in SQL Server 2012,
Up to and including SQL Server 2008R2, Microsoft’s multicore licencing policy was based on the number of sockets on the motherboard, and not the number of cores, but this has changed in SQL Server 2012 and will fall in line with the rest of the industry (a formula will be applied based on the number of cores).
What this means is that for SQL Server 2008R2 and before, for licencing purposes, a dual-core CPU equates to 1 CPU. For SQL Server 2012, it equates to 2 CPUs.
A quad-core CPU therefore equates to 1 CPU or 4 depending on the SQL Server version etc. etc.
Therefore, If you’re thinking about upgrading from SQL Server 200n to SQL Server 2012 I recommend reading Glenn Berry’s hardware post which has a discussion on the issues to consider, as it could cost you dear if the system(s) concerned have more than 4 cores.
Incidentally, the free SQL Express edition as of SQL Server 2012 has been capped to a maximum of 4 cores.
It’s well worth keeping track of official limitations here, as it can be a struggle to keep up to all the licencing changes!
[Edit (2014-01-22). The changes to the CPU core licencing saga for SQL Server 2012 continues. Even if you have Enterprise edition, if the licencing model being applied is Server + Client Access License (CAL) based then the server is limited to 20 physical cores per instance. Only Enterprise Edition utilising core-based licencing will be able to use the operating system maximum as stipulated in the Compute Capacity Limits by Edition of SQL Server documentation.
If you’re not sure what licencing model is in effect, the following message at the top of the SQL Server error log will confirm core based licencing:
2014-07-07 09:45:01.130 spid68 Microsoft SQL Server 2012 (SP1) - 11.0.3412.0 (X64) Mar 2 2014 01:25:09 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: )
If the ‘Core-based licensing’ stipulation is absent you’re on CAL licencing. Further down the error log (from a fresh SQL Server restart you will see the following message:
SQL Server detected x sockets with y cores per socket and zz logical processors per socket, zz total logical processors; using 40 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
To change it you will need to get the right key and change the licencing as documented here, or you can just run setup again from the correct licenced CD and use my post on changing the SQL Server edition to make the change.
Note: I had a licencing expert from one of the big hardware vendors, who try to convince me that I had to reinstall SQL Server to change the licencing model to a higher edition. This is not true, just follow the steps in that changing the SQL Server edition post**.**
You can verify a particular servers CPU core usage by interrogating the sys.dm_os_sys_info SQL Server dynamic management view (DMV). The following query lists how many CPUs a particular instance of SQL Server can see:
select cpu_count from sys.dm_os_sys_info
(The above query will also confirm only 20 or 40 cores (if hyperthreading is enabled) are actually online.)
The sys.dm_os_sys_info DMV returns a whole host of other useful information that used to be very difficult to obtain without using xp_cmdshell or WMI, so it’s well worth exploring further.
In fact, most of the DMVs have invaluable information if you know what you’re looking for, but that’s a subject area far too large for this post; just peruse Books Online as that gives a complete listing and a good rundown of what each DMV offers.
Just remember that, although SQL Server Express will be able to see multiple CPUs and use them to improve concurrency, SQL Server Express edition is prevented from taking advantage of this to parallelise individual queries, which basically means that an individual query will not be executed across multiple CPUs
How many CPU cores is SQL Server using?
Back to counting CPUs. To list how many CPUs a particular instance is actually using, run the following query which is based on the sys.dm_os_schedulers DMV:
select scheduler_id,cpu_id, status, is_online from sys.dm_os_schedulers where status=‘VISIBLE ONLINE’
This will return how many CPUs SQL Server is using by listing all the CPU cores it can see. It’s easy enough to modify this query with a count (*) if you just want a count of the CPU cores. Ordinarily, this will always equate to the number of CPUs on the system. However, if CPU affinity is being used to assign specific CPUs to SQL Server (in order to adhere to licencing requirements, for example), this query will show how many CPUs SQL Server is actively using.
Run the query without the where clause to see how many CPUs are offline and not being used (their status will be VISIBLE OFFLINE). The additional rows returned will show up internally used schedulers such as the scheduler retained for the Dedicated Admin Connection, or DAC.
If this query does not list the number of CPUs you are expecting, and you’re definitely not using CPU affinity, check the edition of SQL Server that you are using against the table below.
Note: To confirm that CPU affinity is indeed being used, the cpu_id column will always be less than 255.
CPUs supported by the various SQL Server Editions
A summary of how many CPUs the different editions of SQL Server support is summarised in the table below:
| SQL Server version/Edition | Express | Workgroup | Web | Standard | Enterprise |
|---|---|---|---|---|---|
| 2005 | 1 | 2 | n/a | 4 | Unlimited |
| 2008 | 1 | 2 | 4 | 4 | Unlimited |
| 2012+ | 1 | Oh dear, you better read this and probably this |
[This table looked a lot better in previous versions of WordPress]
