SQL Server Query Time Testing

In SSMS, I may run a query that takes a while to run. If I run the query again, it completes almost instantly, making it difficult to see improvement or not when I make开发者_如何学JAVA modifications like add a new index.

How do people time queries so that they can accurately gauge if their changes actually made some improvement or not?

Note, I use sql server 2005

On a test/dev server, with production data volumes, you would clear the execution and data caches using the following:

CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

NB. Don't run this on production server

It's because, after the first time you execute a query, the data will be cached in RAM and the execution plan will be cached. So for subsequent calls, it can be retrieved much quicker - mainly due to the data cache as it's much quicker to get the data from RAM than hitting the disks.

So you would:
1) run the initial query and record stats
2) make the query/index change
3) clear cache per above
4) run the query and record the new stats

Also, just comparing the execution plans of before/after the change will give an indication of the difference - e.g. if you see a table scan replaced with an index seek.

You should look at the query execution plan - pay attention to index scans or table scans, you want seeking. In SSMS -> Query -> Include actual execution plan. Run you query again and you will see the results in a tab.

You are seeing that your results are being optimized - which will likely happen in production as well. However, you queries may have to contend with other production systems as well, which may remove your query results from cache from time to time.

http://www.sql-server-performance.com/tips/query_execution_plan_analysis_p1.aspx