Post reply

How can identify those indexes which are slow down database performance.

  • February 2, 2018 at 4:58 am

    #343811

    Identify those indexes which are slow down database performance.

     

  • February 2, 2018 at 5:39 am

    #1978330

    A few things to look out for:
    - Indexes that are never used for seeks or scans
    - Indexes that are too wide (have more columns than are necessary to satisfy a query)
    - Indexes that are duplicates, or subsets, of other indexes
    - Lack, or inappropriate choice, of clustered index

    This is a big topic - far too much for a forum post.  Some of it comes down to experience.  Other things can be identified using DMVs and catalog views.  What is the context of your question - do you suspect you have "bad" indexes?

    John

  • February 2, 2018 at 5:49 am

    #1978331

    Hi John,
    Thanks for your response.
    I want to identify all Indexes which are "Incorrect" and slow down  database/  query performance.

  • February 2, 2018 at 6:09 am

    #1978334

    Indexes don't slow down query performance though. And what exactly do you define as an 'incorrect' index?

    Technically all indexes will slow down data changes (inserts), but that's usually not a problem.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 2, 2018 at 5:42 pm

    #1978435

    Try using the following tables to extract info.
    sys.indexes
    sys.dm_db_index_usage_stats
    sys.dm_db_index_physical_stats

Viewing 5 posts - 1 through 4 (of 4 total)

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