how to know which index useful and which one is bad ?

Farshad Valizade 501 Reputation points
2024-02-07T10:09:02.7566667+00:00

Hi I have a database with man tables.I have put index(clustered and unflustered ) in tables that I think that need. I have a SP that join 20 tables with inner and outer join and make a result for user. It takes 10 second to populate data.I want to get in in less than 3 second if it can. so I read many topics and ebooks about query optimization index and tuning and DMV , .... but all of them just says Index is good or etc and none of them says how we can check which index is right or not ? I use sys.dm_db_index_usage_stats but I don't know how to get info from this quey result. can any one tell me about my problem?

SQL Server | Other
0 comments No comments
{count} votes

Accepted answer
  1. LiHongMSFT-4306 31,571 Reputation points
    2024-02-08T02:09:57.0566667+00:00

    Hi @Farshad Valizade

    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 number of Seeks indicates the number of times the index is used to find a specific row, the number of Scans shows the number of times the leaf pages of the index are scanned, the number of Lookups indicates the number of times a Clustered index is used by the non-clustered index to fetch the full row and the number of Updates shows the number of times the index data is modified.

    You could analyze the query result 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.

    Referring from this article: Gathering SQL Server indexes statistics and usage information.

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Zahid Butt 961 Reputation points
    2024-02-07T15:01:53.1+00:00

    Hi Farshad, You may use below query to check(https://www.johnsansom.com/finding-bad-indexes/) :

    --Possible bad Indexes (writes > reads)
    SELECT  db_name() databasename,OBJECT_NAME(s.object_id) AS 'Table Name',
            i.name AS 'Index Name',
            i.index_id,
            user_updates AS 'Total Writes',
            user_seeks + user_scans + user_lookups AS 'Total Reads',
            user_updates - ( user_seeks + user_scans + user_lookups ) AS 'Difference'
    FROM    sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )
            INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON s.object_id = i.object_id
                                                           AND i.index_id = s.index_id
    WHERE   OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
            AND s.database_id = DB_ID()
            AND user_updates > ( user_seeks + user_scans + user_lookups )
            AND i.index_id > 1
    ORDER BY 'Difference' DESC,
            'Total Writes' DESC,
            'Total Reads' ASC ;
    
    
    1 person found this answer helpful.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.