Microsoft Kerberos Configuration Manager for SQL Server available for download

By tom on May 21st, 2013

Microsoft recently released the Kerberos Configuration Manager. It is a small tool (746kb download) that allows you to troubleshoot and correct Kerberos authentication errors for SQL Server Engine, Analysis Services and Reporting Services.

I know that Kerberos and constrained delegation are sometimes difficult to understand for a lot of my customers and this tool will assist them in analyzing and setting the necessary SPN’s.

There is a x64 and x86 version available. You can find the download link here: http://www.microsoft.com/en-us/download/details.aspx?id=39046&WT.mc_id=rss_alldownloads_all

Tom



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.

Tom