How to benchmark queries on Sql Server

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:

  1. The resolution of the timer is one second. Thats too inaccurate.
  2. 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