This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
Identify how many CPUs our database server is using
Question
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.
asked 2021-09-29 by Hiren Parghi
Answer
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 them1.
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.
1 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.
answered 2021-09-29 by Andy Mallon