Indexes with excessive writes

January 30, 2013 by | 5 Comments | 5,149 views
Categories:

VN:F [1.9.17_1161]
Rating: 3.4/5 (5 votes cast)

    This metric measures the total number of indexes per database, where the number of writes exceed the number of reads. It provides a general indicator of possible performance factors affecting queries in your database.

    If indexes are being updated with new data more often than they are being used in query plans, they can cause performance issues during write-heavy operations (INSERT, UPDATE, and DELETE statements), while adding little or no benefit to read operations.

    For more information, see General Index Design Guidelines.

    Indexes with excessive writes, 3.4 out of 5 based on 5 ratings

    Metric definition

    Metric Name:
    Indexes with excessive writes
    Description:

    This metric measures the total number of indexes per database, where the number of writes exceed the number of reads. It provides a general indicator of possible performance factors affecting queries in your database.

    If indexes are being updated with new data more often than they are being used in query plans, they can cause performance issues during write-heavy operations (INSERT, UPDATE, and DELETE statements), while adding little or no benefit to read operations.

    Guideline values: The ideal value for this metric should be 0, but not every bad index should be dropped. There may be some indexes which are used for occasional (but important) queries. This metric should be used to provide insight into the overall indexing strategy.

    Possible solutions: If this value indicates the need for further investigation, the following query can help identify indexes that may be candidates for adjustment or elimination:

    SELECT  OBJECT_NAME(s.object_id), i.name, i.type_desc

FROM    sys.dm_db_index_usage_stats s WITH ( NOLOCK )

JOIN sys.indexes i WITH (NOLOCK) ON s.index_id = i.index_id

AND s.object_id = i.object_id

WHERE   OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1

AND s.database_id = DB_ID()

AND s.user_updates > ( s.user_seeks + s.user_scans + s.user_lookups )

AND s.index_id > 1

    More information:

    General Index Design Guidelines
    Enter the T-SQL query that will collect data:
    SELECT  COUNT(*)
FROM    sys.dm_db_index_usage_stats s WITH ( NOLOCK )
WHERE   OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
        AND s.database_id = DB_ID()
        AND s.user_updates > ( s.user_seeks + s.user_scans + s.user_lookups )
        AND s.index_id > 1
    Select instances to collect from:
    Select all
    Choose databases to collect from:
    All databases
    Set collection frequency:
    Collect data every minute
    Use collected or calculated values:
    Leave the Use a calculated rate of change between collections check box turned off
    Metric collection is:
    Enabled

    Alert definition

    Alert name:
    High Write Indexes > 0
    Description:
    This alert is raised when the number of writes in a database index exceeds the number of reads. The following query can help identify indexes that may be candidates for adjustment or elimination:

    SELECT  OBJECT_NAME(s.object_id), i.name, i.type_desc

FROM    sys.dm_db_index_usage_stats s WITH ( NOLOCK )

JOIN sys.indexes i WITH (NOLOCK) ON s.index_id = i.index_id

AND s.object_id = i.object_id

WHERE   OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1

AND s.database_id = DB_ID()

AND s.user_updates > ( s.user_seeks + s.user_scans + s.user_lookups )

AND s.index_id > 1

    More information:

    General Index Design Guidelines
    Raise an alert when the metric value goes:
    Above the defined thresholds
    Default threshold values:
    Set the thresholds as follows:
    High:
    Medium:5
    Low:1
    Raise an alert when the threshold is passed for:
    Enter 1 collection
    This alert is:
    Enabled
    VN:F [1.9.17_1161]
    Rating: 3.4/5 (5 votes cast)
      • http://twitter.com/Phil_Factor Phil_Factor

        The best place to find out how and why this metric is useful is to read Performance Tuning with SQL Server DMVs. by Louis Davidson and Tim Ford, pages 189 – 202. It is wall-to-wall richness. It is useful to have the graph and  historical data for this metric because it can give you a lot more clues as to where the problem lies.

        VA:F [1.9.17_1161]
        Rating: 0 (from 0 votes)
      • http://profile.yahoo.com/FKOBAPKN2XAXUAHBTSCJTKSG4M Scott

        “while adding little or no benefit to read operations.”  What’s the justification for that statement?  The index could be extraordinarily helpful to the queries that use it, such as avoiding a full scan of a 200G table.  More index read than writes is certainly not optimal, but the index may still be quite needed.

        VA:F [1.9.17_1161]
        Rating: 0 (from 0 votes)
        • Stuart Ainsworth

          Hi Scott, that particular clause was in reference to the preceding clause “they CAN cause performance problems” [emphasis added].  In other words, some indexes are so write-expensive and used so infrequently (or not at all) that they would be candidates for either removal or re-design.  I agree with you; there are some indexes that are worth the cost (they may get used once a month, but that once-a-month run is worth the cost of maintaing the index for the previous 30 days); however, there are often cases where indexes are no longer necessary and should be pruned.  This metric was intended to be a case of “hey, here’s some things you should look at”, not necessarily “things you should eliminate”.

          Hope that clarifies.

          VA:F [1.9.17_1161]
          Rating: 0 (from 0 votes)
      • magasvs

        I usually exclude Unique indexes from these checks as well.

        VA:F [1.9.17_1161]
        Rating: 0 (from 0 votes)
      • SA

        It would be neat if one could execute the select and include the resulting list in the alert email!
        Then, we could get the list of indexes in the email automatically!

        VA:F [1.9.17_1161]
        Rating: 0 (from 0 votes)