kascecf.blogg.se

1. Sql server 2012 enterprise edition vs core upgrade#
2. Sql server 2012 enterprise edition vs core windows#

Prior to the 20 logical processor limitation in SQL Server 2012 Enterprise Edition with Server+CAL licensing, it was a rare scenario that SQL Server would have a scheduler imbalance between NUMA nodes in a production server. Knowing what I know about SQLOS, SQL Server, and hardware, this makes sense. This behavior is documented in Books Online for SQL Server ( (v=sql.105).aspx). dm_os_schedulers WHERE = N'VISIBLE ONLINE' GROUP BY parent_node_id Parent_node_id, SUM (current_tasks_count ) AS current_tasks_count, SUM (runnable_tasks_count ) AS runnable_tasks_count, SUM (active_workers_count ) AS active_workers_count, AVG (load_factor ) AS avg_load_factorįROM sys. To do this we'll use the following query, the output of which is shown in Figure 5. If we change the way that we are looking at this data and aggregate the data based on parent_node_id we'll see that the tasks are actually being balanced across the NUMA nodes. On NUMA-based systems, new connections are assigned to the NUMA nodes in a round-robin fashion, and then inside of the NUMA node the connection is assigned to a scheduler based on load. This is something that occurs by design, though I doubt that this scenario was expected when the 20 logical processor limitation was originally implemented. So what exactly is the problem and why does this occur?Īt first glance you might think that this is a bug in SQL Server, but it isn't.

This is clearly evident in the runnable tasks counts for the four schedulers in the second NUMA node, which are three to four times the size of those for the schedulers in the first NUMA node. This information shows a significant imbalance and performance is going to be affected as a result. You can also see this symptom visually in monitoring tools such as SQL Sentry Performance Advisor:įigure 4 – NUMA imbalance as shown in SQL Sentry Performance Advisor dm_os_schedulers WHERE = N'VISIBLE ONLINE' Īn example output of this query under load is shown in Figure 3 below.įigure 3 – Schedulers under load with Enterprise Server+CAL In SQL Server under the Enterprise Server+CAL licensing model, this results in a scheduler configuration that is similar to the following:įROM sys. The VM was configured to have 32 virtual CPUs with 16 virtual processors allocated in two vNUMA nodes.

Sql server 2012 enterprise edition vs core windows#

To demonstrate this I created a VM on our Dell R720 test server which has two sockets and Intel Xeon E5-2670 processors installed, each with 8 cores and Hyperthreading enabled, providing a total of 32 logical processors available under Windows Server 2012 Datacenter Edition. As a result, performance problems can occur for the instance, due to NUMA node scheduler imbalances. With the default configuration that SQL Server applies under the 20 logical processor limitation using Server+CAL, the first 20 schedulers are VISIBLE ONLINE and any remaining schedulers are VISIBLE OFFLINE. This is an informational message no user action is required. SQL Server detected 2 sockets with 16 cores per socket and 16 logical processors per socket, 32 total logical processors using 20 logical processors based on SQL Server licensing. This licensing also extends to VMs with a limit of 4 VMs being covered by the Enterprise Server+CAL license, but still with the same 20 logical processor limitation as documented in the SQL Server 2012 Virtualization Licensing Guide.Ī lot of people have been caught off guard by the 20 logical processor limitation, even though it is documented in the licensing guides.Īn entry is made in the ERRORLOG file when the instance starts up, specifying the number of logical processors and that the 20 processor limitation is being enforced:

Sql server 2012 enterprise edition vs core upgrade#

Customers under Software Assurance can upgrade to SQL Server 2012 Enterprise Edition and still use Server+CAL licensing (also known as "grandfathering") but with a limitation to 20 logical processors, as documented in the SQL Server 2012 Licensing Guide. One of the challenges that Microsoft faced with this change was providing a migration path for customers that previously used Server+CAL based licensing for Enterprise Edition prior to SQL Server 2012. Numerous licensing changes were introduced in SQL Server 2012 the most significant was the move from socket-based licensing to core-based licensing for Enterprise Edition.