Tuning SQL Server Queries

As everyone who has worked with a useful database for any length of times knows… they get bigger. And as they get bigger it takes longer to squeeze information or data out. So what’s the problem? You might think it’s the fault of the computer…. blame the system! Heck! I’ve done it. But if you are seeing noticeable degradation of your queries in a short period of time then, 9 times out of 10, you gots to fix da code man.
And that takes experience and learning.
One of the best sources I can give you for really learning the ins and outs of Transact SQL (the Microsoft flavour of SQL in SQL Server) then there’s one book you should look at: Ken Henderson’s The Guru’s Guide to Transact-SQL. It is, hands down, the best book on practical SQL that I use. It never leaves my desk and my only wish is that Ken would write an up dated version. I would buy it right away.
But there are simple things you can do to get a good idea about where your problem areas are. The two settings I like to use are the statistics io and time settings. These really help, especially the first one, to give you a clear idea of the processing ‘cost’ of a database query. The basic commands are:
set statistics io on
set statistics time on

Turning them off is easy: just substitute the on with off. Once you’ve done that they’re on for your entire session. When you execute a query you’ll get some additional processing information but, in my opinion, the most important performance counter in them is the logical reads number. This is the number of 8K pages of information it read from the data cache to give you your result. Of course, the less pages, the better. It’s also a useful value because it is independent of a lot of the other ‘background’ noise like other users and locking that most of our queries have to deal with in the read database world. As long as you are returning the same rows of data and using the same command this value should also remain very close to the same amount.
Try it.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s