Adsense

Tuesday, May 5, 2015

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
*/



Identify unused indexes and tables

The below query will persist data from sys.dm_db_index_usage_stats so that a history of access to indexes can be maintained following SQL Server restarts.  This script should be scheduled to run periodically (nightly is ideal).

use mydatabase
go

if not exists (select 1 from sysobjects where name = 'index_usage_stats')
       CREATE TABLE dbo.index_usage_stats(
              index_usage_stats_id int identity (1,1) not null,
              database_name sysname NOT NULL,
              database_id smallint NOT NULL,
              object_name sysname NOT NULL,
              object_id int NOT NULL,
              index_name sysname NULL,
              index_id int NOT NULL,
              user_seeks bigint NOT NULL,
              user_scans bigint NOT NULL,
              user_lookups bigint NOT NULL,
              user_updates bigint NOT NULL,
              last_user_seek datetime NULL,
              last_user_scan datetime NULL,
              last_user_lookup datetime NULL,
              last_user_update datetime NULL,
              system_seeks bigint NOT NULL,
              system_scans bigint NOT NULL,
              system_lookups bigint NOT NULL,
              system_updates bigint NOT NULL,
              last_system_seek datetime NULL,
              last_system_scan datetime NULL,
              last_system_lookup datetime NULL,
              last_system_update datetime NULL,
              dpages bigint null,
              create_dte smalldatetime not null default getdate(),
              instance_restart_id int
       )

if not exists (select 1 from sysobjects where name = 'instance_restart')
       create table dbo.instance_restart (
              instance_restart_id int identity(1,1) not null,
              instance_restart_dte smalldatetime not null)

declare @instance_restart_id as int
declare @instance_restart_dte as smalldatetime

select @instance_restart_dte = create_date FROM master.sys.databases WHERE database_id = 2 --sql was last restarted at the time tempdb was created

IF NOT EXISTS (select 1 from mydatabase.dbo.instance_restart where instance_restart_dte = @instance_restart_dte)
       insert into mydatabase.dbo.instance_restart (instance_restart_dte) select (@instance_restart_dte)

select @instance_restart_id = max(instance_restart_id) from mydatabase.dbo.instance_restart

declare @dbname as sysname
declare @dbid int
declare @sql as nvarchar(4000)

declare curs cursor for
select name, database_id
from master.sys.databases
where database_id > 4 --do not monitor system databases, we don't necessarily have access

open curs

fetch next from curs into @dbname, @dbid

while @@fetch_status = 0
begin

       print @dbname

       set @sql = '
       insert into mydatabase.dbo.index_usage_stats (database_name, database_id, object_name, object_id, index_name, index_id, user_seeks, user_scans, user_lookups, user_updates,
              last_user_seek, last_user_scan, last_user_lookup, last_user_update, system_seeks, system_scans, system_lookups,system_updates, last_system_seek, last_system_scan,
              last_system_lookup, last_system_update, dpages, instance_restart_id)
       select ''' + @dbname + ''' as database_name, ' + cast(@dbid as varchar) + ' as database_id, o.name as object_name, o.object_id, i.name as index_name,
       i.index_id, isnull(s.user_seeks, 0), isnull(s.user_scans, 0), isnull(s.user_lookups, 0), isnull(s.user_updates, 0), s.last_user_seek, s.last_user_scan,
       s.last_user_lookup, s.last_user_update, isnull(s.system_seeks, 0), isnull(s.system_scans, 0),
       isnull(s.system_lookups, 0), isnull(s.system_updates, 0), s.last_system_seek, s.last_system_scan, s.last_system_lookup,
       s.last_system_update, i2.dpages, ' + cast(@instance_restart_id as varchar) + '
       from ' + @dbname + '.sys.indexes i
       inner join ' + @dbname + '.sys.objects o on o.object_id = i.object_id
       inner join ' + @dbname + '.dbo.sysindexes i2 on i.object_id = i2.id and i.index_id = i2.indid
       left outer join sys.dm_db_index_usage_stats s on i.object_id = s.object_id and i.index_id = s.index_id and s.database_id = ' + cast(@dbid as varchar)

       print @sql

       exec sp_executesql @sql

       fetch next from curs into @dbname, @dbid
end

close curs

deallocate curs