This is a guest editorial from Phil Factor
There is an art in being able to squeeze the highest level of performance from a SQL Server. When the ratio of Database Servers to windows machines was one-to-one, there was certain logic in tolerating slow queries. Just as long as the CPU usage didn’t go over 60%, and the end user didn’t suffer more than a 600 millisecond latency, nobody got upset, and a CPU was there to be used. Hiring database developers who understood their craft was an expensive matter, and if you could get away with the slow- grinding output of the code-jockey, then all was well and good in the eyes of the project manager. The equation altered when it became possible to save on metal by placing several SQL Server instances on one server. The use of CPU-hogging routines became a more expensive shortcut. Now, we can virtualize any number of SQL Servers as long as they are frugal with resources. Databases must now be ‘sociable’, in that you can no longer assume that you can hog resources. They must be written in a way that keeps all options open for deployment.
The difference between a well-tuned query and a poor one is enormous, both in the elapsed time and the CPU usage. It is common to find improvements of a hundredfold. This represents real cost-savings to the business. These slow-running queries have never been so easy to find. A simple DMV will flush them out, and from then on, it is a simple matter of experimenting with algorithms and indexes. I refer to it as an art, because, whereas there are certain clear guidelines to getting queries that perform well and scale well, so much relies on intuition and inspiration . In this art, we’re all learners who can appreciate each others work.
On SQL Server Central, a group of us who are fascinated by the art of writing fast queries against tables of at least a million rows have been holding regular competitions as a way of swapping ideas and trying out unexpected challenges. It is a great way for a community such as ours to work. The winner of each competition gets a $60 Amazon voucher (Thanks, RedGate), and has to set the next competition. It is great to see the work of SQL speed-freaks such as Gianluca, Dave Ballantyne, Peso and Barry Young in The 'FIFO Stock Inventory' SQL Problem or the Subscription List problem. I describe the competition in more detail here in my blog . It would be great if you joined us for the next competition in the Phil Factor SQL Speed Phreak series.