use admin GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetProceduresFoundInCache]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[GetProceduresFoundInCache] GO CREATE PROCEDURE [dbo].[GetProceduresFoundInCache] AS /******************************************************************************************************* * admin.dbo.GetProceduresFoundInCache * Creator: Bill Wunder * * Description: periodically sample syscacheobjects to track which procedures are being used * Notes: Set up a SQL Agent job that runs once a minute to call this job. * Table size is limited to one row per distinct stored procedure called on server * * Usage: EXECUTE GetProceduresFoundInCache * * * Modifications: * Developer Name Date Brief Description * ------------------ -------- ------------------------------------------------------------ * ********************************************************************************************************/ --------------------------------------------- -- declare variables --------------------------------------------- declare @findstr varchar(20) --------------------------------------------- -- create temp tables --------------------------------------------- declare @cacheobjects table (id int identity(1,1) primary key, sql varchar(100), dbname varchar(30)) declare @procedures table (id int identity(1,1) primary key, procname varchar(100), dbname varchar(30)) -- create the history table if it doesn't already exist if object_id('admin.dbo.ProceduresFoundInCache','U') is null CREATE TABLE admin.dbo.ProceduresFoundInCache (ProcedureName varchar(100) not null, DatabaseName varchar(30), LastFoundDt datetime null, -- if null later you'll know the proc was only used that one time RecCreatedDt datetime CONSTRAINT dft_ProceduresFoundInCache__LastFoundDt default getdate(), FoundCount int not null CONSTRAINT dft_ProceduresFoundInCache__FoundCount default 1, CONSTRAINT pk_ProceduresFoundInCache__ProcedureName__DatabaseName primary key (ProcedureName, DatabaseName)) if object_id('admin.dbo.CacheSeed','P') is null exec sp_executesql N'create proc dbo.CacheSeed as select @@spid' --------------------------------------------- -- set session --------------------------------------------- SET NOCOUNT ON --------------------------------------------- -- body of stored procedure --------------------------------------------- if not exists (select 1 from master.dbo.syscacheobjects with(nolock) where objid = object_id('dbo.CacheSeed') and dbid = db_id('admin')) begin -- first get a snapshot of interesting syscacheobjects to minimize system impact while updating history table -- get rid of tabs, line feeds and carriage returns found in the sql column of syscacheobjects -- make a bit of an attempt to eliminate two white adjunnct spaces insert @cacheobjects select replace(replace(replace(replace(substring(ltrim(rtrim(sql)),1,100), char(9), char(32)), char(10) , char(32)), char(13), char(32)), char(32) + char(32), char(32)), db_name(dbid) from master.dbo.syscacheobjects with(nolock) where cacheobjtype = 'Compiled Plan' -- remove database and owner qualification syntax (help avoid duplicates and misqueries later) -- may as well standardize the exec now too for parsing in next steps update @cacheobjects set sql = replace(replace(replace(replace(sql,'..','.'),dbname + '.',''),'dbo.',''),'execute' + char(32),'exec' + char(32)) -- filter as many cache items as possible that are not stored procedures set @findstr = 'exec' + space(1) insert @procedures (procname, dbname) select distinct case when charindex(@findstr,sql) > 0 then cast(substring(sql, charindex(@findstr, sql) + datalength(@findstr), charindex(char(32), substring(sql, charindex(@findstr, sql) + datalength(@findstr), datalength(sql)))) as varchar(100)) when charindex(char(32), sql) = 0 then sql else null end, dbname from @cacheobjects where charindex('sp_MS',sql) = 0 and charindex('sp_help',sql) = 0 and charindex('dbo.sys',sql) = 0 and charindex('information_schema',sql) = 0 and charindex ('select ', sql) = 0 and charindex ('insert ', sql) = 0 and charindex ('update ', sql) = 0 and charindex ('delete ', sql) = 0 and charindex ('CREATE ', sql) = 0 and charindex ('ALTER ', sql) = 0 and charindex ('GRANT ', sql) = 0 and charindex ('REVOKE ', sql) = 0 and charindex ('DENY ', sql) = 0 -- at this point the procedure will stil be prefixed with "." in the case where the connectin db -- is not the db where the procedure lives so move the procedures db out to the dbname column update @procedures set dbname = case when charindex('.', procname) > 0 then substring(procname,1,charindex('.', procname) - 1) else dbname end, procname = case when charindex('.', procname) > 0 then substring(procname,charindex('.', procname) + 1, datalength(procname)) else procname end update p set FoundCount = FoundCount + 1, LastFoundDt = getdate() from admin.dbo.ProceduresFoundInCache p inner join @procedures p1 on p.ProcedureName = p1.procname and p.DatabaseName = p1.dbname insert admin.dbo.ProceduresFoundInCache (ProcedureName, DatabaseName) select distinct procname, dbname from @procedures p where p.procname is not null and rtrim(ltrim(p.procname)) <> '' and not exists (select 1 from admin.dbo.ProceduresFoundInCache where ProcedureName = p.procname and DatabaseName = p.dbName) and p.dbname not in ('master','model','msdb','tempdb','admin', 'pubs', 'Northwind') -- exec the seed proc to get it back in cache exec admin.dbo.CacheSeed end GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO