sql  server  The issue of not being able to fully utilize the number of logical cores in the CPU in 2016

Recently, I encountered a problem in my work where sqlserver2016 cannot fully utilize the CPU core. Through analysis tools, I can see that;There are a total of 72 CPU cores, but SQL  Only 40 cores can be used, and many people have encountered this problem

Recently, I encountered a problem in my work where sqlserver2016 cannot fully utilize the CPU core. Through analysis tools, I can see that;

There are a total of 72 CPU cores, but SQL  Only 40 cores can be used, and many people have encountered this problem. So today's section will first talk about how this problem arose.

First  Introduction to the server environment

  Windows2008R2  Enterprise  Edition(X64)+Microsoft  SQL  Server  2012  (SP1)Enterprise  Edition  (64-bit)

  CPU2 channels with 18 cores enable hyper threading, resulting in 72 cores; As shown in the figure:

Looking at the properties of SQL instances again; As shown in the figure:

SQL Server has actually recognized the total number of cores; 72 

So does SQL server mean that all can be used? From the system view, it can be seen that;

select * fromsys.dm_ os_ schedulerswhereis_ online=1ANDstatus=' VISIBLEONLINE'

Only 40 cores are truly used in SQL Server.

So we know that the concept of ProcessGroup is based on Windows; The technology introduced only in server2008 (x64), sqlserver; 2008R2 and later versions use ProcessGroup technology, why sqlserver2016; Enterprise  Edition  (64 bit) actually has this problem,

Isn't it a bit unreasonable?

Is the version of SQL incorrect? Then I can execute select @ @ version  Check the command for the version and find out if the version is 

Microsoft  SQL  Server  2016  (SP1) -& nbsp; 11.0.3128.0  (X64) 

 & nbsp;& nbsp;& nbsp; Dec  28  2012  20:23:12 

 & nbsp;& nbsp;& nbsp; Copyright  (c)  Microsoft  Corporation

 & nbsp;& nbsp;& nbsp; Enterprise  Edition  (64-bit)  on  Windows  NT  6.1 & lt; X64>& nbsp; (Build 7601: Service Pack 1)

It's a 64 bit enterprise version! Why not? After searching online, it was found that sqlserver2016; There are two types of Enterprise Edition, one is as seen above. It can only recognize a single set of 64 cores at most. Due to processor grouping, it only uses one set of 40 cores, while the other set of 32 cores cannot be reused.

So the simplest way is to upgrade the existing version of SQL to SQLSERVER2016ENTERPRISECORE  Edition

Open the SQL Server Installation Center; As shown in the figure:;

Maintenance- Version upgrade

 & nbsp; SQL  Server  2016  Enterprise  Core  Edition  TBR8B-BXC4Y-298NV-PYTBY-G3BCP

  Enter core; The serial number of the version, and then proceed to the next step  Next  Next  Keep clicking until it is upgraded& nbsp;& nbsp;& nbsp;

Next, let's take a look at sqlserver; At this point, is it genuine? All 72 cores were used& nbsp;& nbsp;& nbsp;& nbsp;

At this point, we will use SELECT again@@ version  Check the version information of SQL

Microsoft  SQL  Server  2016  (SP1) -& nbsp; 11.0.3128.0  (X64) 

 & nbsp;& nbsp;& nbsp; Dec  28  2012  20:23:12 

 & nbsp;& nbsp;& nbsp; Copyright  (c)  Microsoft  Corporation

 & nbsp;& nbsp;& nbsp; Enterprise  Edition:  Core-based  Licensing  (64-bit)  on  Windows  NT  6.1 & lt; X64>& nbsp; (Build 7601: Service Pack 1)

We see & nbsp; Core-based  Licensing  The identification has been removed, and the restrictions on the kernel for SQLSERVER have also been lifted!!

That's all for this article on the issue of SQL Server 2016 not being able to fully utilize the number of CPU logical cores. For more information on the number of CPU logical cores, please search for previous articles from Script Home or continue browsing the following related articles. We hope everyone can support Script Home more in the future!