Is there a way to prevent SQL query re-calls from caching and be faster?

Question

I need to get some requests to start in ~ 1-2 seconds. I am working on optimizing them, but it takes ~ 20 seconds on the first call of any of them and ~ 1 in all subsequent calls. This makes it impossible to determine whether any changes accelerate any changes, because it always works ~ 1 second after that. I am not very familiar with SQL, but from what I was able to find out, something seems to be caching. I am trying to figure out how to prevent this, but nothing works. From what I found on Google, people suggested

DBCC FREEPROCCACHE 

or

 OPTION(recompile) 

None of them seem to work. Each request still works for ~ 1 second, the first time it took ~ 20. I just want to make sure that the changes I make cause improvements, and not that the improvements come from caching. Is there any other trick to do this?

Answer 1

DBCC FREEPROCCACHE is for the plan cache (query compilation), which is similar to the low gain in your request, and not the page buffer data cache, which is a big improvement in IO. To be consistent, you need to clear the buffer cache, by running a checkpoint in your database,

 CHECKPOINT DBCC DROPCLEANBUFFERS 

Answer 2

Today, at a technical event in basel, a SQL professional used a combination of the two suggestions above:

 DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE 

So I think it should work :-)

Answer 3

The reason for subsequent launches is faster because the execution plan is cached. Your changes in your code are either not significant enough to necessitate recompilation, or which they actually work. Try testing every launch with client statistics. At the top next to the Execute icon in Management Studio there is a button that you can turn on / off.

EDIT: clearer directions for enabling client statistics: from the top menu, select Query> Enable Client Statistics.

Answer 4

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

I think that the effect that you see is not due to the cached query plan, but because the sql server caches the query results when you first execute your query.