Anytime anyone runs any command on a SQL server, it changes the conditions of that SQL server. Execution plans get created, different objects are stored in memory, etc. I don’t like to use time as a performance tuning metric (I’m a big fan of logical reads), but most people do – especially executives, so when measuring execution time, it’s important to compare apples to apples.
Below is a script, which I call “Almost a Reboot” as it’s almost like rebooting server, that I run on a server anytime I’m tesing performance. I got the initial script from Kimberly Tripp (Blog/Twitter) at DevConnections in about 2004 – I’ve tweaked it when SQL2005 came out and it’s probably due for a few changes for SQL2008, so don’t be shy to offer suggestions.
My steps for performance tuning any SQL code are:
- Run this script
- Run the original code
- Record the results
- Make my change
- Run this script again
- Run the changed code
- Record/Compare the results
--Almost a reboot-- DECLARE @DBName Varchar(255) Set @DBName = <<SET DBNAME HERE>> --Fill this in to the database you want to flush CHECKPOINT --writes dirty pages to disk DBCC FREEPROCCACHE --clears entire plan cache DBCC DROPCLEANBUFFERS --clears all clean data cache pages DECLARE @intDBID INTEGER SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = @DBName) --Recompile the stored procedures DBCC FLUSHPROCINDB (@intDBID) --clears all clean plan cache for specified database
Again, let me know if you have any good tips to update this.
Here are some related posts from the intraweb:
- An old article from Kalen Delaney here: Geek City: Clearing a Single Plan from Cache
- A good explanation from Brad McGehee here: Useful SQL Server DBCC Commands