The sun is shining, the birds are singing, that girl on the bus smiled at you and your boss just agreed to buy you a new database server. Everything is coming up Milhouse. And then you discover your new server is slower at SQL queries than the old one. To spice things up a bit they aren’t just slower, the speeds are erratic and inconsistent. Each time you benchmark a query, you get wildly different results. You were about to tell your boss what a terrible mistake you made and commit seppuku on his Persian rug, which had only just been dry cleaned, when you considered Googling the issue and found this article.
One of our developers spent many sleepless nights trying to figure out the cause of this problem. He tried to optimise queries by hand, using server analysers to figure out where the hit was coming from. By the time he found the ridiculously simple solution he had already descended from the mountain after a hundred days of solitude as a database master. We even forgot to feed him a few times, although being Eastern European we assume he’s used to that.
Fortunately for you and your loved ones, your problem is as simple as driving with the hand brake on. Since Windows Server 2008 the default power setting has been “Balanced”. This means your server is trying to be an eco-warrior and save a couple of polar bears behind your back, by reducing its energy consumption in exchange for performance. Remember that option on your laptop that dims the screen a bit to keep your battery going longer? Microsoft put that in your “Super Enterprise Data Center Mission Critical Edition R2″ server.
In fact, they wrote a whole white paper about this fabulous innovation. The theory is in “Balanced” mode the CPU will run at a lower clock speed when it is under low workload, and bring more resources online when there is more to do. As great as it sounds, this won’t always be your best option. If your workload is incredibly bursty (quick moments of intense work), your SQL query speeds will take a hit, as by the time Windows has decided it needs more resources the work may already be done.
As stated in this hilarious Knowledge Base article, changing your power options to “High Performance” (an option Microsoft conveniently hides under a “more” menu to prevent you from accidentally finding it) resolves this issue by using more power all the time. In return, you need to accept that global warming is entirely your fault. Amazingly, no performance analyser of SQL server suggests you change this option.
You can see the effect on page load speed of our product page in the below graph. The response time changed from an erratic 100-300ms, spiking occasionally to as much as 1.5s to a beautifully smooth 100ms. Not a bad day’s work for changing a radio button.