Persist Stored Procedure Execution Counts

/*

SELECT * FROM sys.procedures AS p left JOIN sys.dm_exec_procedure_stats AS s ON s.[object_id] = p.[object_id]

drop table stored_procedure_monitor

use mydatabase

CREATE TABLE dbo.stored_procedure_monitor(

    stored_procedure_monitor_id int identity(1,1),

    db_id         int             NOT NULL,

    obj_id        int             NOT NULL,

    schema_id     int             NOT NULL,

    db_nme        varchar(256)    NOT NULL,

    obj_nme       varchar(256)    NOT NULL,

       exp_dte       smalldatetime   NULL,

    create_dte    datetime        DEFAULT (getdate()) NOT NULL,

    create_uid    char(10)        DEFAULT (substring(suser_sname(),charindex('\',suser_sname())+(1),(10))) NOT NULL,

    update_dte    datetime        DEFAULT (getdate()) NOT NULL,

    update_uid    char(10)        DEFAULT (substring(suser_sname(),charindex('\',suser_sname())+(1),(10))) NOT NULL,

    CONSTRAINT PK__stored_procedure_monitor PRIMARY KEY CLUSTERED (stored_procedure_monitor_id)

)

drop table stored_procedure_monitor_executions

CREATE TABLE dbo.stored_procedure_monitor_executions(

    stored_procedure_monitor_id int NOT NULL,

    cached_time   datetime        NOT NULL,

    sql_handle varbinary(64) NOT NULL,

    plan_handle varbinary(64) NOT NULL,

    last_execution_time datetime  NOT NULL,

    execution_count bigint NOT NULL,

    total_worker_time bigint NOT NULL,

       min_worker_time bigint NOT NULL,

       max_worker_time bigint NOT NULL,

       total_physical_reads bigint NOT NULL,

       min_physical_reads bigint NOT NULL,

       max_physical_reads bigint NOT NULL,

       total_logical_reads bigint NOT NULL,

       min_logical_reads bigint NOT NULL,

       max_logical_reads bigint NOT NULL,

       total_logical_writes bigint NOT NULL,

       min_logical_writes bigint NOT NULL,

       max_logical_writes bigint NOT NULL,     

       total_elapsed_time bigint NOT NULL,     

       min_elapsed_time bigint NOT NULL,

       max_elapsed_time bigint NOT NULL,

       exp_dte smalldatetime null,

    create_dte    datetime        DEFAULT (getdate()) NOT NULL,

    create_uid    char(10)        DEFAULT (substring(suser_sname(),charindex('\',suser_sname())+(1),(10))) NOT NULL,

    update_dte    datetime        DEFAULT (getdate()) NOT NULL,

    update_uid    char(10)        DEFAULT (substring(suser_sname(),charindex('\',suser_sname())+(1),(10))) NOT NULL,

    CONSTRAINT PK__stored_procedure_monitor_executions PRIMARY KEY CLUSTERED (stored_procedure_monitor_id, cached_time, sql_handle, plan_handle)

)

*/

use mydatabase

go

create table #procedure (

db_id int NOT NULL,

db_nme varchar(256) NOT NULL,

obj_id int NOT NULL,

obj_nme varchar(256) NOT NULL,

schema_id int NOT NULL)

declare @db_id int

declare @db_nme as varchar(256)

declare @sql nvarchar(4000)

declare curs cursor for

select distinct database_id, name

from master.sys.databases

open curs

fetch next from curs into @db_id, @db_nme

while @@FETCH_STATUS = 0

begin

       set @sql = 'select ' + cast(@db_id as varchar) + ' as db_id, ''' + @db_nme + ''' as db_nme, object_id as obj_id, name as obj_nme, schema_id from ' + @db_nme + '.sys.procedures'

      

       insert into #procedure (db_id, db_nme, obj_id, obj_nme, schema_id)

       exec sp_executesql @sql

       fetch next from curs into @db_id, @db_nme

end

close curs

deallocate curs

--insert any new stored procedures that were identified

insert into mydatabase.dbo.stored_procedure_monitor(db_id, db_nme, obj_id, obj_nme, schema_id)

select db_id, db_nme, obj_id, obj_nme, schema_id

from #procedure p

where not exists

       (select 1 from mydatabase.dbo.stored_procedure_monitor p2

       where p.db_id = p2.db_id

       and p.obj_id = p2.obj_id

       and p.schema_id = p2.schema_id)

--expire any stored procedures which no longer exist

update mydatabase.dbo.stored_procedure_monitor

set exp_dte = getdate()

from mydatabase.dbo.stored_procedure_monitor p2

where not exists (select 1 from #procedure p

       where p.db_id = p2.db_id

       and p.obj_id = p2.obj_id

       and p.schema_id = p2.schema_id)

and exp_dte is null

--unexpire anything that exists again

update mydatabase.dbo.stored_procedure_monitor

set exp_dte = null

from mydatabase.dbo.stored_procedure_monitor p2

where exists (select 1 from #procedure p

       where p.db_id = p2.db_id

       and p.obj_id = p2.obj_id

       and p.schema_id = p2.schema_id

       and exp_dte is not null)

--------------------------------------------------------------------------------------

---------------begin stored proc executions

CREATE TABLE #executions(

       db_id int not null,

       obj_id int not null,

    cached_time   datetime        NOT NULL,

    sql_handle varbinary(64) NOT NULL,

    plan_handle varbinary(64) NOT NULL,

    last_execution_time datetime  NOT NULL,

    execution_count bigint NOT NULL,

    total_worker_time bigint NOT NULL,

       min_worker_time bigint NOT NULL,

       max_worker_time bigint NOT NULL,

       total_physical_reads bigint NOT NULL,

       min_physical_reads bigint NOT NULL,

       max_physical_reads bigint NOT NULL,

       total_logical_reads bigint NOT NULL,

       min_logical_reads bigint NOT NULL,

       max_logical_reads bigint NOT NULL,

       total_logical_writes bigint NOT NULL,

       min_logical_writes bigint NOT NULL,

       max_logical_writes bigint NOT NULL,     

       total_elapsed_time bigint NOT NULL,     

       min_elapsed_time bigint NOT NULL,

       max_elapsed_time bigint NOT NULL)

             

declare curs cursor for

select distinct db_id, db_nme

from mydatabase.dbo.stored_procedure_monitor

where exp_dte is null

open curs

fetch next from curs into @db_id, @db_nme

while @@FETCH_STATUS = 0

begin

       set @sql = 'SELECT ' + CAST(@db_id as varchar) + ' as db_id, s.object_id, s.cached_time, s.sql_handle, s.plan_handle, s.last_execution_time, s.execution_count, s.total_worker_time,

       s.min_worker_time, s.max_worker_time, s.total_physical_reads, s.min_physical_reads, s.max_physical_reads, s.total_logical_reads, s.min_logical_reads,

       s.max_logical_reads, s.total_logical_writes, s.min_logical_writes, s.max_logical_writes, s.total_elapsed_time, s.min_elapsed_time, s.max_elapsed_time

       FROM ' + @db_nme + '.sys.procedures AS p

       INNER JOIN sys.dm_exec_procedure_stats AS s ON s.object_id = p.object_id and s.database_id = ' + CAST(@db_id as varchar)

       --select * from sys.dm_exec_procedure_stats

       --insert into #procedure (db_id, db_nme, obj_id, obj_nme, schema_id)

      

       insert into #executions (db_id, obj_id, cached_time, sql_handle, plan_handle, last_execution_time, execution_count, total_worker_time,

       min_worker_time, max_worker_time, total_physical_reads, min_physical_reads, max_physical_reads, total_logical_reads, min_logical_reads,

       max_logical_reads, total_logical_writes, min_logical_writes, max_logical_writes, total_elapsed_time, min_elapsed_time, max_elapsed_time)

       exec sp_executesql @sql

       fetch next from curs into @db_id, @db_nme

end

close curs

deallocate curs

--insert new stored procedure executions

insert into mydatabase.dbo.stored_procedure_monitor_executions (stored_procedure_monitor_id, cached_time, sql_handle, plan_handle, last_execution_time, execution_count, total_worker_time,

       min_worker_time, max_worker_time, total_physical_reads, min_physical_reads, max_physical_reads, total_logical_reads, min_logical_reads,

       max_logical_reads, total_logical_writes, min_logical_writes, max_logical_writes, total_elapsed_time, min_elapsed_time, max_elapsed_time)

select stored_procedure_monitor_id, cached_time, sql_handle, plan_handle, last_execution_time, execution_count, total_worker_time,

       min_worker_time, max_worker_time, total_physical_reads, min_physical_reads, max_physical_reads, total_logical_reads, min_logical_reads,

       max_logical_reads, total_logical_writes, min_logical_writes, max_logical_writes, total_elapsed_time, min_elapsed_time, max_elapsed_time

from #executions e

inner join dbo.stored_procedure_monitor spm on e.obj_id = spm.obj_id and e.db_id = spm.db_id

where not exists (select 1

       from mydatabase.dbo.stored_procedure_monitor_executions spme

       where spme.stored_procedure_monitor_id = spm.stored_procedure_monitor_id

       and spme.cached_time = e.cached_time

       and spme.sql_handle = e.sql_handle

       and spme.plan_handle = e.plan_handle)

      

--update stored procedure executions stats

update mydatabase.dbo.stored_procedure_monitor_executions

set last_execution_time = e.last_execution_time,

execution_count = e.execution_count,

total_worker_time = e.total_worker_time,

min_worker_time = e.min_worker_time,

max_worker_time = e.max_worker_time,

total_physical_reads = e.total_physical_reads,

min_physical_reads = e.min_physical_reads,

max_physical_reads = e.max_physical_reads,

total_logical_reads = e.total_logical_reads,

min_logical_reads = e.min_logical_reads,

max_logical_reads = e.max_logical_reads,

total_logical_writes = e.total_logical_writes,

min_logical_writes = e.min_logical_writes,

max_logical_writes = e.max_logical_writes,

total_elapsed_time = e.total_elapsed_time,

min_elapsed_time = e.min_elapsed_time,

max_elapsed_time = e.max_elapsed_time,

update_dte = GETDATE()

from #executions e

inner join dbo.stored_procedure_monitor spm on e.obj_id = spm.obj_id and e.db_id = spm.db_id

where spm.stored_procedure_monitor_id = mydatabase.dbo.stored_procedure_monitor_executions.stored_procedure_monitor_id and

mydatabase.dbo.stored_procedure_monitor_executions.cached_time = e.cached_time and

mydatabase.dbo.stored_procedure_monitor_executions.sql_handle = e.sql_handle and

mydatabase.dbo.stored_procedure_monitor_executions.plan_handle = e.plan_handle and

mydatabase.dbo.stored_procedure_monitor_executions.last_execution_time <> e.last_execution_time

--record the time we noted the plan was out of cache

update mydatabase.dbo.stored_procedure_monitor_executions

set exp_dte = GETDATE(),

update_dte = GETDATE()

where not exists (select 1

       from #executions e

       inner join dbo.stored_procedure_monitor spm on e.obj_id = spm.obj_id and e.db_id = spm.db_id

       where spm.stored_procedure_monitor_id = mydatabase.dbo.stored_procedure_monitor_executions.stored_procedure_monitor_id and

       mydatabase.dbo.stored_procedure_monitor_executions.cached_time = e.cached_time and

       mydatabase.dbo.stored_procedure_monitor_executions.sql_handle = e.sql_handle and

       mydatabase.dbo.stored_procedure_monitor_executions.plan_handle = e.plan_handle and

       mydatabase.dbo.stored_procedure_monitor_executions.last_execution_time = e.last_execution_time)

and exp_dte is null

      

/*

select spm.db_nme,

spm.obj_nme,

SUM(execution_count) as execution_count,

SUM(total_worker_time)/SUM(execution_count) as avg_cpu_time,

MIN(min_worker_time) as min_worker_time,

MAX(max_worker_time) as max_worker_time,

sum(total_elapsed_time) total_elapsed_time,

sum(total_elapsed_time)/SUM(execution_count) as avg_execution_time,

min(min_elapsed_time) as min_elapsed_time,

max(max_elapsed_time) as max_elapsed_time

from mydatabase.dbo.stored_procedure_monitor spm

left outer join mydatabase.dbo.stored_procedure_monitor_executions spme on spm.stored_procedure_monitor_id = spme.stored_procedure_monitor_id

group by spm.db_nme, spm.obj_nme

order by sum(total_elapsed_time)/SUM(execution_count) desc

*/