How can I clear the SQL Server query cache?

Question

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.

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

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

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)

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.