Optimizing your upgraded SQL 2012 when you only have 20 cores you can use due to licensing

By tom on September 27th, 2012

If you upgrade your existing SQL 2008 R2 to SQL 2012 using Software Assurance, you will be limited to using 20 cores. Aaron Bertrand wrote about it: http://sqlblog.com/blogs/aaron_bertrand/archive/2012/04/27/a-cautionary-tale-about-grandfathering-CAL-licenses-in-SQL-Server-2012-Enterprise.aspx and Geert Vanhove had this issue as well: http://geertvanhove.wordpress.com/2012/09/12/sql-2012-slower-than-sql-2008-on-large-boxes/

If you get hit by this, your SQL 2012 server will by default only use the first 20 cores. If for example you have a 4 CPU, 10 cores/CPU box, only the 2 first CPU’s will be used. You can verify this for yourself when looking at the sys.dm_os_schedulers DMV.

CPU wise it makes no difference which cores you use, they are all the same. But since SQL server is NUMA aware, memory access is different. You can read about how SQL server supports NUMA in BOL: http://msdn.microsoft.com/en-us/library/ms180954%28v=sql.105%29.aspx and http://msdn.microsoft.com/en-us/library/ms178144%28v=sql.105%29.aspx.

When SQL server needs memory from one of the other NUMA nodes, this is called foreign memory and is more expensive to fetch performance wise.

In order to optimize memory access a little bit, we changed the affinity mask. On the box we did our tests on we enabled CPU’s 0, 1, 2, 3, 4, 10, 11, 12, 13, 14, 20, 21, 22, 23, 24, 30, 31, 32, 33, 34, having a maximum total of 20 cores used, but spread out over the different CPU’s. We noticed that with a standard TPC-C and TPC-E check the performance gain was about 3-5%.

If you don’t have the budget to fully license your SQL 2012 server, this might be a way to squeeze out a little bit of performance.

A word of caution though: do not play with affinity masks if you’re not sure what you are doing and always perform tests in a development environment with a workload similar to your production workload.