Browse All Articles > Speeding up SQL server, more is less
There are many ways to speed up a MS SQL Server. This topic could cover anything from design to programming as well as maintenance. But one feature of Intel processors have hampered performance for years and this fact has been overlooked by a lot of people, but is now number one on my list.
Back in 2000, Intel introduced hyperthreading in it's Pentium 4 processors, and various Pentiums and Xeons have been hyperthreading-enabled over the years, and the i7 still has hyperthreading. So, there's a great chance that if you have your MS SQL server running on an Intel processor, that processor has hyperthreading, and that it has hyperthreading enabled. The sad fact is that some large server manufacturers have not only fitted these processors (nothing wrong with it in fact) but that they have enabled this feature in the BIOS by default. In my view this was a big mistake.
What is hyperthreading anyway? It's a way to use idle processor resources to serve multiple threads without the need to build a chip with two cores (which was introduced much later because it needed a much larger die at the time, and would create cooling and other problems). So, there's some extra logic on the chip to control two virtual execution paths, but there was only one L1 cache (this on chip cache covers a a significant area on a CPU). The problem that it supposed to solve is this: a program executes a branch statement, which requires a portion of data which is not in the L1 cache. While the CPU waits for this data to become available, some other thread executes, for instance doing some adding or multiplication, which does take time but less than the time required to bring in the required code or data to have the first thread finish it's turn. So, without Hyperthreading both threads would wait for the right code or data to turn up in L1, and with hyperthreading, the second thread would continue. This was said to improve multithreading performance by 15-30%.
As MS SQL is multithreaded, one would expect it to be a fine candidate. However, it's not. The simple fact is that in SQL applications, it leads to a phenomenon known as cache thrashing. So, while the first thead is waiting for some more data, the second thread gets control, empties the cache and while waiting for the right data to turn up it all takes much longer than if one would not have hyperthreading. Intel has been asked about this by some large web sites, but apparently never responded. It's a fact that is not widely known, or even acknowledged, and the reason I'm mentioning it here.
I have personally worked on SQL servers at several customers and where performance was slow, I have looked at what could be done. I have not trusted hyperthreading since it's introduction, and I read the reports and benchmarks at the time indicating that graphic software like video editors would benefit but not much more. So, I tried my luck at turning hyperthreading off. Bingo, a huge jump in performance. So, last year, during a contract as DBA for a large non profit governmental company, I was confronted by a sluggish SQL / IIS / ASP.Net setup running a well known accounting application. The customer was not happy at all, his end users had to wait for a certain query to finish and it took about 45 seconds. By turning hyperthreading off, I was able to bring the execution time of this one query down to 5 seconds. It was not the only thing I changed on his server, but by baselining before turning hyperthreading off and measuring the result, I could prove that this change alone contributed to having off 40 seconds.
If you have a SQL server that has bad performance (which is not something you have to rely on an end user to tell you) it is worth investigating. How do you go about it?
Now this spectacular improvement might seem outrageous, but it's not. One other instance where I applied this was a script a co-worker used to accumulate information from 20 sites into one database. The full run took 2 days, one major site took 2 hours for the SQL intensive part to finish. By just disabling hyperthreading, we shortened it to 45 minutes. Your mileage may vary. However, I feel that there are not many more things you can do to SQL that will make such a difference, and cost no more than a reboot.
So, this is truly a case of more is less. Enjoy. Feedback is welcome.
Disclaimer : Hyperthreading is not the cause for all performance problems. So, disabling it might not fix your problem, however if you do have high CPU in an OLTP environment then I have no problems recommending disabling it as a starting point. I am sure you will be pleasantly surprised (and relieved).
Back in 2000, Intel introduced hyperthreading in it's Pentium 4 processors, and various Pentiums and Xeons have been hyperthreading-enabled over the years, and the i7 still has hyperthreading. So, there's a great chance that if you have your MS SQL server running on an Intel processor, that processor has hyperthreading, and that it has hyperthreading enabled. The sad fact is that some large server manufacturers have not only fitted these processors (nothing wrong with it in fact) but that they have enabled this feature in the BIOS by default. In my view this was a big mistake.
What is hyperthreading anyway? It's a way to use idle processor resources to serve multiple threads without the need to build a chip with two cores (which was introduced much later because it needed a much larger die at the time, and would create cooling and other problems). So, there's some extra logic on the chip to control two virtual execution paths, but there was only one L1 cache (this on chip cache covers a a significant area on a CPU). The problem that it supposed to solve is this: a program executes a branch statement, which requires a portion of data which is not in the L1 cache. While the CPU waits for this data to become available, some other thread executes, for instance doing some adding or multiplication, which does take time but less than the time required to bring in the required code or data to have the first thread finish it's turn. So, without Hyperthreading both threads would wait for the right code or data to turn up in L1, and with hyperthreading, the second thread would continue. This was said to improve multithreading performance by 15-30%.
As MS SQL is multithreaded, one would expect it to be a fine candidate. However, it's not. The simple fact is that in SQL applications, it leads to a phenomenon known as cache thrashing. So, while the first thead is waiting for some more data, the second thread gets control, empties the cache and while waiting for the right data to turn up it all takes much longer than if one would not have hyperthreading. Intel has been asked about this by some large web sites, but apparently never responded. It's a fact that is not widely known, or even acknowledged, and the reason I'm mentioning it here.
I have personally worked on SQL servers at several customers and where performance was slow, I have looked at what could be done. I have not trusted hyperthreading since it's introduction, and I read the reports and benchmarks at the time indicating that graphic software like video editors would benefit but not much more. So, I tried my luck at turning hyperthreading off. Bingo, a huge jump in performance. So, last year, during a contract as DBA for a large non profit governmental company, I was confronted by a sluggish SQL / IIS / ASP.Net setup running a well known accounting application. The customer was not happy at all, his end users had to wait for a certain query to finish and it took about 45 seconds. By turning hyperthreading off, I was able to bring the execution time of this one query down to 5 seconds. It was not the only thing I changed on his server, but by baselining before turning hyperthreading off and measuring the result, I could prove that this change alone contributed to having off 40 seconds.
If you have a SQL server that has bad performance (which is not something you have to rely on an end user to tell you) it is worth investigating. How do you go about it?
If you know what's inside your box exactly, proceed, no damage done if you find out you barked up the wrong tree. Servers have been rebooted often for lesser reasons.
If you don't, make sure you know what hardware you have. If you have a AMD processor, forget it. AMD has no hyperthreading. If you open Task Manager and see only one processor, either you don't have a hyperthreaded processor, or it's disabled. If you see an uneven number (AMD has triple cores), you don't have hyperthreading. Don't rely on Task Manager to tell you if you have hyperthreading: if you see two CPU's in there, it may be separate cores or it may be the virtual cores that the OS "sees".
Look into your BIOS. On HP and Dell servers, you can access your BIOS from within Windows, via Dell Openmanage for example. Check the CPU (not the OS stuff, but the hardware level). If it shows hyperthreading as a feature, it should also show if it's enabled or not. If it doesn't show, the only hope is to reboot and go into the BIOS and check it there.
Try some CPU identification utility, which is risky, because you need to install something on the box.
Once you have determined that you do (probably) have hyperthreading enabled, it's a good idea to measure performance up front. You may be lucky that your SQL setup has some SQL intensive jobs scheduled. Noting how long they take to finish may be a good start.
Otherwise Start SQL Profiler. Get an idea which SQL queries pop up a lot and check it's duration. Try and find a relationship with the end user doing something, like running a report. If it's the only way, measure the user's actions with a stopwatch. Run several of these measurements, the second run may take less time, because of the way SQL optimizes queries.
Now it's time to shut hyperthreading up. To do that, you need to reboot the server. So find a good time to do this. Unfortunately, as you need to interrupt the boot process, you can't schedule this.
Interrupt the boot process, go into the CPU settings and find features of the processor(s). Disable hyperthreading if it's there and reboot.
Repeat your measuring process. My first run after rebooting took 40 seconds, the second and further runs finished in no more than 5 seconds.
Now this spectacular improvement might seem outrageous, but it's not. One other instance where I applied this was a script a co-worker used to accumulate information from 20 sites into one database. The full run took 2 days, one major site took 2 hours for the SQL intensive part to finish. By just disabling hyperthreading, we shortened it to 45 minutes. Your mileage may vary. However, I feel that there are not many more things you can do to SQL that will make such a difference, and cost no more than a reboot.
So, this is truly a case of more is less. Enjoy. Feedback is welcome.
Disclaimer : Hyperthreading is not the cause for all performance problems. So, disabling it might not fix your problem, however if you do have high CPU in an OLTP environment then I have no problems recommending disabling it as a starting point. I am sure you will be pleasantly surprised (and relieved).
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)