Blog Post

SQL Server and Hyper-threading

,

This post is about the CPU utilisation in the Standard, Web and Express editions of SQL Server because there is limitation for them. Licensing is given at the end of this post.

Physical environments

According to the official msdn references ([1], [2], [3]), Table 1 gives information about the physical cores the editions can utilize.

Number of cores Versions
SQL Server Edition 2005 and 2008 2012 and 2014 2016
Standard 4 16 24
Web 4 16 24
Express 1 4 4

Table 1. Maximum number of physical cores for the Editions

When hyper-threading is not enabled a physical core is actually a logical processor. When hyper-threading is enabled, then a physical core counts two logical processors. Hence, the next Table 2 shows the doubled numbers of the logical processors the instances can utilise.

Number of logical processors Versions
SQL Server Edition 2005 and 2008 2012 and 2014 2016
Standard 8 32 48
Web 8 32 48
Express 2 8 8

Table 2. Maximum number of logical processors for the Editions

You can use the following query to see how many logical processors are used by your SQL Server instance.

SELECT scheduler_id, cpu_id, [status], is_online
FROM sys.dm_os_schedulers
WHERE [status] = 'VISIBLE ONLINE'

Virtual environments

At virtual environments the physical processor architecture is practically not visible to the SQL Server instance. The maximum number of logical processors a SQL Server instance can utilise is according to Table 1.

Licensing

When you use dedicated physical servers for your SQL Server instances, then the licensing is based on the physical cores. For example, if your SQL Server 2016 instance is installed on a 24 physical core server, it will make a utilisation of the 24 (or 48 if hyper-threading is enabled) logical processors, but the licensing would be based on the 24 physical cores.

When you use virtual environment the licensing is based on the logical processors. For example, if you have a SQL Server 2016 instance installed on a virtual server with 30 logical processors, your instance would utilise the maximum of 24 logical processors, but the licensing would be based on the 30 logical processors.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating