6

I need to get several queries to run in ~1-2 seconds. I've been working on optimizing them, but it takes ~20 seconds the first time any of them are called and ~1 on all subsequent calls. This is making it impossible to tell if any changes I make are speeding up the query, because it always runs in ~1 second afterwards. I'm not incredibly familiar with SQL, but from what i've been able to learn it seems like something is caching. I'm trying to figure out how to prevent this, but nothing seems to work. From what i've found off Google, people have been suggesting 

DBCC FREEPROCCACHE

or

OPTION(recompile)

Neither of these seem to work though. Each query is still running in ~1 second when they took ~20 the first time. I just want to make sure the changes i'm making are causing improvements, and not that the improvements are coming from caching. Is there some other trick to do this?

user1652427
  • 697
  • 2
  • 8
  • 21
  • 1
    So, you want it to take ~20 seconds to run the query every time? Why? – Guffa Nov 05 '13 at 17:51
  • Yes, I just want consistent results so when I make a small change I make sure it's not because of caching. – user1652427 Nov 05 '13 at 17:51
  • Are you trying to prevent the ~20 runtime on the initial run, or slow down the other queries? – AllenG Nov 05 '13 at 17:52
  • He wants to slow down the other queries for performance testing. I have the same problem: It's hard to know if your change helped performance when caching is altering query runtime. – Jack Nov 05 '13 at 17:53
  • possible duplicate of [How can I clear the SQL Server query cache?](http://stackoverflow.com/questions/1873025/how-can-i-clear-the-sql-server-query-cache) – Guffa Nov 05 '13 at 17:55
  • The two options you mentioned prevent the cached execution plan from being executed. I think what you are seeing is that the results on the first query are not yet in memory. When SQL gets results they are loaded into memory so that subsequent calls do not need to hit the hard disk (this explanation is of course horribly simplified). My suggestion would be to research a way to have sql have that data loaded already (not sure if this is even possible) then the query would presumably run in ~1 second every time. –  Nov 05 '13 at 18:02

4 Answers4

2

DBCC FREEPROCCACHE is for the plan cache (query compilation) which is likeky to be a small gain in your query, not the page buffer data cache which is a big improvement on IO. To be consistent, you need to clear the buffer cache, after having done a checkpoint in your database, with

CHECKPOINT
DBCC DROPCLEANBUFFERS
ARA
  • 1,296
  • 10
  • 18
1

Today at the techday event in basel a SQL professional did use the combination of the 2 suggestions above:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

So I think it should work :-)

Reto
  • 102
  • 3
0

The reason subsequent runs are faster is because the execution plan is cached. Your changes to your code are either not significant enough to cause the need for a recompile, or they are actually working. Try testing each run with Client Statistics on. There is a button at the top next to the Execute icon in Management Studio that you can toggle on/off.

EDIT: Clearer directions to turn on client statistics: at the top menu, click Query > Include Client Statistics.

HOT theChunKk
  • 181
  • 6
0

you should try dbcc dropcleanbuffers (MSDN: Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.)

i think the effect you see is not because of a cached query plan but because the sql server caches the query results the first time you execute your query.

PrfctByDsgn
  • 1,022
  • 1
  • 14
  • 18