In response to my recent post about analyzing query performance in SQL server 2005, my colleague Mr Rowland Jones emailed me a couple of additional points around best practice, which will further aid query performance. Here are the points he makes (in his very own words)
Owner Qualification
When writing the table names in your queries it is always best to state who you expect ‘owns’ your table. I.e. to which schema it belongs. In most cases (esp. on sql 2000) this is dbo. This saves sql server having to do this work for you. Cost wise it’s not massive at all but it is considered good form.
Checkpoint
When using DBCC DROPCLEANBUFFERS you should first issue a CHECKPOINT statement. BOL explains this quite nicely (and in far fewer words than I ever could) so I have copied the salient piece below:
To drop clean buffers from the buffer pool, first use CHECKPOINT to produce a cold buffer cache. This forces all dirty pages for the current database to be written to disk and cleans the buffers. After you do this, you can issue DBCC DROPCLEANBUFFERS command to remove all buffers from the buffer pool.
Here’s a link to the article
James