6

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.

Darren
  • 68,902
  • 24
  • 138
  • 144
ImGreg
  • 2,946
  • 16
  • 43
  • 65
  • So you are "constructing" these queries in code and then invoking them from code too? – Icarus Mar 26 '12 at 21:01
  • Yes @Icarus. I am building the queries as I go and executing them from the query windows in SQL Server. – ImGreg Mar 26 '12 at 21:04
  • SET STATISTICS IO ON can yield quality info as well as the actual execution plan, rather than relying upon query duration (which is dependent upon a number of factors). – Tim Lehner Mar 26 '12 at 21:05

3 Answers3

8

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

SQLMenace
  • 132,095
  • 25
  • 206
  • 225
  • Anything I should be concerned about with using this on a live database @DarrenDavies? I unfortunately can't make a test copy. – ImGreg Mar 26 '12 at 21:06
  • 1
    Yes..don't run this on a live DB since it will have to fetch all the data again and also procs will be recompiled – SQLMenace Mar 26 '12 at 21:08
  • and if I only have access to a live database to use, I should..? – ImGreg Mar 26 '12 at 21:11
  • In that case look at the execution plans and try to eliminate scans by making the queries `SARGable` – SQLMenace Mar 26 '12 at 21:13
  • I read somewhere that closing and reopening SQL Server will have basically the same effect as those commands, is this correct? – ImGreg Mar 26 '12 at 21:18
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.

Community
  • 1
  • 1
sgmorrison
  • 948
  • 7
  • 11
0

Include the Actual Execution Plan and execute the following command:

CHECKPOINT; 
GO 
DBCC DROPCLEANBUFFERS; 
GO
Darren
  • 68,902
  • 24
  • 138
  • 144