Menu
I am using SQL Server 2008 R2.
I know that DBCC FREEPROCCACHE will clear cache of all stored procedures in SQL Server.
But what I need is to clear cache of only 1 stored procedure. How can I do that?
The Stored Procedure name is Rpt_RegionReport. I don't want to execute the stored procedure with WITH RECOMPILE option.
291
We can use the DBCC FREEPROCCACHE command to clear the procedural cache in SQL Server. We might drop a single execution plan or all plans from the buffer cache. SQL Server needs to create new execution plans once the user reruns the query.
Purge the query plan cache from each Compute node and from the Control node. Starting with SQL Server 2016 (13. x), the ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE to clear the procedure (plan) cache for the database in scope.
Syntax. DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ] plan handle uniquely identifies a query plan for a batch that has executed and whose plan resides in the plan cache. sql_handle is the SQL handle of the batch to be cleared.
By cleaning the buffer pool before each test run SQL Server will have to re-read the data it needs from disk. To clean the buffer pool you execute the command: DBCC DROPCLEANBUFFERS. Next you should remove your execution plans from the procedure cache.
DBCC FreeProcCache has a single optional argument - the ID of the execution plan you want to delete.
You can find the plan you want to delete using sys.dm_exec_cached_plans, and then you can just use it as
DBCC FREEPROCCACHE (0x0123456....);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
