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
- Laurier in Chain e-mails on Immigration
- Junior Rangers
- Georgian Manor, hard sells and pyramid schemes
- SQL Server 2005 file sizes and filegrowth
- Cellular Distopia
- Billy and Bessy Cheesecake on a Toasted Almond Crust
- Sunday afternoon at Camp Restall
- Killer Diller
Tag! You’re it!
- climate change
- environmental awareness
- graphic novel
- Guelph Festival of Moving Media
- Guelph International Film Festival
- Jane Austen
- long distance
- Miss Celia Leblanc
- Neil Gaiman
- Old Growth Forest
- Poetry Month
- Robert J. Sawyer
- science fiction
- short story
- Space opera
- speculative fiction
- SQL Server
- SQL Server 2005
- Stephen King
- The Education of Mike Moonblazer