SQL SERVER – Poor Indexing Strategies – 10 Don’ts for Indexes

Earlier this week, I wrote a blog post about SQL SERVER – Top Reasons for Slow Performance. The blog post got extremely popular as it contained many real-world problems and suggestions. After the blog post was released, I received quite many emails and questions. In response to the blog post, I have received a lot of comments on my  Twitter and LinkedIn. The most questions were around section Poor Indexing Strategies and that lead me to write about 10 Don’ts for Indexes.

SQL SERVER - Poor Indexing Strategies - 10 Don'ts for Indexes Poor-Indexing-Strategies-800x485

One of my FinTech clients who often hire me for Comprehensive Database Performance Health Check just sent me their 10 Don’ts for the Indexes which I had built with them when I worked along with them. I think the list is very simple but effective. I suggest everyone create their such list and keep in front of developers.

Solarwinds

10 Don’ts for Indexes – Poor Indexing Strategies

  1. Don’t index every column of the table.
  2. Don’t create more than 7 indexes per table (clustered and non-clustered)
  3. Don’t leave a table as Heap (create a clustered index).
  4. Don’t create an index on every column involved in every foreign key
  5. Don’t rebuild an index too frequently (monthly once is good enough)
  6. Don’t create an index with more than 5 to 7 key columns
  7. Don’t create an index with more than 5 to 7 included columns
  8. Don’t add your clustered index key in your non-clustered index
  9. Don’t change server fill factor, change it at an index level (as needed)
  10. Don’t ignore index maintenance
    1. Missing Index Script
    2. Unused Index Script

In your organization, if you are following the above 10 simple guidelines, I am very confident that your server would be running absolutely fine and you may not need not worry about indexes anymore. There are many different guidelines one has to follow if you want 100% performance for your SQL Server. However, that is out of the scope of this blog post and we often discuss that in detail Comprehensive Database Performance Health Check.

If you are following all the 10 guidelines of what to not do with index and if you are still facing performance problems, I am very sure that the issue is related to either your deployment or how your queries are following anti-patterns. I will write about that in the future.

Give yourself 1 point for each Don’t which you follow and post your score in the comments. If you get 10/10, your organizations should be proud of you.

Update: Always create indexes after doing proper workload analysis of your system. Just creating indexes because of best practices is not always a great idea.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – Disable Parameter Sniffing with DISABLE_PARAMETER_SNIFFING Query Hint

Related Posts

8 Comments. Leave new

  • AHMED ALI ELAGOUZ
    February 13, 2020 1:52 pm

    i am DBA on database for dynamics ax 2012 r2 and i face problem on performance for some tables there is built in indexes with the custom that comes from query so the count of them more than 7 indexes per some tables so is it possible to be special case
    also i need to create script for specific table (not all tables) inside dynamics database to check which indexes need rebuild and which need reorganize

    Reply
  • Peter D Daniels
    February 13, 2020 3:50 pm

    Not sure I agree with #4. Yes, there are cases where adding an index on FKs is not needed, but very frequently FKs benefit frim from an index.

    Reply
    • Great Point Peter,

      The title of the blog post is what not to do. Very often people create indexes all the FK and eventually build lots of indexes. The goal is not to create indexes on every single FK.

      One has to do a proper analysis of their workload and come up with 5-7 best possible indexes. Creating indexes on all the FK may just lead to lots of indexes, which do not necessarily help the performance.

      Now after workload analysis, if you find you need to create an index on FK, absolutely is welcome.

      However, I see, why this statement can pass an incorrect messages. Let me modify it a bit more to pass the correct intent.

      Thanks for bringing to my attention.

      Reply
  • Don’t add your clustered index key in your non-clustered index

    What is wrong with this ? I understand clustering key will be already part of the NC. I don’t think SQL server will duplicate it even clustering key columns are added explicitly in NC.

    Sometime it is useful to have clustering key column for the included non cluster index. For example high selective seekable queries.

    So my opinion is not to generalise it.

    Reply
    • The question is if it is already part of it, why add them again? The reason of high selective seekable queries will just work without even adding the keys.

      Reply
  • This is such a broad statement to claim there will be no performance issues.

    Based on the above, if I had a database where each table only had a single clustered index on an auto incrementing Id and contained millions of rows, there would be no performance issues. No chance!

    Reply
  • What about covering indexes to improve query performance? Most of the time they have more then 5-7 columns..

    Reply

Leave a Reply

Menu