The Perils of Hyperthreading on SQL Server

Just a quick note to send a big Thank You to Christoph Stotz of Frankfurt, Germany for his hospitality on Sunday. Thank you, Christoph! Slava Oks has a rather well-known blog entry about how hyperthreading can negatively impact the performance of a SQL Server 2000 instance. You might be wondering if those recommendations still hold true on SQL Server 2005?

Well, I have it on good authority (SQL Server MVP Geoff Hiten, FYI) that hyperthreading is still misbehaving on SQL Server 2005. Evidently, hyperthreading looks like a multi-core system to SQL Server 2005 thus triggering some "soft NUMA" behavior. As Geoff says, "The real problem comes in the synchronization primitives that aren't hyperthreaded friendly (such as spinlock code). SQL 2000 had a bug that was fixed in build 910 that dealt with this issue. This bug was re-introduced in SQL 2005 when they changed the memory and scheduler to handle multi-core processors and NUMA architectures. So, in short, I would turn off HT on SQL 2005 host computers as the default. As always, your mileage may vary." Great advice, Geoff!

Thanks, -Kevi