Recently I had to optimize some SQL-queries on Sql Server 2005. Therefore I used Sql Server Management Studio.
I run into the following two problems:
- The resolution of the timer is one second. Thats too inaccurate.
- The cache in Sql Server optimized subsequent runs of the same query. So i couldn’t tell if I improved the query or made it worse.
If found the following solution for both problems on http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic13664.aspx. Just insert the query you want to tune below the comment and you can run your query always under the same preconditions.
CHECKPOINT
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
go
DECLARE @Started datetime, @Ended datetime
SET @Started = CURRENT_TIMESTAMP
— insert query to tune here
SET @Ended = CURRENT_TIMESTAMP
SELECT DATEDIFF(ms, @Started, @Ended)
go