SQL Server Query Time Testing

Question

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 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

AdaTheDev · Accepted Answer · 2011-05-09 21:39:20Z

3

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.

answered May 9, 2011 at 21:39

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sam · Accepted Answer · 2011-05-09 21:22:36Z

2

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

edited May 9, 2011 at 21:22

answered May 9, 2011 at 21:17

Sam

7,6987 gold badges55 silver badges62 bronze badges

2 Comments

Martin Smith Over a year ago

Making modifications like adding a new index would invalidate the plan and cause it to be recompiled anyway so I assume it is the fact that the data pages are already in the buffer cache that is really making the difference here.

Sam Over a year ago

Sounds like a reasonable assumption. You can clear out the buffer cache - wouldn't really recommend doing it unless you are planning on never having a result cached. msdn.microsoft.com/en-us/library/ms187762.aspx

Collectives™ on Stack Overflow

SQL Server Query Time Testing

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related