Unused indexes
Rédigé par Sozezzo - - Aucun commentaireUnused indexes should be deleted, but you would not drop all the unused indexes without deep analyse.
This a script delete all unused indexes.
- Nonclustered indexes
- Non primary keys
- Non unique
- Non unused (#Total reads < 100)
Finding Unused Indexes -- Only Nonclustered
SELECT 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 'Total Writes' ,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' ,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' ,'USE '+ db_name()+';' + 'DROP INDEX '+QUOTENAME(sys.schemas.NAME)+ '.' + QUOTENAME(sys.tables.NAME) + '.' +QUOTENAME(sys.indexes.NAME) + ';' + CHAR(13) + CHAR(10) + 'GO' FROM 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 WHERE sys.indexes.is_primary_key = 0 -- non primary key AND sys.indexes.is_unique = 0 -- non unique AND sys.indexes.type in (2, 6, 7) -- Only Nonclustered -- https://msdn.microsoft.com/en-us/library/ms173760.aspx 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 !!! 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;
Check all Unused indexes on all database
BEGIN TRY drop table #UnusedIndexes; END TRY BEGIN CATCH END CATCH; CREATE TABLE #UnusedIndexes ( [DatabaseName] [nvarchar](128) NULL, [ShemaName] [sysname] NOT NULL, [TableName] [sysname] NOT NULL, [IndexName] [sysname] NULL, [IndexSize] [bigint] NULL, [TotalWrites] [bigint] NOT NULL, [TotalReads] [bigint] NULL, [Difference] [bigint] NULL, [DropScript] [nvarchar](max) NULL ) ON [PRIMARY] DECLARE @sql nvarchar(max); SET @sql = ' IF ''?'' NOT IN(''MSDB'', ''MASTER'', ''Model'', ''tempdb'') BEGIN USE [?]; EXEC ('' INSERT INTO #UnusedIndexes SELECT 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 ,''''USE [''''+ db_name()+''''];'''' + ''''DROP INDEX ''''+QUOTENAME(sys.schemas.NAME)+ ''''.'''' + QUOTENAME(sys.tables.NAME) + ''''.'''' +QUOTENAME(sys.indexes.NAME) + '''';'''' + CHAR(13) + CHAR(10) + ''''GO'''' as DropScript FROM 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 WHERE 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; '') END ' EXEC sp_MSforeachdb @sql select * from #UnusedIndexes order by IndexSize desc drop table #UnusedIndexes;
Script to create all indexes.
SELECT t.Name as TableName, I.name as IndexName, ' CREATE ' + CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END + I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' + I.name + ' ON ' + Schema_name(T.Schema_id)+'.'+T.name + ' ( ' + KeyColumns + ' ) ' + ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') + ISNULL(' WHERE '+I.Filter_definition,'') + ' WITH ( ' + CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ',' + 'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ',' + -- default value 'SORT_IN_TEMPDB = OFF ' + ',' + CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ',' + CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ',' + -- default value ' DROP_EXISTING = ON ' + ',' + -- default value ' ONLINE = OFF ' + ',' + CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ',' + CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END + ' ) ON [' + DS.name + ' ] ' [CreateIndexScript] FROM sys.indexes I JOIN sys.tables T ON T.Object_id = I.Object_id JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid JOIN (SELECT * FROM ( 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 FROM sys.index_columns IC1 JOIN Sys.columns C ON C.object_id = IC1.object_id AND C.column_id = IC1.column_id AND IC1.is_included_column = 0 WHERE IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id GROUP BY IC1.object_id,C.name,index_id ORDER BY MAX(IC1.key_ordinal) FOR XML PATH('')), 1, 2, '') KeyColumns FROM sys.index_columns IC2 --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4 ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id LEFT JOIN (SELECT * FROM ( SELECT IC2.object_id , IC2.index_id , STUFF((SELECT ' , ' + C.name FROM sys.index_columns IC1 JOIN Sys.columns C ON C.object_id = IC1.object_id AND C.column_id = IC1.column_id AND IC1.is_included_column = 1 WHERE IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id GROUP BY IC1.object_id,C.name,index_id FOR XML PATH('')), 1, 2, '') IncludedColumns FROM sys.index_columns IC2 --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables GROUP BY IC2.object_id ,IC2.index_id) tmp1 WHERE IncludedColumns IS NOT NULL ) tmp2 ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0 --AND I.Object_id = object_id('Person.Address') --Comment for all tables --AND I.name = 'IX_Address_PostalCode' --comment for all indexes
Source:
http://blog.sqlauthority.com/2010/05/09/sql-server-size-of-index-table-for-each-index-solution-2/
http://www.foliotek.com/devblog/identifying-unused-indexes-in-a-sql-server-database/
http://sqlmag.com/sql-server/tip-finding-unused-indexes
https://www.mssqltips.com/sqlservertip/3441/script-out-all-sql-server-indexes-in-a-database-using-tsql/