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
- Laurier in Chain e-mails on Immigration
- Using Excel to generate Inserts for SQL Server
- Cat curled up by register in the washroom
- Data Obfuscation through Random String replacement in SQL Server
- Biotype Editorial, Dec. 1990
- Celebrating Odd Day
- Deciding against Code Collapse in SQL Server Management Studio 2008
- Veni, Vidi, Deus Vicit on this day in 1683
- Boy, Roald Dahl and early Sensory Science
- Creating a SQL Server database by script
Tag! You’re it!
- climate change
- environmental awareness
- graphic novel
- Guelph Festival of Moving Media
- Guelph International Film Festival
- Jane Austen
- long distance
- Old Growth Forest
- Robert J. Sawyer
- science fiction
- short story
- Space opera
- speculative fiction
- SQL Server
- SQL Server 2005
- Stephen King
- The Education of Mike Moonblazer