原文: SQLServer 2012異常問題(二)--由安裝介質引發性能問題

問題描述：生產環境一個數據庫從SQLSERVER 2008 R2升級到SQLSERVER 2012 ，同時更換硬件，但遷移后發現性能明顯下降，應用寫入、讀取性能下降的比較厲害；

?

向微軟尋求幫助后得出答案，原來這與SQLSERVER的安裝介質有關。

大致意思是說由于NUMA架構可以自行管理內存池，在安裝了CAL的EE后，由于限制只能使用20個cores，同樣內存則只能管理到20個cores涉及到的NUMA的對應的內存空間（具體算法為 限制內存=當前物理內存/NUMA數量*(總核數/20)），如果限制SQL Server的最大使用內存超過前面說的限制內存，則當使用內存大于限制內存需要再向操作系統再申請空間時，則會產生跨NUMA處理的情況，導致大量消耗系統資源，引起性能下降；

?

http://blogs.msdn.com/b/saponsqlserver/archive/2012/06/15/sql-server-2012-enterprise-editions.aspx

這是我在網上找到的解釋，摘錄其中幾段（本人E文水平有限，翻譯不當之處敬請見諒）

關于SQLSERVER EE的安裝介質（EE為Enterprise Editions簡拼，企業版）

• SQL Server EE is no longer being offered under the Server + CAL (Client Access License) licensing model. For customers with Software Assurance on existing SQL EE Server licenses (or access to them under their current Enterprise Agreements during term) a version of Enterprise Edition was created to enable them to upgrade to SQL Server 2012. This version has technical restrictions limiting an instance to using only 20 processor cores (40 CPU threads with Hyperthreading).. Customers must still have the proper version of the CAL and additional physical and virtual use right restrictions of this SKU (Stock Keeping Unit) apply. Please refer to the three documents listed above for additional details.
• An Enterprise Edition which is licensed per core and which does not have limits on the # of cores usable on a server (within the absolute limits supported). This Enterprise Edition does reflect the new licensing model for SQL Server Enterprise Edition.

上面說到 即便是SQLSERVER EE，由于授權方式的差異導致對processor cores的限制

For customers with Software Assurance on existing SQL EE Server licenses

An Enterprise Edition which is licensed per core and which does not have limits on the # of cores usable on a server

通過以下方式可以檢查當前運行的SQL EE信息

1、sp_readerrorlog ，第一行顯示SQLSERVER 版本信息如下

2012-05-08 16:04:54.56 Server????? Microsoft SQL Server 2012 - 11.0.2100.60 (X64)

???????????? Feb 10 2012 19:39:15

???????????? Copyright (c) Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

2、select serverproperty('Edition') ，顯示版本信息如下

Enterprise Edition (64-bit)

如何判斷當前的SQL EE是基于per CAL還是per core的呢？如果顯示的信息如上所示，那就是基于per CAL的，文中再次強調此模式下受限于20 cores；

Answer is: It is the CAL licensed one and with that the Enterprise Edition which is limited to 20 cores!!!

而如果顯示的信息如下所示，那就是基于per core的 則沒有限制；

The per-core licensed Enterprise Edition will show like this:

2012-05-18 23:57:29.77 Server Microsoft SQL Server 2012 - 11.0.2100.60 (X64)

Feb 10 2012 19:39:15

Copyright (c) Microsoft Corporation

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

?Executing:

select serverproperty('Edition')

which then could show this result:

Enterprise Edition: Core-based Licensing (64-bit)

?

關于20 cores的限制問題，需要區分CPU是否支持超線程而言

Other indications that there might be a limitation to 20 cores could be identified as well at the beginning of the SQL Server 2012 errorlog where we can find a message like:

SQL Server detected 4 sockets with 6 cores per socket and 6 logical processors per socket, 24 total logical processors; using 20 logical processors based on SQL Server licensing.

In the case above, we are looking at a server with the last generation of Intel processors which did not have Hyperthreading yet. Or in more modern Intel Servers with Hyperthreading it would look like:

SQL Server detected 4 sockets with 8 cores per socket and 16 logical processors per socket, 64 total logical processors; using 40 logical processors based on SQL Server licensing.

上文中的描述，根據SQL Server 2012 errorlog中的內容，我們可以看到

如果SQL Server 檢測到 4個插槽，每個插槽有6個核，且有6個邏輯處理器（單線程），則總共為24個邏輯處理器，受限于SQL Server licenseing，只能使用20個邏輯處理器；

對于超線程CPU：

如果SQL Server 檢測到 4個插槽，每個插槽有8個核，且有16個邏輯處理器（單線程），則總共為64個邏輯處理器，受限于SQL Server licenseing，只能使用40個邏輯處理器；

?

Another possibility of discovery is through the Microsoft MAP toolkit. Where to get it and how to use it is excellently described in this document: http://download.microsoft.com/download/F/F/2/FF29F6CC-9C5E-4E6D-85C6-F8078B014E9F/Determining_SQL_Server_2012_Core_Licensing_Requirements_at_SA_Renewal_Apr2012.pdf

另外一種可能的發現是通過Microsoft MAP toolkit，可以在以下這個文檔中得到更準確的描述；

---------------------------華麗麗的分割線---------------------------------------

How is the throttle of 20 cores enforced for the CAL license-based Enterprise Edition?

The limitation or the cap is enforced by the # of SQL Server schedulers. Usually SQL Server creates one scheduler thread for every logical CPU on a server. Each of those scheduler threads is administrating a pool of worker threads which execute requests or are in different other states. A scheduler only can have one thread running at maximum. If a scheduler thread over all of the time has one of worker threads running, it can leverage at maximum one logical CPU and not a bit more. If there are (as in the second situation above) only 40 schedulers active to schedule worker threads, the maximum number of CPU power we can use at any given time is 40 logical CPUs.

Querying sys.dm_os_schedulers with this query:

select * from sys.dm_os_schedulers

we will realize that the all the schedulers are ‘Visible’ for all the logical CPUs, but only 40 of them will be ‘Online’, whereas the others are ‘Offline’

If you disable Hyperthreading, the number of schedulers being Online will decline to 20, since one single core is now represented by one CPU thread only compared to two with Hyperthreading enabled. In cases where there are many more CPU threads or logical CPUs than the limit of the Server+CAL licensed SQL Server 2012 Enterprise Edition, one certainly can use affinity mask settings to chose the CPUs SQL Server shall use.

通過sys.dm_os_schedulers這個DMV可以查詢到SQL Server調度線程的情況；

?

如何在EE的兩個不同的產品間變更？在下面的鏈接中可以找到答案

http://msdn.microsoft.com/zh-cn/library/ms143393.aspx

SQLServer 2012異常問題(二)--由安裝介質引發性能問題