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

Configure logging in Kaspersky’s server anti-virus products

By tom on June 8th, 2009

Last week we learned an important lesson: do not leave the default logging values in Kaspersky’s anti-virus products.

If you install Kaspersky Security 6.0 for Exchange 2007, 2 different logs will be kept: one common log (which contains information about the applications’ activity) and an anti-virus log (which contains the results of the anti-virus scans). The default logging values are to have one log per month, keep 4 of these logs before rotating them and a minimal logging level. Also by default these files reside in your Program Files under the folder Kaspersky Labs.

Last week, we noticed that on this exchange servers’ root drive there was only 300MB left. After analysis we discovered that the Kaspersky log files where over 36GB in size!

You can configure the logging values in your Kaspersky Management Console under General Settings –> Diagnostics tab.

Hereunder you can find a screenshot where you can see that we have moved the log files from the Program Files folder to a drive used to store only log files and that we changed the setting to keep 2 months of logging.

 

I hope this helps.

Tom