When should I add an index?

SQL Server Best Practices
SQL Server Best Practices
We all know indexes are good and I’m hoping everyone knows you can have too many indexes. That means we should be careful when adding new indexes right? So when should we add a new index?

Here are my general rules of thumb, although of course, you should always use your best judgment. Also, this is for OLTP systems (ex: data entry systems) not OLAP systems (ex: data warehouses).

  • No Clustered Index: You really should add a clustered index. Clustered indexes are important for a number of reasons, so if you don’t have one spend some time, figure one out and create it. When possible I like dates as a clustered index but of course, it’s highly specific to the table you are working on. There are a few types of tables, some load tables, for example, were not having a clustered index is appropriate but they are few and far between.
  • 1-5 indexes: Is this index going to help performance? If so add after running some tests in your dev/test systems.
  • 5-10 indexes: At this point, we need to be more careful. Do we have any indexes that aren’t being used? Are there any that can be combined? Once all of this has been done then we might as well add the index. We are starting to have a few too many indexes but it’s not entirely unreasonable.
  • 10+ indexes: We are now starting to hit the too many indexes category. Same reviews as above but more urgency about getting rid of unused indexes and add in getting rid of underused indexes (few reads but still getting writes). At this point, I’m also a bit more cautious about adding indexes. It had best be for an important query.

There are a few important questions you may have at this point:

Continue reading on SQLStudies.com.

(0 votes. Average 0 of 5)
Leave a reply

Your email address will not be published. Required fields are marked *