227

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).

shA.t
  • 16,580
  • 5
  • 54
  • 111
PaulB
  • 23,264
  • 14
  • 56
  • 75
  • DUPLICATE: http://stackoverflow.com/questions/1856966/how-to-let-sql-server-know-not-to-use-cache-in-queries but better – Faiz Jan 21 '13 at 08:27
  • Does this answer your question? [How to let SQL Server know not to use Cache in Queries?](https://stackoverflow.com/questions/1856966/how-to-let-sql-server-know-not-to-use-cache-in-queries) – Jim G. Jun 01 '21 at 18:16

5 Answers5

288

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.

Sheridan
  • 68,826
  • 24
  • 143
  • 183
Saar
  • 8,286
  • 5
  • 30
  • 32
  • 14
    One maight also include DBCC FREEPROCCACHE – jaraics Sep 28 '11 at 07:42
  • 2
    When using dropcleanbuffers this is for everybody that is connected to the database or only for that user? – Kris Nobels Oct 28 '15 at 14:48
  • 1
    @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. – Saar Oct 30 '15 at 19:23
  • 1
    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. – MSC May 19 '16 at 15:25
  • @Saar: You replied to @Kris that `DROPCLEANBUFFERS` removes all clean buffers from buffer pool. Is this for the entire database-server or only for a certain database that is hosted inside the server? This [msdn DBCC DROPCLEANBUFFERS](https://msdn.microsoft.com/en-us/library/ms187762.aspx) page contains `... This forces all dirty pages for the current database to be written to disk and cleans the buffers. ...` I assume that database means that its effect are restricte f.e. to `AdventureWorks` without affecting the `Northwind`-Database on the same server? – surfmuggle Feb 17 '17 at 09:04
  • 1
    Good, this is the only command that actually works, tried many other and didn't worked. – Gabriel Rodriguez Feb 23 '17 at 19:08
48

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

Community
  • 1
  • 1
Somnath Muluk
  • 55,015
  • 38
  • 216
  • 226
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

MSC
  • 2,011
  • 1
  • 16
  • 22
  • 1
    This did not work for me, then plan was unchanged. Please see here https://stackoverflow.com/questions/46987785/actual-execution-plan-not-updated-after-change-in-azure-sql-database-stored-proc – Meneghino Oct 28 '17 at 09:02
  • After testing, it seems that adding and removing a column has no effect on the cache. – Yster Nov 10 '20 at 21:24
  • 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 Feb 09 '21 at 15:21
  • (although having said that it's rapidly reverted to being over a minute.. - even with ARITHABORT ON) – d219 Feb 09 '21 at 15:34
7

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)
Tony Basallo
  • 3,000
  • 2
  • 29
  • 47
  • 2
    Incorrect syntax near the keyword 'OPTION'. or Incorrect syntax near 'UNKNOWN'. – pabrams Aug 26 '13 at 17:53
  • 1
    @pabrams These go after (as part of) your query like so: `select * from Table where Col = 'someval' OPTION (OPTIMIZE FOR UNKNOWN)` – Mark Avenius Aug 13 '14 at 15:08
  • 1
    Just make ABSOLUTELY sure you don't inadvertently drop something like this into PRODUCTION code - because that could cause MAJOR issues down the road. – Michael K. Campbell Oct 22 '15 at 20:49
  • 5
    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. – user2864740 Oct 24 '16 at 19:55
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.

erikkallen
  • 33,800
  • 13
  • 85
  • 120