View SQL Server Plan Cache

The major reason we use Stored Procedures is because it allows SQL Server to cache the Execution plan so it can be reused. This gives a performance boost as the Query Optimizer doesn't have to go through the expensive steps of choosing the right query plan for the query.

But have you ever actually seen whether your stored procedure is actually has a plan cached in the server or whether it is been reused? There are quite a lot of scenarios Query Optimizer is forced to do a recompile of a Procedure which can be a huge performance hit. It is entirely a different topic for another article which I will write in the future. 

In this article I am going to show you how you can view whether a procedure is cached or not, how many times it has been reused, etc.This is the query which you can use to view these information;

SELECT UseCounts,RefCounts, Cacheobjtype, Objtype, 
ISNULL(DB_NAME(dbid),'MissingDB') AS DBName, TEXT AS SQLSTMT 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 

This will return below columns;

UseCounts	Number of times the plan has reused since caching
RefCounts	Number of times the other cached objects reused this plan
Cacheobjtype	Object type out of one of the following;                                                        Compiled Plan Parse Tree Extended Proc CLR Compiled Functions CLR Compiled Procedures
Objtype	Object type with ragards to the database (SP, View, Trigger,  etc)
DBName	Database name which the cached object belongs to
SQLSTMT	SQL Statement Cached

However execution plans are not kept in the cache forever. They removed from the cache if one of the following criteria's met;

• More memory is required by the system
• The "age" of the plan has reached zero
• The plan isn't currently being referenced by an existing connection

Aging is happens using a formula that multiplies the estimated cost of the plan by the number of times it has been used (UseCounts). There is a SQL Process called "Lazywriter" (Love the name) which is responsible for removing plans from the cache.


You can see this at work, first select a particular SP. Then simply restart the SQL Server Service and then run the above script. You wont see your SP in the cache, then run the SP. Then run the above script again, then you can see your SP is cached. Running again you will see the UseCounts column increases if your SP is not recompiling at each run.

This is a good way to be sure your SP is not recompiling as there are lot of situations where it does.