index analysis help

Question

Hi All,

We see some tables in Prod are having 20-30 indexes and we wanted to do some cleanup to improve performance of DML queries.

In mid of Feburary month, we have collected index usage stats and we are seeing some indexes are contributing to more updates and not for reads+scans+lookups. After monthly patching we lost that information.So, wanted to know the best way to capture this unused index information and how to take a call on what indexes to be disabled/dropped.

Please share your thoughts on how to do the cleanup in a proper way.

App team wants to disable those indexes which are not useful from past one month. But since after monthly patching, we lost that index usage information. if we have to capture that data into a table and if we add capturedate as a column, how to do the analysis, is it on day-to-day or do we need to consider the last day before patching window to get proper index analysis done?

Regards,

Sam

Answer 1

Cleaning up unused indexes can help improve the performance of DML queries in your database. To collect and analyze index usage statistics, follow these steps:

1. Create a table to store index usage data:

CREATE TABLE dbo.IndexUsageStats (
    CaptureDate DATE,
    DatabaseID INT,
    ObjectID INT,
    IndexID INT,
    UserSeeks BIGINT,
    UserScans BIGINT,
    UserLookups BIGINT,
    UserUpdates BIGINT
);

1. Schedule a daily job to capture index usage data:

INSERT INTO dbo.IndexUsageStats (
    CaptureDate, DatabaseID, ObjectID, IndexID,
    UserSeeks, UserScans, UserLookups, UserUpdates
)
SELECT
    CAST(GETDATE() AS DATE),
    DatabaseID, ObjectID, IndexID,
    User_Seeks, User_Scans, User_Lookups, User_Updates
FROM sys.dm_db_index_usage_stats
WHERE DatabaseID = DB_ID(); -- Replace with the target database ID

1. After collecting data for a month, analyze the index usage:

WITH IndexStatsSummary AS (
    SELECT
        ObjectID,
        IndexID,
        SUM(UserSeeks) AS TotalSeeks,
        SUM(UserScans) AS TotalScans,
        SUM(UserLookups) AS TotalLookups,
        SUM(UserUpdates) AS TotalUpdates
    FROM dbo.IndexUsageStats
    WHERE CaptureDate > DATEADD(MONTH, -1, CAST(GETDATE() AS DATE))
    GROUP BY ObjectID, IndexID
)
SELECT
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    s.TotalSeeks,
    s.TotalScans,
    s.TotalLookups,
    s.TotalUpdates
FROM IndexStatsSummary s
JOIN sys.indexes i ON s.ObjectID = i.object_id AND s.IndexID = i.index_id
WHERE s.TotalSeeks = 0 AND s.TotalScans = 0 AND s.TotalLookups = 0
ORDER BY s.TotalUpdates DESC, OBJECT_NAME(i.object_id), i.name;

This query will show the indexes that haven't been used for reads (seeks, scans, or lookups) in the past month and are ordered by the number of updates. These are potential candidates for disabling or dropping. However, before making any changes, consult with the application team to ensure that these indexes are not needed for any specific use cases or reporting purposes.

Keep in mind that this analysis assumes that your workload is representative of typical usage during the month. If there are any unusual activities or ad-hoc queries that may impact index usage, consider extending the analysis period or investigate those activities separately. Always test the impact of disabling or dropping indexes in a non-production environment before making changes in production.

Answer 2

Hi @Samantha r

The T-SQL script below uses the sys.dm_db_index_usage_stats DMV, along with other system catalog views, to return a meaningful and useful cumulative information about each index and its usage since the last restart.

SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name
	   ,IX.name AS Index_Name
	   ,IX.type_desc Index_Type
	   ,SUM(PS.[used_page_count]) * 8 IndexSizeKB
	   ,IXUS.user_seeks AS NumOfSeeks
	   ,IXUS.user_scans AS NumOfScans
	   ,IXUS.user_lookups AS NumOfLookups
	   ,IXUS.user_updates AS NumOfUpdates
	   ,IXUS.last_user_seek AS LastSeek
	   ,IXUS.last_user_scan AS LastScan
	   ,IXUS.last_user_lookup AS LastLookup
	   ,IXUS.last_user_update AS LastUpdate
FROM sys.indexes IX
INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id
WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1
GROUP BY OBJECT_NAME(IX.OBJECT_ID) ,IX.name ,IX.type_desc ,IXUS.user_seeks ,IXUS.user_scans ,IXUS.user_lookups,IXUS.user_updates ,IXUS.last_user_seek ,IXUS.last_user_scan ,IXUS.last_user_lookup ,IXUS.last_user_update

The result can be analyzed as follows:

• All zero values mean that the table is not used, or the SQL Server service restarted recently.
• An index with zero or small number of seeks, scans or lookups and large number of updates is a useless index and should be removed, after verifying with the system owner, as the main purpose of adding the index is speeding up the read operations.
• An index that is scanned heavily with zero or small number of seeks means that the index is badly used and should be replaced with more optimal one.
• An index with large number of Lookups means that we need to optimize the index by adding the frequently looked up columns to the existing index non-key columns using the INCLUDE clause.
• A table with a very large number of Scans indicates that SELECT * queries are heavily used, retrieving more columns than what is required, or the index statistics should be updated.
• A Clustered index with large number of Scans means that a new non-clustered index should be created to cover a non-covered query.
• Dates with NULL values mean that this action has not occurred yet.
• Large scans are OK in small tables.
• Your index is not here, then no action is performed on that index yet.

You need to dig deeper before deciding to remove or replace an index. You can use the previous results in conjunction with the result of the sys.dm_db_index_physical_stats dynamic management function to have a full view of the index usage. The sys.dm_db_index_physical_stats DMF returns information about the lower-level I/O activities, such as INSERT, UPDATE and DELETE operations, occurred on that index, per each table partition.

Please refer to this article for more details: Gathering SQL Server indexes statistics and usage information.

Best regards,

Cosmog Hong