Check how many CPU cores SQL can use/see
To see which version of SQL is running:
Run this query to see how many CPU’s are online:
select scheduler_id,cpu_id, status, is_online from sys.dm_os_schedulers where status='VISIBLE ONLINE'
The output will show many CPU’s SQL is using, most of the time it will equate with the number of CPU’s allocated to the system but if the CPU affinity is defined for SQL server to use particular amount of CPU’s according the licensing model then only active CPU’s will be shown in the output.
If we run the above query without the where clause then it will show how many CPU’s are online and offline/not being used.
The additional rows will show the internally used schedulers such as the schedulers used for the dedicated admin connection.
CPU’s supported by SQL Server Edition:
|| SQL Server Version || Express || Workgroup ||Web || Standard || Enterprise ||
| SQL Server 2005 | 1 | 2 | NA | 4 | Unlimited |
| SQL Server 2008 | 1 | 2 | 4 | 4 | Unlimited* |
| SQL Server 2012 | 1 | 2 | 4 | 4 | 8 |
| SQL Server 2014 | 1 | NA | 4 | 4 | Unlimited (See Notes) |
**Unlimited: Subject to OS Limit
**SQL 2014 Enterprise Edition: Core-based Licensing
Enterprise Edition with Server + Client Access License (CAL) based licensing (not available for new agreements) is limited to a maximum of 20 cores per SQL Server instance. There are no limits under the Core-based Server Licensing model.