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'

Create a Comma Separated Column List for a SQL Server Table

declare @t as varchar(100)

declare @all as varchar(4000)

declare curs cursor for

select rtrim(c.name) + ', '

from sysobjects o

inner join syscolumns c on o.id = c.id

where o.xtype = 'u'

and o.name = 'table_name'

order by colid

open curs

fetch next from curs into @t

set @all = ''

while @@fetch_status = 0

begin

       set @all = @all + @t

       fetch next from curs into @t

end

close curs

deallocate curs

print @all

Average Run Time of a SQL Server Job

To get the average run time of a SQL Server job the below code will get that information from sysjobhistory.

SELECT

       name,

       start_time,

       dateadd(s, avg(run_duration_seconds), start_time) as average_end_time,

       dateadd(s, max(run_duration_seconds), start_time) as max_end_time,

       dateadd(s, min(run_duration_seconds), start_time) as min_end_time

FROM

       (SELECT

              j.name,

              cast('01/01/1900 ' +

              substring(right(('000000') + cast(jh.run_time as varchar), 6), 1, 2) + ':' +

              substring(right(('000000') + cast(jh.run_time as varchar), 6), 3, 2) + ':' +

              substring(right(('000000') + cast(jh.run_time as varchar), 6), 5, 2) as smalldatetime) as start_time,

              substring(right(('000000') + cast(jh.run_duration as varchar), 6), 5, 2) +

              (substring(right(('000000') + cast(jh.run_duration as varchar), 6), 3, 2) * 60) +

              (substring(right(('000000') + cast(jh.run_duration as varchar), 6), 1, 2) * 60 * 60) as run_duration_seconds

       FROM sysjobs j

              INNER JOIN sysjobhistory jh on j.job_id = jh.job_id

       WHERE

              datepart(weekday, cast(substring(cast(jh.run_date as varchar), 5, 2) + '-' + substring(cast(jh.run_date as varchar), 7, 2) + '-' + substring(cast(jh.run_date as varchar), 1, 4) as smalldatetime)) in (2,3,4,5,6)

WHERE run_duration_seconds > 120

GROUP BY

       name,

       start_time

ORDER BY

       name