SQL Server and CPU usage

How many CPUs can SQL Server supports should be a simple question, you might think. But 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 where a formula will be applied based on the number of cores). Therefore, for licencing purposes, a dual-core CPU equates to 1 CPU, not 2. A quad-core CPU equates to 1 CPU, not 4 etc. etc. Therefore, If you’re thinking about upgrading from SQL Server 200n to SQL Server 2012 I recommend reading Glenn Berry’s recent hardware post which has a discussion on the issues to consider, as it could cost you dear.

What this means is that your free SQL Express edition, up until SQL Server 2012 can use all 4 cores in your quad core CPU and not just one core. That’s changed with the current version, which as of the time of updating this post is SQL Server 2012 which has been capped to a maximum of 4 cores. It’s worth keeping track of official limitations here.

You can verify usage by interrogating the sys.dm_os_sys_info SQL Server dynamic management view (DMV), particularly. 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 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 a blog; 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.

Confirming how many CPUs SQL Server is using

Back to counting CPUs. To find out 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. 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, 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: