-- Stored Procedure: dbo.GetProceduresFoundInCache
-- Bill Wunder

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
* Date:   3-14-2003
*
* Project:      
* Project Mgr: 
* Dev Contact: 
*
* Description: periodically sample syscacheobjects to track which procedures are being used
* Notes:         figuring out the sampling interval may prove to be an art
*                Once you thing it is not getting used, add an insert to a tracking table to any proc      
*
* Usage:
      EXECUTE GetProceduresFoundInCache
*
*
* Modifications:   
*         Developer Name     Date     Brief Description
*         ------------------ -------- ------------------------------------------------------------
*         bw                 3-22-03  add times found column to provide better indication of activity
--run this before creating procedure in db where table already exists without new column (3-22-3002)
if Columnproperty(object_id('admin.dbo.ProceduresFoundInCache'),'FoundCount','AllowsNull') is null
 alter table admin.dbo.ProceduresFoundInCache
 add FoundCount int not null
        CONSTRAINT dft_ProceduresFoundInCache__FoundCount default 1
  bw       4-24-03  add a check for and exec of a seed proc to avoid having to run the 
                                      entire process each time called (reduce cpu load)  
********************************************************************************************************/

---------------------------------------------
-- 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 "<dbname>." 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 ON
GO
SET ANSI_NULLS ON 
GO