Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 230 | Views in the last 30 days: 1
 
Related Articles
BLOG

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...

BLOG

All In & Being Competitive

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

ARTICLE

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.

FORUM

database query

query database and instancename

BLOG

SQL Cruise competition, and the winner is …

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

Tags
editorial    
sql puzzles    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones