Tuesday, December 25, 2012

SQL Server 2012 Enterprise Edition Licensing Model

Microsoft made some changes on the SQL Server 2012 licensing. One of the big change is from previously processor based licensing, to core based licensing model. While Microsoft try to simplify SQL Server 2012 licensing by retiring Datacenter, workgroup and small business edition, there are some confusion linger around with SQL Server 2012 Enterprise Edition Server plus CAL licensing model and SQL Server 2012 Enterprise Edition Core-based licensing model.

Although Microsoft no longer offer SQL Server 2012 Enterprise Edition Server+CAL license, customer with SA (Software Assurance) coverage for existing SQL Server 2008 R2 can continue to purchase new SQL Server 2012 EE Server+CAL license until their active agreement end.

To identify which SQL Server edition installed, use serverproperty to retrieve information about the server instance,


Result for Enterprise Edition Server+CAL model,

Enterprise Edition (64-bit)

Result for Enterprise Edition Core based model,
Enterprise Edition: Core-based Licensing (64-bit)

Let's examine the difference between the SQL Server 2012 EE Server+CAL and SQL Server 2012 EE Core based licenses from both technical and license perspective. The comparison below is only applied for Enterprise Edition (Other edition like Standard Edition has different licensing model)

SQL 2012 EE Per Core model license by each core. In physical operating system environment (OSE), all physical cores on the server must be licensed regardless of software partitioning or using CPU affinity (refer to this blog post of using CPU affinity for licensing portion of CPU). In virtualized environment, all virtual cores (or virtual processor, virtual CPU, virtual thread) allocated to VM must be licensed accordingly, with a minimum of 4 cores license per VM. If all physical cores on the server are licensed, unlimited number of SQL instance can be run (with the limitation that number of OSE (both physical or/and VM) equal to number of cores licensed)

SQL 2012 EE Server+CAL model has a 20 physical cores limitation (20 logical cores without hyperthreading or 40 logical cores with hyperthreading turned on). This license model is intended and physically limited for server with total of 20 physical cores and less. In physical environment, the OSE (Operating System Environment) permit access of maximum 20 physical cores. Every SQL instance is limited to 20 (or 40 if hyperthreading turned on) threads. In virtual environment, single server license up to a set of 4 VMs and the whole set can only access up to total of 20 physical threads at any time. If more than 4 VMs are needed, additional SQL 2012 EE server license could be assigned for another set of 4 VMs with access up to total of 20 physical thread.

Complete details of SQL Server 2012 licensing including the distinction and limitation of Enterprise Edition Server+CAL and Enterprise Edition Core-based licensing can be downloaded here.

Use the dm_os_schedulers DMV (database management view) to inspect how many schedulers (with VISIBLE ONLINE status) that are available to process threads.

FROM sys.dm_os_schedulers
WHERE scheduler_id < 1048576 AND [status] = 'VISIBLE ONLINE';

Notes: Regular queries run on scheduler with ID less than 1048576.

If the server has more than 20 physical cores, only 20 schedulers (or 40 with hyperthreading) are available for processing with status of VISIBLE ONLINE and the rest of the schedulers as VISIBLE OFFLINE.

With the Server+CAL license, SQL Server appears to always assign the first 20 schedulers (or 40 with hyperthreading) it identifies as VISIBLE ONLINE and the rest as VISIBLE OFFLINE. This may result a imbalance of schedulers on the NUMA nodes. Imagine there are two NUMA with each with 16 schedulers. The first 16 schedulers of the first NUMA node togther with only 4 schedulers in the second NUMA node is assigned as VISIBLE ONLINE. This affect the performance as loads are distributed to NUMA as round-robin basis (more information can be found here). If there are 10 tasks are to be processed, 5 tasks are distributed to first NUMA (with 16 schedulers) and 5 tasks to second NUMA (only 4 schedulers). This results the second NUMA has a much higher loads than the first NUMA.

One way to address this issue to use CPU affinity mask to affinitize 10 schedulers in first NUMA, and 10 schedulers in second NUMA to achieve a balanced distribution. However, please keep in mind that CPU affinity mask is deprecated in SQL 2012. Use ALTER SERVER CONFIGURATION to set the process affinity instead.

Jonathan Kehayias blog about the above described performance problem on SQL Server 2012 EE Server+CAL license in detail here at this post.