2

There are queries on the internet that finds bad indexes

Their logic is so simple though

If write count > read count = bad index

Here one example query

    SELECT  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 ;
-- Index Read/Write stats for a single table
SELECT  OBJECT_NAME(s.object_id) AS 'TableName',
        i.name AS 'IndexName',
        i.index_id,
        SUM(user_seeks) AS 'User Seeks',
        SUM(user_scans) AS 'User Scans',
        SUM(user_lookups) AS 'User Lookups',
        SUM(user_seeks + user_scans + user_lookups) AS 'Total Reads',
        SUM(user_updates) AS 'Total Writes'
FROM    sys.dm_db_index_usage_stats AS s
        INNER JOIN sys.indexes AS i 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 OBJECT_NAME(s.object_id) = 'AccountTransaction'
GROUP BY OBJECT_NAME(s.object_id),
        i.name,
        i.index_id
ORDER BY 'Total Writes' DESC,
        'Total Reads' DESC ;,

However is the issue this simple? Can we call all write > read = bad index?

Are there any better logic having SQL query that can define bad indexes?

Lets ignore indexes that ensures integrity of data such as unique constraints

Lets assume that indexes are updated and used every minute

I am using SQL server 2014 and here the results this query generates

Ty

enter image description here

2
  • 2
    Consider that a unique index (constraint or not) may be used for data integrity, not just performance. This would not be a "bad" index.
    – Dan Guzman
    Aug 1, 2016 at 11:17
  • @DanGuzman that makes sense Aug 1, 2016 at 11:52

2 Answers 2

5

However is the issue this simple?

Absolutely not. Unfortunately it can vary a lot between applications so while there are many "rules of thumb" like the one you state none of them are universally applicable apart from "an index that is never used is a waste".

Can we call all write > read = bad index?

If that index is for enforcing integrity (i.e. it is a unique index or is supporting a foreign key) then that fact would generally trump a performance requirement. Also, the writes could be mostly at times where it doesn't matter (a bulk update in the middle of the night perhaps) or it could be really important that those reads are as fast as possible so slow writes are a price worth paying.

When thinking about these matters always consider the time period that any figures you are looking at cover. It could be that an index looks under-used this week, but next week is the start of a new reporting period so the users are performing a different set of actions and the index suddenly looks far more useful.

Never use rules-of-thumb as hard-and-fast rules to live by. Use them as suggestions and consider the results carefully with your knowledge of your specific application(s)'s needs.

can you expand your answer and add more details?

Not without writing a whole book unless you have more specific questions, this is a very wide area. Many whole book have been written on the subject in fact... http://use-the-index-luke.com/ is a good place to start.

1
  • ty for answer. can you expand your answer and add more details? i have added 2 assumptions to the original question : Lets ignore indexes that ensures integrity of data such as unique constraints Lets assume that indexes are updated and used every minute Aug 1, 2016 at 11:55
3

There is no absolute logic. To define a good index, in my terms

" If an index speeds up your query and makes it faster, it is a good index and you can keep that index".

You have sp_blitzindex from Brent Ozar which would point out unused and bad indexes. Also you have Jason Strates index Analysis script which would come handy. I guess both of above will do the job for you. But if you like other approach, below is what I follow sometimes.

Regrading user reads and user writes there is no absolute value from which you can determine whether index is aptly utilized or not. But what I actually do is divide user reads by user writes and then multiply it by 100 to get the percentage.

NOTE: This would always not be applicable I use it as "tie breaker".

  1. If percentage is > 60 you can keep that index
  2. If percentage is between 50 and 60 you yourself need to figure out whether this index is helpful or not.
  3. If it is less than 50 the index is causing more of I/O utilization in updates than it is helping in making query faster.
  4. If it is around 20-30% you can first disabled and then removed.

Example:

Look at index b it is totally not usable at all. It would be correct decision to first disable it and then remove it.

Look at index a the index is both read and updated but updated more than read. The division value is .80 which is almost 80 %. You can keep this index it both being utilized and updated, I dont see issue with this index.

Look at index C the division give .33 value and percentage is 33 %. This index again has to be looked into this is doing more harm than it is doing good.

The whole idea about percentage is based on how good knowledge you have about your database. The decision to keep or remove an index should be largely based on requirement. Its quite possible index might be utilized fortnightly or weekly but updated every day. This may be because a job runs weekly or fortnightly and utilizes those indexes only at that time.

Moreover you should not worry about small indexes which have page count < 2000, again this is ball park figure, such indexes cannot do any harm.

3
  • ty for answer really detailed. i added 2 assumption to the original question : Lets ignore indexes that ensures integrity of data such as unique constraints Lets assume that indexes are updated and used every minute Aug 1, 2016 at 11:55
  • i have checked your link and it has these commands , REALIGN , DROP-USAGE , DROP-COUNT , and --- . i suppose for --- i dont have do anything and for others i should apply right? Aug 1, 2016 at 12:02
  • For indexes which are used and updated frequently the percentage would come around 50-60% and then you have to make sure that indexes help the query when they run. If previous is true you should keep that index. The whole point is index should help make query faster and should also be utilized.
    – Shanky
    Aug 1, 2016 at 12:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.