Unused Indexes

 1
 2SELECT
 3     db_name() as DatabaseName
 4    ,sys.schemas.NAME AS ShemaName
 5    ,sys.tables.NAME AS TableName
 6    ,sys.indexes.NAME AS IndexName
 7    ,8 * SUM(sys.allocation_units.used_pages) AS IndexSize
 8    ,sys.dm_db_index_usage_stats.user_updates AS 'Total Writes'
 9    ,sys.dm_db_index_usage_stats.user_seeks + sys.dm_db_index_usage_stats.user_scans + sys.dm_db_index_usage_stats.user_lookups AS 'Total Reads'
10    ,sys.dm_db_index_usage_stats.user_updates - (sys.dm_db_index_usage_stats.user_seeks + sys.dm_db_index_usage_stats.user_scans + sys.dm_db_index_usage_stats.user_lookups) AS 'Difference'
11    ,'USE '+ db_name()+';' + 'DROP INDEX '+QUOTENAME(sys.schemas.NAME)+ '.' + QUOTENAME(sys.tables.NAME) + '.' +QUOTENAME(sys.indexes.NAME) + ';' + CHAR(13) + CHAR(10) + 'GO'
12FROM sys.partitions
13INNER JOIN sys.indexes ON sys.partitions.index_id = sys.indexes.index_id
14    AND sys.partitions.object_id = sys.indexes.object_id
15INNER JOIN sys.schemas
16INNER JOIN sys.tables ON sys.schemas.schema_id = sys.tables.schema_id ON sys.partitions.object_id = sys.tables.object_id INNER JOIN sys.allocation_units ON sys.partitions.partition_id = sys.allocation_units.container_id INNER JOIN sys.dm_db_index_usage_stats ON sys.indexes.object_id = sys.dm_db_index_usage_stats.object_id
17    AND sys.indexes.index_id = sys.dm_db_index_usage_stats.index_id
18WHERE
19        sys.indexes.is_primary_key = 0   -- non primary key
20    AND sys.indexes.is_unique = 0        -- non unique
21    AND sys.indexes.type in (2, 6, 7)     -- Only Nonclustered -- https://msdn.microsoft.com/en-us/library/ms173760.aspx
22    AND (sys.dm_db_index_usage_stats.user_seeks + sys.dm_db_index_usage_stats.user_scans + sys.dm_db_index_usage_stats.user_lookups) < 100  -- unused !!!
23GROUP BY
24     sys.schemas.NAME
25    ,sys.tables.NAME
26    ,sys.indexes.NAME
27    ,sys.dm_db_index_usage_stats.user_seeks
28    ,sys.dm_db_index_usage_stats.user_scans
29    ,sys.dm_db_index_usage_stats.user_lookups
30    ,sys.dm_db_index_usage_stats.user_updates
31ORDER BY IndexSize DESC;

 1
 2BEGIN TRY
 3drop table #UnusedIndexes; END TRY BEGIN CATCH END CATCH;
 4
 5CREATE TABLE #UnusedIndexes (
 6    [DatabaseName] [nvarchar](128) NULL,
 7    [ShemaName] [sysname] NOT NULL,
 8    [TableName] [sysname] NOT NULL,
 9    [IndexName] [sysname] NULL,
10    [IndexSize] [bigint] NULL,
11    [TotalWrites] [bigint] NOT NULL,
12    [TotalReads] [bigint] NULL,
13    [Difference] [bigint] NULL,
14    [DropScript] [nvarchar](max) NULL
15) ON [PRIMARY]
16
17DECLARE @sql nvarchar(max);
18
19SET @sql = '
20IF ''?'' NOT IN(''MSDB'', ''MASTER'', ''Model'', ''tempdb'')
21BEGIN
22USE [?];
23EXEC (''
24INSERT INTO #UnusedIndexes
25SELECT db_name() as DatabaseName , sys.schemas.NAME AS ShemaName , sys.tables.NAME AS TableName , sys.indexes.NAME AS IndexName , 8 * SUM(sys.allocation_units.used_pages) AS IndexSize , sys.dm_db_index_usage_stats.user_updates AS TotalWrites , sys.dm_db_index_usage_stats.user_seeks + sys.dm_db_index_usage_stats.user_scans + sys.dm_db_index_usage_stats.user_lookups AS TotalReads , sys.dm_db_index_usage_stats.user_updates - (sys.dm_db_index_usage_stats.user_seeks + sys.dm_db_index_usage_stats.user_scans + sys.dm_db_index_usage_stats.user_lookups) AS Difference
26,''''USE [''''+ db_name()+''''];'''' + ''''DROP INDEX ''''+QUOTENAME(sys.schemas.NAME)+ ''''.'''' + QUOTENAME(sys.tables.NAME) + ''''.'''' +QUOTENAME(sys.indexes.NAME) + '''';'''' + CHAR(13) + CHAR(10) + ''''GO'''' as DropScript
27FROM sys.partitions INNER JOIN sys.indexes ON sys.partitions.index_id = sys.indexes.index_id AND sys.partitions.object_id = sys.indexes.object_id INNER JOIN sys.schemas INNER JOIN sys.tables ON sys.schemas.schema_id = sys.tables.schema_id ON sys.partitions.object_id = sys.tables.object_id INNER JOIN sys.allocation_units ON sys.partitions.partition_id = sys.allocation_units.container_id INNER JOIN sys.dm_db_index_usage_stats ON sys.indexes.object_id = sys.dm_db_index_usage_stats.object_id AND sys.indexes.index_id = sys.dm_db_index_usage_stats.index_id
28WHERE sys.indexes.is_primary_key = 0 AND sys.indexes.is_unique = 0 AND sys.indexes.type in (2, 6, 7) AND (sys.dm_db_index_usage_stats.user_seeks + sys.dm_db_index_usage_stats.user_scans + sys.dm_db_index_usage_stats.user_lookups) < 100 GROUP BY sys.schemas.NAME , sys.tables.NAME , sys.indexes.NAME , sys.dm_db_index_usage_stats.user_seeks , sys.dm_db_index_usage_stats.user_scans , sys.dm_db_index_usage_stats.user_lookups , sys.dm_db_index_usage_stats.user_updates ORDER BY IndexSize DESC;
29'')
30END
31'
32EXEC sp_MSforeachdb @sql
33select * from #UnusedIndexes order by IndexSize desc
34drop table #UnusedIndexes;

 1
 2SELECT
 3    t.Name as TableName, I.name as IndexName,
 4    ' CREATE ' +
 5    CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END  + I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' +
 6    I.name  + ' ON '  +
 7    Schema_name(T.Schema_id)+'.'+T.name + ' ( ' + KeyColumns + ' )  ' +
 8    ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') +
 9    ISNULL(' WHERE  '+I.Filter_definition,'') + ' WITH ( ' +
10    CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ','  +
11    'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ','  +
12    -- default value
13    'SORT_IN_TEMPDB = OFF '  + ','  +
14    CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ','  +
15    CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ','  +
16    -- default value
17    ' DROP_EXISTING = ON '  + ','  +
18    -- default value
19    ' ONLINE = OFF '  + ','  +
20   CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ','  +
21   CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END  + ' ) ON [' +
22   DS.name + ' ] '  [CreateIndexScript]
23FROM sys.indexes I
24 JOIN sys.tables T ON T.Object_id = I.Object_id
25 JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid
26 JOIN (SELECT * FROM (
27    SELECT IC2.object_id , IC2.index_id, STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END
28    FROM sys.index_columns IC1
29    JOIN Sys.columns C
30       ON C.object_id = IC1.object_id
31       AND C.column_id = IC1.column_id
32       AND IC1.is_included_column = 0
33    WHERE IC1.object_id = IC2.object_id
34       AND IC1.index_id = IC2.index_id
35    GROUP BY IC1.object_id,C.name,index_id
36    ORDER BY MAX(IC1.key_ordinal)
37       FOR XML PATH('')), 1, 2, '') KeyColumns
38    FROM sys.index_columns IC2
39    --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
40    GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4
41  ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id
42 JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id
43 JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id
44 JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id
45 LEFT JOIN (SELECT * FROM (
46    SELECT IC2.object_id , IC2.index_id ,
47        STUFF((SELECT ' , ' + C.name
48    FROM sys.index_columns IC1
49    JOIN Sys.columns C
50       ON C.object_id = IC1.object_id
51       AND C.column_id = IC1.column_id
52       AND IC1.is_included_column = 1
53    WHERE IC1.object_id = IC2.object_id
54       AND IC1.index_id = IC2.index_id
55    GROUP BY IC1.object_id,C.name,index_id
56       FOR XML PATH('')), 1, 2, '') IncludedColumns
57   FROM sys.index_columns IC2
58   --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
59   GROUP BY IC2.object_id ,IC2.index_id) tmp1
60   WHERE IncludedColumns IS NOT NULL ) tmp2
61ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id
62WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0
63--AND I.Object_id = object_id('Person.Address') --Comment for all tables
64--AND I.name = 'IX_Address_PostalCode' --comment for all indexes