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