SQL Server 2012 exception Issue (ii)--performance issues caused by installation media

Original: SQL Server 2012 exception Issue (ii)--performance issues caused by installation media

Problem Description: Production environment A database is upgraded from SQL Server R2 to SQL Server 2012, while the hardware is replaced, but after the migration, the performance decreases significantly, and the performance of the application writes and reads is worse.

Ask Microsoft for help after the answer, originally this is related to SQL Server installation media.

The general meaning is that since the NUMA architecture can manage its own pool of memory, after the EE with CAL is installed, because the limit can only use 20 cores, the same memory can only manage the corresponding memory space of 20 cores-related NUMA (the specific algorithm is limited memory = current physical Memory/ NUMA number * (Total cores/20)), if you limit the maximum use of SQL Server memory than previously stated limit memory, then when using memory greater than the limit memory needs to re-request space to the operating system, it will produce cross-NUMA processing, resulting in a large consumption of system resources, causing performance degradation;

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

This is the explanation I found on the Internet, excerpts of several paragraphs (I am limited in E-language, translation is not a bad place please forgive me)

About SQL Server EE installation media (EE for Enterprise Editions, Corporate Edition)

• 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 is created to enable them to upgrade to SQL Server 2012. This version has technical restrictions limiting a instance to using only the processor cores (CPU threads with Hyperth Reading).. Customers must still has the proper version of the CAL and additional physical and virtual use right restrictions of this SKU (Stock keeping Unit) apply. Refer to the three documents listed above for additional details.
• an Enterprise Edition which are licensed per core and which does not having 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.

It says that even SQL Server EE, due to differences in licensing methods, leads to restrictions on processor cores.

For customers with Software Assurance on existing SQL EE Server licenses

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

You can check the currently running SQL EE information in the following ways

1, Sp_readerrorlog, the first line shows the SQL Server version information as follows

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 '), display version information as follows

Enterprise Edition (64-bit)

How can I tell if the current SQL EE is based on per Cal or per core? If the information shown above is based on the per Cal, it is again emphasized that this mode is limited by the cores;

Answer is: It's the CAL licensed one and with the Enterprise Edition which are limited to cores!!!

And if the information displayed is as follows, then there is no limit based on per core;

The Per-core licensed Enterprise Edition would 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)

Regarding the limitations of the cores, it is necessary to differentiate whether the CPU supports Hyper-threading

Other indications, there might is a limitation to cores could is identified as well at the beginning of the SQL Ser Ver errorlog where we can find a message like:

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

In the case above, we is looking at a server with the last generation of Intel processors which do not has Hyperthreadi ng yet. Or in the modern Intel Servers with hyperthreading it would:

SQL Server detected 4 sockets with 8 cores per socket and logical processors per socket, up to total logical processors; Using the logical processors based on SQL Server licensing.

As described in the preceding section, according to the contents of SQL Server errorlog, we can see

If SQL Server detects 4 slots with 6 cores per socket and 6 logical processors (single threaded), a total of 24 logical processors, limited to SQL Server licenseing, can use only 20 logical processors;

For Hyper-Threading CPUs:

If SQL Server detects 4 slots with 8 cores per socket and 16 logical processors (single threaded), a total of 64 logical processors, limited to SQL Server licenseing, can use only 40 logical processors;

Another possibility of discovery is through the Microsoft MAP Toolkit. Where to get it and how to use it are 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

Another possible discovery is through the Microsoft MAP Toolkit, which can be more accurately described in the following document:

---------------------------Gorgeous split-line---------------------------------------

How are the throttle of 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 CPUs on a Server. Each of the those scheduler threads is administrating a pool of worker threads which execute requests or was in different othe R states. A Scheduler only can has one thread running at maximum. If a scheduler thread over all of the time have one of the worker threads running, it can leverage at maximum one logical CPU a nd not a bit more. If there is (as in the second situation above) only a schedulers active to schedule worker threads, the maximum number O F CPU Power We can use at any given time is logical CPUs.

Querying sys.dm_os_schedulers with this query:

SELECT * FROM Sys.dm_os_schedulers

We'll realize the all the schedulers is ' Visible ' for all the logical CPUs, but only if the them would be ' Online ', Whereas the others is ' Offline '

If you disable hyperthreading, the number of schedulers being Online would decline to a, since one single core are now REPR esented by one CPU thread is compared to both with hyperthreading enabled. In cases where there is many more CPU threads or logical CPUs than the limit of the Server+cal licensed SQL Server (E) Nterprise Edition, one certainly can use affinity mask settings to chose the CPUs SQL Server shall use.

By sys.dm_os_schedulers the DMV can query the SQL Server scheduler thread;

How do I change between the two different products of EE? The answers can be found in the links below

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

SQL Server 2012 exception Issue (ii)--performance issues caused by installation media