Remove Procedure Cache and Reset Wait Stats

Remove Procedure Cache and Reset Wait Stats

Remove all elements from the plan cache for the entire sql server instance

DBCC FREEPROCCACHE;

Flush the cache and suppress the regular completion message

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

Remove all elements from the plan cache for a specific database

DECLARE @intDBID INT;
SET @intDBID = (SELECT [dbid]
                FROM master.dbo.sysdatabases
                WHERE name = 'AdventureWorks');

-- Flush the procedure cache for one database only
DBCC FLUSHPROCINDB (@intDBID);

Remove the specific plan from the cache using the plan handle

-- Remove one plan from the cache
-- Get the plan handle for a cached plan
SELECT cp.plan_handle, st.[text]
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%/* ProcedureName %';

-- Remove the specific plan from the cache using the plan handle
DBCC FREEPROCCACHE (0x05000800F7BA926C40C1505507888000000000000000000);

Reset wait and latch statistics

DBCC SQLPERF('sys.dm_os_latch_stats' , CLEAR)
DBCC SQLPERF('sys.dm_os_wait_stats' , CLEAR)

PLAN CACHE POLLUTION

While the plan cache generally is self-maintaining, poor application coding practices can cause the plan cache to become polluted with a large number of query plans that are unlikely to be reused.Query to extract the query plans which are used only once and options to remove them.
Query to find out the plans which are used only once and options to handle.This also indicates ad-hoc workload to the server

SELECT   [text],
         cp.size_in_bytes,
         plan_handle
FROM     sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE    cp.cacheobjtype = N'Compiled Plan'
         -- AND cp.objtype = N'Adhoc'
         AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;

DBCC FREEPROCCACHE (plan_handle);

-- Clean all the caches with entries specific to the resource pool named "default".
DBCC FREESYSTEMCACHE ('ALL', 'default');

/*
 The following example uses the MARK_IN_USE_FOR_REMOVAL
 clause to release entries from all current caches once the entries become unused.
*/
DBCC FREESYSTEMCACHE ('ALL')
    WITH MARK_IN_USE_FOR_REMOVAL;