How to clear SQL Server query cache

Question

I've got a simple query running against SQL Server 2005

select * from Table where Col = 'someval'

The first time I execute the query can take > 15 secs. Subsiquent executes are back in < 1 sec.

How can I get SS2005 not to use any cached results. I've tried runnning

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

but this seems to have no effect on the query speed (still < 1 sec).

Answer 1

That is how you clean the cache (data and execution plan).

If the table is small/query is efficient then you won't notice much difference.

Try running SQL Profiler and recording stats like Reads, Duration and CPU. Compare those when you do and when you don't clear the cache - you should notice a difference when you're running with a "clean cache"

Answer 2

EXEC sys.sp_configure N'max server memory (MB)', N'2147483646'
GO
RECONFIGURE WITH OVERRIDE
GO

What value you specify for the server memory is not important, as long as it differs from the current one.

Btw, the thing that causes the speedup is not the query cache, but the data cache.

Answer 3

Here is some good explaination. check out it.

http://www.mssqltips.com/tip.asp?tip=1360

CHECKPOINT; 
GO DBCC DROPCLEANBUFFERS; 
GO