Identify how many CPUs our database server is using

Our server where the Microsoft SQL Server 2016 Web Edition server is installed is running very slow.

We are thinking of increasing the number of cores licensed on it. However, we need to check how many CPU cores the database is using currently. Our Windows Server 2016 has 16 cores (dual) of CPU.

SQL Server licensing (PDF license guide) requires that you license all cores (all cores installed on the physical server, or all vCores visible in the VM), up to the max limit of the SQL Server edition. In the case of SQL Server Web Edition, the enforced CPU maximum is the lesser of 4 sockets or 16 cores.

If your server has 16 cores, SQL Server will use all of them, and you must license all of them¹.

If you need to increase compute on your server, you will need to do an edition upgrade to move to a version that supports more than 16 cores.

¹ There is a deprecated feature that allows you to set CPU affinity to restrict SQL Server from using all cores. However, in addition to being deprecated, and causing unwanted side effects, it does not affect licensing. Even with CPU affinity set to use less than all cores in the server, you must still license all cores in the server.

The best way to see how many CPU’s the SQL server is using is to check sys.dm_os_schedulers or the startup message in the errorlog, which will not report on any CPU’s that have been hot added.

SELECT count (cpu_id)
 FROM sys.dm_os_schedulers
WHERE scheduler_id < 1048576
 AND is_online = 1

Check the SQL Server Error log – a message is written into the log when the SQL Service starts up, for example, this is from my laptop this morning :-

SQL Server detected 1 sockets with 4 cores per socket and 8 logical processors per socket, 8 total logical processors; using 8 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

I will use system dynamic management view sys.dm_os_schedulers. Take a not of status and is_online column, which will tell you which CPU’s are being used by SQL Server.

Please note that SQL Server 2016 Web edition has limitations about ‘Maximum compute capacity’. See Microsoft documentation Editions and supported features of SQL Server 2016.

You can get most of the information regarding operating system from DMV dm_os_sys_info – Be it memory or CPU or hyperthread.

Please run the below T-SQL and you will have details:

SELECT cpu_count, *
FROM [sys].[dm_os_sys_info]

Alternatively you may read from SQL Server errorlog as suggested by Stephen Morris.

You may also refer to this thread for more detail regarding your question.