First you need to store the sql server plan cache into a table..
You can create a view then store the contents of that view into a table or query your view directly.
Create this in the master database
CREATE VIEW [dbo].[MYCache]
as
SELECT sp.*
FROM sys.dm_exec_cached_plans as cp
CROSS APPLY SqlAndPlan(cp.plan_handle) as sp
You can select the above data into a table for querying.. The reason I suggest ths is the cache querying can be intensive depending on the number of objects in your cache.
The below query gives you Compile Time, Estimated Rows, and Compile Memory..
You can use this information to determine your worst performing stored procedures.
SELECT SUBSTRING(TEXT, CHARINDEX(‘CREATE PROCEDURE’, text)+16,50) as ProcName,
text,
query_plan.value(‘
declare namespace AWMI=”http://schemas.microsoft.com/sqlserver/2004/07/showplan”;
sum(//AWMI:RelOp/@EstimateRows)’, ‘float’) as EstRows,
query_plan.value(‘
declare namespace AWMI=”http://schemas.microsoft.com/sqlserver/2004/07/showplan”;
sum(//AWMI:QueryPlan/@CompileMemory)’, ‘float’) as EstMemory,
query_plan.value(‘
declare namespace AWMI=”http://schemas.microsoft.com/sqlserver/2004/07/showplan”;
sum(//AWMI:QueryPlan/@CompileTime)’, ‘float’) as CompileTime
FROM PlayDB..Mycache
