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

Update use SIDS following a SQL Server Restore

declare @usrname varchar(100), @command varchar(100)

declare Crs insensitive cursor for

select name as UserName from sysusers

where issqluser = 1 and (sid is not null and sid <> 0x0)

and suser_sname(sid) is null

order by name

for read only

open Crs

fetch next from Crs into @usrname

while @@fetch_status=0

begin

select @command= 'exec sp_change_users_login @action ='+''''+ 'auto_fix' + '''' + ',@UserNamePattern='+''''+ @usrname+''''

print @command

exec(@command)

fetch next from Crs into @usrname

end

close Crs

deallocate Crs

Estimate Size of a SQL Server Table

If you're curious how large a SQL Server table will get use the below script to estimate the size after updating the table name, estimated number of records, and the freespace in your database.

set nocount on

declare @freespace as numeric(4, 2)

declare @page_space_available as bigint

declare @estimated_records_after_one_year as bigint

declare @table_name as sysname

declare @max_record_size as bigint

declare @records_per_page as bigint

declare @total_datapages as bigint

declare @RID_size as bigint

/*******************************************************************/

/********update these variables to get estimated space usage********/

/*******************************************************************/

/*****/set @freespace = 0.1                                   /*****/

/*****/set @estimated_records_after_one_year= 3000000         /*****/

/*****/set @table_name = 'TABLE NAME'                         /*****/

/*******************************************************************/

/*******************************************************************/

/*******************************************************************/

/*A datapage is 8192 bytes, but up to 132 bytes of that can be the header leaving just 8060 bytes for data*/

set @page_space_available = 8060 * (1.0 - @freespace)

if not exists (select 1 from sys.objects where name = @table_name and type = 'u')

       raiserror ('Table does not exist.', 16, 1)

else

begin

       if exists (select 1 from sys.indexes si inner join sys.objects so on so.object_id = si.object_id where so.type = 'u' and so.name = @table_name and si.index_id = 0)

       begin

              raiserror ('This table is a HEAP and space estimate could be off due to forwarded records, you should consider adding a clustered index.', 16, 1)

       end

       if exists (select 1 from sys.columns c inner join sys.objects o on o.object_id = c.object_id and o.name = @table_name and o.type = 'u' and c.max_length = -1)

       begin

              raiserror ('This table contains datatypes which do not live on the datapage - such as varchar(max) or xml - this estimate is not entirely valid since those datatype could reach an infinite size.', 16, 1)

       end

       select @max_record_size = sum(case c.max_length when -1 then 10000 else c.max_length end)

       from sys.columns c

       inner join sys.objects o on o.object_id = c.object_id

       and o.name = @table_name

       and o.type = 'u'

       if @max_record_size > @page_space_available

       begin

              select 'This table contains a datatype which may not live on a datapage so these results should be taken with a grain of salt' as message

              set @max_record_size = @page_space_available

       end

       set @records_per_page = @page_space_available / @max_record_size

       set @total_datapages = @estimated_records_after_one_year / @records_per_page

       select (@total_datapages * 8192) / (1048576) as estimated_table_space_used_in_mb

       --If there is a clustered index then grab the size of it in bytes

       if exists (select 1 from sys.indexes si inner join sys.objects so on so.object_id = si.object_id where so.type = 'u' and so.name = @table_name and si.index_id = 1)

       begin

              select @RID_size = sum(sc.max_length)

              from sys.indexes si

              inner join sys.objects so on so.object_id = si.object_id

              inner join sys.sysindexkeys sik on sik.id = so.object_id and sik.indid = si.index_id

              inner join sys.columns sc on sc.object_id = so.object_id and sc.column_id = sik.colid

              where so.type = 'u'

              and so.name = @table_name

              and si.index_id = 1

       end

       else

       begin

              select @RID_size = 16 /*setting this to 16 bytes if the table is a HEAP - it's not necessarily 16 bytes but I think

                                           thats a good estimage for the file identifier, page number, and row number.*/

       end

       select sum(max_length) as estimated_index_space_used_in_mb

       from (

              select ((@estimated_records_after_one_year / (@page_space_available / (sum(sc.max_length) + @RID_size))) * 8192) / (1048576) as max_length -- include a constant for nonclustered key value and a row locator

              from sys.indexes si

              inner join sys.objects so on so.object_id = si.object_id

              inner join sys.sysindexkeys sik on sik.id = so.object_id and sik.indid = si.index_id

              inner join sys.columns sc on sc.object_id = so.object_id and sc.column_id = sik.colid

              where so.type = 'u'

              and so.name = @table_name

              and si.index_id <> 1 --Do not include the clustered index

              and si.index_id <> 0 --Do not include heaps

              group by si.name) t

end

Script to Create a Script that Will Drop and Add all Foreign Keys in a SQL Server Database

   select 'ALTER TABLE ' + cast(c.name as  varchar(255))  + '

        DROP CONSTRAINT ' + cast(f.name  as varchar(255))

    from sysobjects f

    inner join sysobjects c on  f.parent_obj = c.id

    inner join sysreferences r on f.id =  r.constid

    inner join sysobjects p on r.rkeyid = p.id

    inner  join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid

    inner  join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid

    left join  syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid

    left join  syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid

    where f.type =  'F'

select 'ALTER TABLE ' + cast(c.name as  varchar(255)) + ' ADD CONSTRAINT ' + cast(f.name  as varchar(255)) + '

            FOREIGN KEY (' + cast(fc.name as varchar(255)) + ')

            REFERENCES ' + cast(p.name as varchar(255)) + ' (' + cast(rc.name as varchar(255)) + ')'

    from sysobjects f

    inner join sysobjects c on  f.parent_obj = c.id

    inner join sysreferences r on f.id =  r.constid

    inner join sysobjects p on r.rkeyid = p.id

    inner  join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid

    inner  join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid

    left join  syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid

    left join  syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid

    where f.type =  'F'