THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Why the “Toilet” Analogy for SQL might be bad

Robert Davis(blog/twitter) recently blogged The Toilet Analogy … or Why I Never Recommend Increasing Worker Threads, in which he uses an analogy for why increasing the value for the ‘max worker threads’ sp_configure option can be bad inside of SQL Server.  While I can’t make an argument against Robert’s assertion that increasing worker threads may not improve performance, I can make an argument against his suggestion that, simply increasing the number of logical processors, for example from 4 to 8, can resolve the problem.

Why do I make an argument against this?  It’s really quite simple, you can easily double the number of logical processors inside of SQL Server, by enabling hyper threading at the BIOS level for the server.  While this feature of Intel processors doubles the number of logical processors available to the system, thus doubling the number of schedulers available to SQL Server, it isn’t without cost.  Now, to be perfectly fair, Robert doesn’t mention in his blog post any concept of increasing the number of logical processors by enabling hyper threading for the server, but its not hard for someone that is unfamiliar with SQL Server scheduling to come to that conclusion.

It is unfortunate that Robert uses a easy to misinterpret/misconstrue example of simply doubling the logical processors, which in turn doubles the number of schedulers inside SQL Server.  Whether or not enabling hyper threading will help or hinder SQL Server depends on a number of factors, not the least of which is the type of processor in use on the server.  Why does the type of processor make a difference?  I am very happy that you asked.  It makes a difference because the size of the on-chip cache, as well as the number of processor cores affects how efficiently the processor can make use of hyper threading under memory dependent workloads, like that generated by SQL Server.   Older processors with smaller caches, can be prone to cache miss problems that can negatively impact performance, while newer processors may not have this same type of problem under the same workload with hyper threading enabled.

One of the key points that Robert accentuates in his blog post, is that appropriate testing should be performed for changes to the system to ensure that the change has the anticipated effect.  On some systems, enabling hyper threading, and doubling the number schedulers available inside of SQL Server, might just be the needed change to accommodate a given workload, without having to scale up the physical hardware for the server.  However, the same system may be prone to bottlenecks under a different workload.  On older hardware, hyper threading has been recommended against, but my own testing on newer hardware (Xeon 5530) processors, have shown the the impact of enabling hyper threading can be beneficial, or at worst negligible depending on the type of workload being generated.

The biggest takeaway from Robert’s blog post is to properly test any type of configuration change before actually implementing it in production.

Published Saturday, May 08, 2010 3:33 AM by Jonathan Kehayias

Comments

 

Glenn Berry said:

I agree completely with you that hyper-threading works much better in Nehalem and Westmere-based processors (the Xeon 55xx, 56xx, 60xx, and 75xx) than it did in the old Netburst Pentium 4 based Xeon processors of the past.

My testing and real world experience with Xeon X5550 over the last year and a half has shown excellent performance with HT enabled for an OLTP workload. Every single submitted TPC-E benchmark that uses one of these newer Xeon processors has HT enabled. I don't think that is an accident.

As always, people should do their own testing to decide what works best in their environment.

May 8, 2010 8:59 AM
 

Robert L Davis said:

I think you misinterpreted my statement regarding increasing the number of logical processors. I never stated that doing so would resolve any issues. I'm definitely not a proponent of throwing hardware at performance issues.

Also, I wasn't advocating enabling hyperthreading. I was thinking more along the lines of adding additional CPU's. That's why the analogy said to add another bathroom rather than putting more stalls in the existing bathroom.

I was stating that if you increase the number of CPU's, you can get more CPU threads and are able to process more at the same time. That's all I was stating with those last two sentences.

May 8, 2010 10:20 AM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement