How can I clear the SQL Server query cache?

sql-server caching
PaulB picture PaulB · Dec 9, 2009 · Viewed 365k times · Source

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. Subsequent executes are back in < 1 sec.

How can I get SQL Server 2005 not to use any cached results? I've tried running

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

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

Answer

Saar picture Saar · Dec 9, 2009

Here is some good explaination. check out it.

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

CHECKPOINT; 
GO 
DBCC DROPCLEANBUFFERS; 
GO

From the linked article:

If all of the performance testing is conducted in SQL Server the best approach may be to issue a CHECKPOINT and then issue the DBCC DROPCLEANBUFFERS command. Although the CHECKPOINT process is an automatic internal system process in SQL Server and occurs on a regular basis, it is important to issue this command to write all of the dirty pages for the current database to disk and clean the buffers. Then the DBCC DROPCLEANBUFFERS command can be executed to remove all buffers from the buffer pool.

Related questions

How to remove cached server names from the Connect to Server dialog?

Or, to put it another way, where is SqlStudio.bin for SQL Server 2012? It doesn't seem to be in the place that would be expected by looking at this other SO question.

Read More
Does SQL Server CACHES Query Results?

When I run a query does the SQL Server caches the results? Because: When I run the below query: SELECT id FROM Foo WHERE Foo.Name LIKE '%bar%' The query runs for 40 seconds on the 1st time. But …

Read More
How to use SqlCacheDependency?

I need to implement SqlCacheDependency for a table which will depend on this query: SELECT Nickname FROM dbo.[User]. I have created a method for this purpose: private IEnumerable<string> GetNicknamesFromCache() { const String cacheValueName = "Nicknames"; var result = HttpRuntime.…

Read More