How can I clear the 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. 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).

DUPLICATE: stackoverflow.com/questions/1856966/… but better — Faiz
– Faiz, Commented Jan 21, 2013 at 8:27
Does this answer your question? How to let SQL Server know not to use Cache in Queries? — Jim G.
– Jim G., Commented Jun 1, 2021 at 18:16

Sheridan · Accepted Answer · 2015-08-21 11:17:30Z

298

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.

edited Aug 21, 2015 at 11:17

Sheridan

70.1k24 gold badges151 silver badges193 bronze badges

answered Dec 9, 2009 at 10:45

Saar

8,5245 gold badges33 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

jaraics Over a year ago

One maight also include DBCC FREEPROCCACHE

Kris Nobels Over a year ago

When using dropcleanbuffers this is for everybody that is connected to the database or only for that user?

Saar Over a year ago

@Kris: DBCC DROPCLEANBUFFERS, removes all clean buffers from buffer pool. This is necessary step in query performance tuning and one should not use it on live SQL Server.

MSC Over a year ago

This works well for SQL Server, but please note that this does not work in SQL Azure - I've posted an alternate solution below to handle the SQL Azure scenario.

gbe Over a year ago

Good, this is the only command that actually works, tried many other and didn't worked.

|

Community · Accepted Answer · 2020-06-20 09:12:55Z

Eight different ways to clear the plan cache

1. Remove all elements from the plan cache for the entire instance

DBCC FREEPROCCACHE;

Use this to clear the plan cache carefully. Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance.

2. Flush the plan cache for the entire instance and suppress the regular completion message

"DBCC execution completed. If DBCC printed error messages, contact your system administrator."

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

3. Flush the ad hoc and prepared plan cache for the entire instance

DBCC FREESYSTEMCACHE ('SQL Plans');

4. Flush the ad hoc and prepared plan cache for one resource pool

DBCC FREESYSTEMCACHE ('SQL Plans', 'LimitedIOPool');

5. Flush the entire plan cache for one resource pool

DBCC FREEPROCCACHE ('LimitedIOPool');

6. Remove all elements from the plan cache for one database (does not work in SQL Azure)

-- Get DBID from one database name first
DECLARE @intDBID INT;
SET @intDBID = (SELECT [dbid] 
                FROM master.dbo.sysdatabases 
                WHERE name = N'AdventureWorks2014');

DBCC FLUSHPROCINDB (@intDBID);

7. Clear plan cache for the current database

USE AdventureWorks2014;
GO
-- New in SQL Server 2016 and SQL Azure
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

8. Remove one query plan from the cache

USE AdventureWorks2014;
GO

-- Run a stored procedure or query
EXEC dbo.uspGetEmployeeManagers 9;

-- Find the plan handle for that query 
-- OPTION (RECOMPILE) keeps this query from going into the plan cache
SELECT cp.plan_handle, cp.objtype, cp.usecounts, 
DB_NAME(st.dbid) AS [DatabaseName]
FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st 
WHERE OBJECT_NAME (st.objectid)
LIKE N'%uspGetEmployeeManagers%' OPTION (RECOMPILE); 

-- Remove the specific query plan from the cache using the plan handle from the above query 
DBCC FREEPROCCACHE (0x050011007A2CC30E204991F30200000001000000000000000000000000000000000000000000000000000000);

Source 1 2 3

MSC · Accepted Answer · 2016-05-19 15:24:56Z

8

Note that neither DBCC DROPCLEANBUFFERS; nor DBCC FREEPROCCACHE; is supported in SQL Azure / SQL Data Warehouse.

However, if you need to reset the plan cache in SQL Azure, you can alter one of the tables in the query (for instance, just add then remove a column), this will have the side-effect of removing the plan from the cache.

I personally do this as a way of testing query performance without having to deal with cached plans.

More details about SQL Azure Procedure Cache here

answered May 19, 2016 at 15:24

MSC

2,0411 gold badge18 silver badges23 bronze badges

4 Comments

Meneghino Over a year ago

This did not work for me, then plan was unchanged. Please see here stackoverflow.com/questions/46987785/…

Yster Over a year ago

After testing, it seems that adding and removing a column has no effect on the cache.

d219 Over a year ago

The above two comments make it look like this is not a valid answer but it is. I've just added and removed a column in a SQL DB and execution had dropped from over a minute to a few seconds.

d219 Over a year ago

(although having said that it's rapidly reverted to being over a minute.. - even with ARITHABORT ON)

Tony Basallo · Accepted Answer · 2016-10-25 03:08:38Z

8

While the question is just a bit old, this might still help. I'm running into similar issues and using the option below has helped me. Not sure if this is a permanent solution, but it's fixing it for now.

OPTION (OPTIMIZE FOR UNKNOWN)

Then your query will be like this

select * from Table where Col = 'someval' OPTION (OPTIMIZE FOR UNKNOWN)

edited Oct 25, 2016 at 3:08

answered Nov 5, 2012 at 18:33

Tony Basallo

3,1342 gold badges36 silver badges49 bronze badges

4 Comments

pabrams Over a year ago

Incorrect syntax near the keyword 'OPTION'. or Incorrect syntax near 'UNKNOWN'.

Mark Avenius Over a year ago

@pabrams These go after (as part of) your query like so: select * from Table where Col = 'someval' OPTION (OPTIMIZE FOR UNKNOWN)

Michael K. Campbell Over a year ago

Just make ABSOLUTELY sure you don't inadvertently drop something like this into PRODUCTION code - because that could cause MAJOR issues down the road.

user2864740 Over a year ago

OPTIMIZE FOR UNKNOWN does not ignore cached plans. Rather, when generating a plan it instructs SQL server to choose "average distribution values, independent of any [automatic] parameterization" for deciding what plan to create - this results in plans that may be more consistent across non-uniform statistics. An OPTION (RECOMPILE) creates a new plan, but does not otherwise clean/release the data cache - this usually generates more ideal plans at the expense of plan regeneration and plan caching costs.

erikkallen · Accepted Answer · 2009-12-09 10:56:12Z

4

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.

edited Dec 9, 2009 at 10:56

answered Dec 9, 2009 at 10:43

erikkallen

34.6k14 gold badges90 silver badges121 bronze badges

Collectives™ on Stack Overflow

How can I clear the SQL Server query cache?

5 Answers 5

6 Comments

Eight different ways to clear the plan cache

1. Remove all elements from the plan cache for the entire instance

2. Flush the plan cache for the entire instance and suppress the regular completion message

3. Flush the ad hoc and prepared plan cache for the entire instance

4. Flush the ad hoc and prepared plan cache for one resource pool

5. Flush the entire plan cache for one resource pool

6. Remove all elements from the plan cache for one database (does not work in SQL Azure)

7. Clear plan cache for the current database

8. Remove one query plan from the cache

Comments

4 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

Eight different ways to clear the plan cache

1. Remove all elements from the plan cache for the entire instance

2. Flush the plan cache for the entire instance and suppress the regular completion message

3. Flush the ad hoc and prepared plan cache for the entire instance

4. Flush the ad hoc and prepared plan cache for one resource pool

5. Flush the entire plan cache for one resource pool

6. Remove all elements from the plan cache for one database (does not work in SQL Azure)

7. Clear plan cache for the current database

8. Remove one query plan from the cache

Comments

4 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related