Unused Indexes

  • Dear Experts,
    I generated a list of unused indexes from my database by equating User_scans, user_seeks , and user_lookups to zero. Though I got the data , I could see that some of them have Index Name as NULL . Can anybody share the reason for Index names not appearing for some . Any similar experiences ?  Thank you in advance.

    Best Regards
    Arshad

  • Arsh - Wednesday, September 12, 2018 5:34 AM

    Dear Experts,
    I generated a list of unused indexes from my database by equating User_scans, user_seeks , and user_lookups to zero. Though I got the data , I could see that some of them have Index Name as NULL . Can anybody share the reason for Index names not appearing for some . Any similar experiences ?  Thank you in advance.

    Best Regards
    Arshad

    I would guess your query pulls the name from sys.Indexes and name will appear as Null when indid = 0. The table is a heap, there is no index.
    You could add type or type_desc to your query if you want to differentiate those.

    Sue

  • In addition to identifying the heaps via the NULL index name, if you're using sys.dm_db_index_usage_stats it won't include information for anything that hasn't been touched at all, which can be confusing if you don't see tables there when you expect to.

    If you're not already accounting for it, the below script returns info for everything:
    SELECT * from (
      SELECT
          u.object_id
          ,[SchemaName] = OBJECT_SCHEMA_NAME(u.object_id)
       ,[TableName] = OBJECT_NAME(u.object_id)
       ,[IndexName] = CASE WHEN u.index_id = 0 THEN 'HEAP' ELSE i.name END
       ,[Reads] = SUM(user_seeks + user_scans + user_lookups)
       ,[Writes] = SUM(user_updates)
              FROM sys.dm_db_index_usage_stats u
              JOIN sys.indexes i
              on u.index_id = i.index_id
              and u.object_id = i.object_id
              WHERE u.database_id = DB_ID()
              AND u.object_id > 100
              group by database_id, u.object_id, u.index_id, i.name
      UNION ALL
      SELECT
          o.object_id
       ,OBJECT_SCHEMA_NAME(o.object_id)
       ,object_name(o.object_id)
       ,CASE WHEN i.index_id = 0 THEN 'HEAP' ELSE i.name END
          ,0
       ,0
          FROM sys.indexes i
          JOIN sys.objects o ON i.object_id = o.object_id
          WHERE o.type_desc in ('user_table')
          and i.index_id NOT IN (select s.index_id from sys.dm_db_index_usage_stats s where s.object_id=i.object_id
          and i.index_id=s.index_id and database_id = db_id(db_name()) )
    ) as tableUsage
    order by reads, writes

  • Thank you Sue . Thank you to you too SQLPirate ; and Yes I am usage dm_db_usage_stats . Your query gets everything and that's useful as well.

    Best Regards

    Arshad

  • Arsh - Sunday, September 16, 2018 4:31 AM

    Thank you Sue . Thank you to you too SQLPirate ; and Yes I am usage dm_db_usage_stats . Your query gets everything and that's useful as well.

    Best Regards

    Arshad

    The trouble is that view resets to being empty when the SQL Server Service is restarted.  Unless you're collecting data over a long time without restarts or you have a system that does regular updates to a control table of your own, you could be identifying indexes as "unused" when they actually are.

    There are also indexes associated with certain constraints that may never see user usage.  For example, a "unique" constraint, which is usually an uber important constraint especially when FK's and other data quality methods are concerned.  If you drop such an index because of apparent lack of user usage, there could be serious hell to pay.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Sunday, September 16, 2018 9:54 AM

    The trouble is that view resets to being empty when the SQL Server Service is restarted.  Unless you're collecting data over a long time without restarts or you have a system that does regular updates to a control table of your own, you could be identifying indexes as "unused" when they actually are.

    There are also indexes associated with certain constraints that may never see user usage.  For example, a "unique" constraint, which is usually an uber important constraint especially when FK's and other data quality methods are concerned.  If you drop such an index because of apparent lack of user usage, there could be serious hell to pay.

    Yes Jeff , that's a really good point . I'll have an exception list probably based on such important points and a full cycle end to end testing would provide a better picture. Nonetheless , the duplicate indexes are definitely potential candidates to do away with . Thank you for the point put forth.

    regards..Arshad

  • Arsh - Monday, September 17, 2018 1:53 AM

    Jeff Moden - Sunday, September 16, 2018 9:54 AM

    The trouble is that view resets to being empty when the SQL Server Service is restarted.  Unless you're collecting data over a long time without restarts or you have a system that does regular updates to a control table of your own, you could be identifying indexes as "unused" when they actually are.

    There are also indexes associated with certain constraints that may never see user usage.  For example, a "unique" constraint, which is usually an uber important constraint especially when FK's and other data quality methods are concerned.  If you drop such an index because of apparent lack of user usage, there could be serious hell to pay.

    Yes Jeff , that's a really good point . I'll have an exception list probably based on such important points and a full cycle end to end testing would provide a better picture. Nonetheless , the duplicate indexes are definitely potential candidates to do away with . Thank you for the point put forth.

    regards..Arshad

    Be careful  even with that, Arsh.  I've seen people drop NCIs that were an exact duplicate of the Clustered Index key(s) and it crushed performance in many areas because the NCI was a whole lot more narrow at the leaf level than the CI.  I even have a presentation with code that demonstrates the huge performance increase thanks to an NCI that duplicated the CI key.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply