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.
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.
10 Don’ts for Indexes – Poor Indexing Strategies
- Don’t index every column of the table.
- Don’t create more than 7 indexes per table (clustered and non-clustered)
- Don’t leave a table as Heap (create a clustered index).
- Don’t create an index on every column involved in every foreign key
- Don’t rebuild an index too frequently (monthly once is good enough)
- Don’t create an index with more than 5 to 7 key columns
- Don’t create an index with more than 5 to 7 included columns
- Don’t add your clustered index key in your non-clustered index
- Don’t change server fill factor, change it at an index level (as needed)
- Don’t ignore index maintenance
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)