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

問題描述：生產(chǎn)環(huán)境一個(gè)數(shù)據(jù)庫從SQLSERVER 2008 R2升級(jí)到SQLSERVER 2012 ，同時(shí)更換硬件，但遷移后發(fā)現(xiàn)性能明顯下降，應(yīng)用寫入、讀取性能下降的比較厲害；

?

向微軟尋求幫助后得出答案，原來這與SQLSERVER的安裝介質(zhì)有關(guān)。

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

?

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

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

關(guān)于SQLSERVER EE的安裝介質(zhì)（EE為Enterprise Editions簡拼，企業(yè)版）

• 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，由于授權(quán)方式的差異導(dǎo)致對(duì)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

通過以下方式可以檢查當(dāng)前運(yùn)行的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)

如何判斷當(dāng)前的SQL EE是基于per CAL還是per core的呢？如果顯示的信息如上所示，那就是基于per CAL的，文中再次強(qiáng)調(diào)此模式下受限于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)

?

關(guān)于20 cores的限制問題，需要區(qū)分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.

上文中的描述，根據(jù)SQL Server 2012 errorlog中的內(nèi)容，我們可以看到

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

對(duì)于超線程CPU：

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

?

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

另外一種可能的發(fā)現(xiàn)是通過Microsoft MAP toolkit，可以在以下這個(gè)文檔中得到更準(zhǔn)確的描述；

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

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這個(gè)DMV可以查詢到SQL Server調(diào)度線程的情況；

?

如何在EE的兩個(gè)不同的產(chǎn)品間變更？在下面的鏈接中可以找到答案

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

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