SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Speed Phreaks

By Phil Factor,

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.

Total article views: 246 | Views in the last 30 days: 1
Related Articles

Phil Factor SQL Speed Phreak Competition

If you have a database application that is running slowly, or is straining the server, what do you d...


All In & Being Competitive

Many of us in IT are competitive, maybe slightly more so than in other professions. Competition adds...


Red Gate Software announces DBA in Space competition winner

Find out who has been announced as the winner of Red Gate Software's DBA in Space competition.


database query

query database and instancename


SQL Cruise competition, and the winner is …

Earlier this month SQL Cruise, (an initiative created by Tim Ford (B|T)) and Dell Software announced...

sql puzzles