? on Non-Clustered Indexes

  • jbalbo

    SSCertifiable

    Points: 7456

    Hi

    Recently we moved to replication of a DB we receive.

    I noticed it was slow, long story short, the non-clustered indexes were missing, added them back in and queries ran quickly again.

    My question is , how do I know when and what fields to add a non-clustered index?

    Any info/links would be appreciated

    Thanks

     

     

     

     

     

  • Mr. Brian Gale

    SSC-Insane

    Points: 22932

    Adding non-clustered indexes depends on the query being executed.  Generally, you want to look at the WHERE clause or the ORDER BY clause and anything those are filtering on will generally benefit from the index.

    Now on the other hand, an index is essentially a copy of the table data you are including in the index.  So if you have a 10 column table and have an index on 2 of the columns, those 2 columns (plus the clustered index key(s)) are going to exist in the table (either a heap or a clustered index) AND in the index.  And if you have any INCLUDES on the index, those are replicated in the index as well.  So you can imagine what this does to space over time.  Plus, it will affect performance of INSERT, UPDATE and DELETE statements as you need to insert, update, or delete from the table AND the indexes.

    So adding indexes blindly is a good way to hit performance problems,but also removing indexes blindly is a good way to hit performance issues.  There are also cases that can cause SELECT performance to be impacted in a negative way by adding indexes, but that is usually related to duplicate and/or unused indexes.

     

    But to answer your questions directly:

    WHEN - add indexes when you notice SELECT performance is slow and you are out of other options to improve performance

    WHAT FIELDS - add fields that are used to filter or order the data

    The reason I say "out of other options to improve performance" is because if you have some crazy complex query or you have a poorly written query (uses a cursor for example... which isn't always a poorly written query, but will always give you performance problems), it is often better to fix the bad query than to fix it at the table level.  It is EASY to add an index that makes your query faster, but it may be difficult to test all queries that touch that table and your new index MAY make everything else slower.

  • jbalbo

    SSCertifiable

    Points: 7456

    Thank You...

     

    This is great information to have.....

  • Mr. Brian Gale

    SSC-Insane

    Points: 22932

    If you'd like more information on indexes, I highly recommend Kendra Little's blog:

    https://sqlworkbooks.com/blog/

    Kendra is my "go-to" person for indexes!

    Brent Ozar has a blog post on it with Kendra Little too:

    https://www.brentozar.com/archive/2011/09/kendra-little-explains-how-design-smarter-indexes/

  • Grant Fritchey

    SSC Guru

    Points: 396560

    And to see what indexes are being used and how they're being used, you want to look at the Execution Plans. The book in my link is available in digital format for free, or you can pay for the paper copy.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • jbalbo

    SSCertifiable

    Points: 7456

    Thank you....

     

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

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