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.
If wishes were horses
Golbing Time Dimension
- Using Excel to generate Inserts for SQL Server
- SQL Server 2005 file sizes and filegrowth
- Mooncalves among the werewolves
- Junior Rangers
- Samuel Strickland describing some of the Kawartha Lakes
- Deciding against Code Collapse in SQL Server Management Studio 2008
- Data Obfuscation through Random String replacement in SQL Server
- SQL Server Performance and the IN clause
- Using SQLCMD with SQL Server 2005
- Jane Austen's Mafia
Tag! You’re it!2008 age Apocalypse art Atheism bicycling Biology book Canadian Christmas climate change comedy conservation cycling database Disney documentary environment environmental awareness Excel fantasy film game graphic novel Guelph Guelph Festival of Moving Media Guelph International Film Festival haiku hiking history humour Jane Austen long distance love Microsoft Montreal movie music mystery native Nature Neil Gaiman novel Old Growth Forest Oshawa performance philosophy Poem poetry Poetry Month quote recipe relationship review Robert J. Sawyer science science fiction short story silliness silly snow society Space opera speculative fiction Spring SQL SQL Server SQL Server 2005 Stephen King story The Education of Mike Moonblazer touring TV water writing