MS SQL Server and CPU usage


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 and SQL Server 2008 editions.

Microsoft’s multicore licencing policy is based on the number of sockets on the motherboard, notthe number of cores (EDIT 20111118: this is apparently going to change 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.

What this means is that your free SQL Express edition can use all 4 cores in your quad core CPU and not just one core. You can verify this 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:

SQL Server version Express Workgroup Web Standard Enterprise
SQL Server 2005 1 2 n/a 4 Unlimited1
SQL Server 2008 1 2 4 4 Unlimited1
1Subject to OS limits 


Comments are closed.