Testing for Performance in Multiple SQL Queries

Question

I'm working to improve the efficiency of some SQL Queries on SQL-Server-2008. There are different ways of performing each query and I want to find the fastest of them.

However, the issue that I'm having is that I am having trouble determining which is actually executing faster. Ideally I could just run each query one after the other and see which runs fastest. Ideally...

Problem is, is that SQL is too smart for my liking. When constructing these queries I run them multiple times. When I do this, the queries' efficiencies improve on their own. This I would imaged is because of some behind-the-scenes stuff that SQL does. What is this? How can I avoid it?

For example, I run the query once and it takes 30s. I run it again and it takes 10s. The more I run the query the faster it seems to run.

So.. Is there any way of "clearing the cache" or whatever the equivalent would be in SQL? I want to get an accurate indication of which query is going to actually run faster. Alternatively, what would be the best way to do the type of testing that I want?

Any information in regards to this topic would be accepted as valid input.

Answer 1

When the query is run first most likely the data is still on disk, SQl Server has to fetch this data, when you run the same query the data is already in RAM and thus it will be much faster than going to disk

run DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE to clear the cache without doing a restart

You need to look at execution plans, statistics io and statistics time to really see what is going on. in the plan look for conversions and also for scans (you want seeks if possible).

See also Client Statistics in SSMS. Check execution times

Answer 2

The improvement in speed that you see is a result of the database's query cache. Most relational DB engines have this feature, which caches the result of a query until the table(s) you read from are updated.

This post gives good pointers on how to work around this for performance tuning. You should also look into Execution Plans, which show you how the database would run the query, without actually running it. The benefit of this is that you can see if full table scans are being done where an index could be used instead.