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

標籤：

原文: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異常問題(二)--由安裝介質引發效能問題