What is plan cache and how to clear the plan cache of a procedure in SQL Server?

Problem: What is plan cache and how to clear the plan cache of a procedure in SQL Server?

Solution:

Plan Cache: When we write a store procedure and execute it on a database, the SQL Server Query optimizer analyzes the query operation at the time of compiling or executing. And It creates a generic plan based on the parameters passed while the execution and this will remain as a standard cached plan when it tries to execute it again, and the engine is mandated to use the same plan if an execution plan already exists in the below set of views.

sys.dm_exec_query_plan - Returns the Showplan in XML format for the batch specified by the plan handle. The plan specified by the plan handle can either be cached or currently executing.

sys.dm_exec_procedure_stats - Returns aggregate performance statistics for cached stored procedures. The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view.

So, Sometimes the plan which has been created a year ago might not be applicable as the same beneficial plan. We may have to clear out the plan cache some way or recompile the stored procedure to get the new plan generated.

Clearing out the procedure cache:

Under the database console commands there is a maintenance statement which can be fired to get the plan cache cleared out, please see more details in the below section.  

DBCC FREEPROCCACHE - Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool.

Syntax:

DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]

Example: I would like to create a procedure and store the statistics for the procedure in the plan cache and clear the execution stats for the procedure.

-- Create a table type to hold multiple values.

  CREATE TYPE [dbo].[EmployeeType] AS TABLE

(

                [EmployeeID] [int] NOT NULL Primary key ,

                [employeetype] Varchar(32)  Null            

)

GO

-- Create a procedure to use the table type to pass as parameter.

Create  Procedure dbo.getallemployeesbyempID

@employeetable [EmployeeType] readonly

AS

BEGIN

SELECT

                E.[EmployeeID]

      ,E.[Name]

      ,E.[PhoneNumber]

                  ,E.[employeetype]

  FROM [dbo].[Employees] E

  INNER JOIN (Select* from @employeetable) ET ON

  ET.[EmployeeID]=E.[EmployeeID]

  AND ET.[employeetype]=E.[employeetype]

 

END

GO

Declare @EmployeeID as [EmployeeType]

-- insert into the table type variable to load the parameter with multiple employee id's

Insert Into @EmployeeID (employeeid,employeetype)

VALUES

(100122,'Developer'),

(100123,'Tester'),

(100124,'Developer')

--execute the procedure to pass the table type variable which is loaded.

EXEC dbo.getallemployeesbyempID @EmployeeID;

--get the plancache details of the procedure from procedure stats dmv.

SELECT TOP 10 d.object_id, DB_NAME(d.database_id) as database_name, OBJECT_NAME(object_id, database_id) 'proc name',  

    d.cached_time, d.last_execution_time, d.total_elapsed_time, 

    d.total_elapsed_time/d.execution_count AS [avg_elapsed_time], 

    d.last_elapsed_time, d.execution_count 

FROM sys.dm_exec_procedure_stats AS d 

WHERE DB_NAME(d.database_id)='DB'

ORDER BY [total_worker_time] DESC; 

-- getting the cached plan from query_plan and cached plan dmv's

SELECT usecounts,

                object_Schema_name(objectid) + '.' + OBJECT_NAME(objectid) AS PROCEDURE_NAME,

                plan_handle,

                query_plan

FROM sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)

WHERE dbid = DB_ID()

AND objtype = 'Proc'

                AND  object_Schema_name(objectid) + '.' + OBJECT_NAME(objectid)='dbo.getallemployeesbyempID'

OUTPUT Before clearing the cache :

 -- clearing the plan cache usign the planhandle

                DBCC FREEPROCCACHE (0x05000E0099348E30305D08E80300000001000000000000000000000000000000000000000000000000000000) ;

OUTPUT after clearing the cache: