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.

Find column names in a table

Thought this might save sometime going through Books Online to find this; There are few ways to do this in SQL Server 2005 onwards.

Useing Information_Schema.Columns is the best out of those. This is because it returns results in a readable format. Below is how you can do this;

SELECT * FROM information_schema.columns  WHERE Table_Name = 'YourTableName' 

Alternately you can use SP_Columns as well, usage is as below;

EXEC sp_Columns 'YourTableName'

In addition to this if you want to say get all the columns in a database which has a particular name or part of the name in it, this can be also achieved using a script like this;

SELECT * FROM information_schema.columns
WHERE Column_Name LIKE  '%url%'

This will return all columns which are having url as part of the name.

Find referenced tables of a stored procedure with sys.sql_expression_dependencies

During fixing one of the performance issues of a stored procedure I wanted to find the tables being used in this complex stored procedure. Doing this manually would have been a nightmare, but there is a system view (Microsoft called them as Object Catalog Views) which we can query to get these results.

If you want to get the referenced tables of a particular stored procedure you can use below query;

SELECT

OBJECT_NAME(referencing_id) AS SP_NAME,

referenced_class_desc,

referenced_database_name,

referenced_entity_name

FROM sys.sql_expression_dependencies

WHERE

OBJECT_NAME(referencing_id) = 'YourStoredProcedureName'

There are quite a bit of additional columns with additional details in the view. You can find more details from this MSDN article.

http://msdn.microsoft.com/en-us/library/bb677315.aspx

This is only available in SQL Server versions 2008 and upwards.