The following stored procedure can be used for removing unnecessary indexes:?

pr_DropUnusedIndexes

The script for adding this procedure to your database is below and embedded usage notes:

??????????? Createpr_DropUnusedIndexes.sql

The risk associated with dropping unused indexes would be that occasionally an ad-hoc query in the report builder would run more slowly. In that event it may be desirable to restore the missing indexing. The MS SQL Management Studio provides statistics for missing indexes and can recommend index additions.?

The pr_DropUnusedIndexes procedure drops indexes used less than a specified number of times. The default is "1" time...meaning drop indexes that have never been used. The procedure will exit immediately if the sql server has not been up for at least 7 days to ensure meaningful statistics exist. The procedure also does not drop sensitive indexes use for internal processing.

The default setting calls of dropping indexes that have never been used since the statistics started being collected.
To specify a larger number of uses to target for dropping declare a variable and set it as in this example:

declare @minKeepUsage int = 10

exec pr_DropUnusedIndexes ?@minKeepUsage

Here is an example of the result:

Before dropping any indexes index space used is 29400 KB

Indexes that have been used less than 1 time(s) will be dropped.

Dropping index IX_TD_AGGREGATION_55_DATA_39 on table TD_AGGREGATION_55_DATA

Dropping index IX_TD_AGGREGATION_172_DATA_37 on table TD_AGGREGATION_172_DATA

Dropping index IX_TD_AGGREGATION_172_DATA_38 on table TD_AGGREGATION_172_DATA

Dropping index IX_TD_AGGREGATION_172_DATA_39 on table TD_AGGREGATION_172_DATA

Dropping index IX_TD_AGGREGATION_235_DATA_40 on table TD_AGGREGATION_235_DATA

Dropping index IX_TD_AGGREGATION_235_DATA_10 on table TD_AGGREGATION_235_DATA

Dropping index IX_TD_AGGREGATION_235_DATA_13 on table TD_AGGREGATION_235_DATA

Dropping index IX_TD_AGGREGATION_235_DATA_12 on table TD_AGGREGATION_235_DATA

Dropping index IX_TD_AGGREGATION_235_DATA_11 on table TD_AGGREGATION_235_DATA

...

Dropping index IX_TD_AGGREGATION_86_DATA_12 on table TD_AGGREGATION_86_DATA

Dropping index IX_TD_AGGREGATION_86_DATA_11 on table TD_AGGREGATION_86_DATA

Dropping index IX_TD_AGGREGATION_71_DATA_10 on table TD_AGGREGATION_71_DATA

Dropping index IX_TD_AGGREGATION_71_DATA_13 on table TD_AGGREGATION_71_DATA

Dropping index IX_TD_AGGREGATION_71_DATA_12 on table TD_AGGREGATION_71_DATA

Dropping index IX_TD_AGGREGATION_71_DATA_11 on table TD_AGGREGATION_71_DATA

Dropping index IX_TD_AGGREGATION_71_DATA_39 on table TD_AGGREGATION_71_DATA

Dropping index IX_TD_AGGREGATION_71_DATA_38 on table TD_AGGREGATION_71_DATA

Dropping index IX_TD_AGGREGATION_71_DATA_37 on table TD_AGGREGATION_71_DATA

Dropping index IX_TD_AGGREGATION_71_DATA_40 on table TD_AGGREGATION_71_DATA

Dropped 1466 indexes.

After dropping indexes index space used is 15408 KB

Additional Notes:

• To measure the potential savings from dropping to unused indexes use a system catalog usage statistics query.

• This query may be time consuming to run on a large database.

• If Overstat is installed the usability dimension and aggregation indexes are often among the largest but as they will have active "Usage" statistics they will not be trimmed. The best way to reduce index cost for those tables would be to reduce data retention manually (as there is no separate setting for that) and/or to ensure these dimensions remain under control by using filter events so that unwanted and dynamic element IDs are not stored.

USE TL_REPORTS;

select object_name(i.[object_id]) as 'object_name' i.name 'index_name' f.name as 'index_filegroup'

sum((au.pages * 8192.0)/1048576.0) as 'MB' sum(ius.user_lookups + ius.user_scans + ius.user_seeks) as 'usage'

sum(ius.user_updates) 'updates'

CAST(sum(ius.user_lookups + ius.user_scans + ius.user_seeks) as float)/sum(case when ius.user_updates = 0 then 1 else ius.user_updates end) as 'usability'

from sys.dm_db_index_usage_stats ius

join sys.indexes i on i.[object_id] = ius.[object_id] and i.index_id = ius.index_id and i.index_id > 1

join sys.data_spaces ds on ds.data_space_id = i.data_space_id

join sys.filegroups f on f.data_space_id = ds.data_space_id

join (select p.[object_id] p.index_id SUM(au.total_pages) pages

?????from sys.partitions p

?????join sys.allocation_units au

?????on (au.[type] in (13) and au.container_id = p.hobt_id)

?????or (au.[type] = 2 and au.container_id = p.partition_id)

?????group by p.[object_id] p.index_id

?????)

?????au on au.[object_id] = i.[object_id] and au.index_id = i.index_id

group by object_name(i.[object_id]) f.name i.name

order by 3 asc 4 desc

Example Output

object_name ??????????????index_name ??????????????????index_filegroup ??MB ?????????????usage ???updates ?usability

------------------------ ?--------------------------- ?---------------- ?--------------- ------- ?-------- ------------------

TD_DEVIATION_LT ??????????UQ_DIM_DEVIATION_DTS_LT ??????AGGDATA ?????????0.0625000000 ???801 ?????79 ??????10.1392405063291

TD_DEVIATION ?????????????UQ_DIM_DEVIATION_DTS ?????????AGGDATA ?????????0.0000000000 ???434 ?????56 ??????7.75

TD_AGGREGATION_55_DATA ???IX_TD_AGGREGATION_55_DATA_10 ?AGGINDEX ????????0.0312500000 ???2806 ????26 ??????103.925925925926

TD_AGGREGATION_55_DATA ???IX_TD_AGGREGATION_55_DATA_11 ?AGGINDEX ????????0.0156250000 ???0 ???????26 ??????0

TD_AGGREGATION_55_DATA ???IX_TD_AGGREGATION_55_DATA_12 ?AGGINDEX ????????0.0156250000 ???0 ???????26 ??????0

Note:
As this query provides generic SQL Server statistics information refer to SQL Server documentation for more information on these numbers. The query may require a long run time if the database is large.

Another example: (usage counts without size)

USE TL_REPORTS;

SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME]

??????I.[NAME] AS [INDEX NAME]

??????A.LEAF_INSERT_COUNT

??????A.LEAF_UPDATE_COUNT

??????A.LEAF_DELETE_COUNT

FROM ??SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULLNULLNULLNULL ) A

??????INNER JOIN SYS.INDEXES AS I

????????ON I.[OBJECT_ID] = A.[OBJECT_ID]

???????????AND I.INDEX_ID = A.INDEX_ID

WHERE ?OBJECTPROPERTY(A.[OBJECT_ID]'IsUserTable') = 1

Tealeaf versions 8.x 9.x