One of the most important things to measure, monitor, and “get right” for good overall SQL Server OLTP performance is the number and composition of your SQL Server relational indexes. It is extremely important to have neither too many or too few relational indexes on each table, and that the indexes you do have are being used effectively by your workload.

Every time you modify data in a table(with a insert, update, or delete), the affected indexes must also be updated. With an OLTP workload (which means that your data is volatile) you typically want to have fewer indexes than with a DSS/DW type of workload (where the data is relatively static). One guideline from Tom Davidson when he was on the SQLCAT team in 2006 was to try to have less than four indexes on tables that were frequently updated. Lindsey Allen (from SQLCAT) also discussed the cost of having too many relational indexes here. I generally start to get concerned when I see more than about five or six relational indexes on a volatile table. Having too many relational indexes has a negative effect on insert, update, and delete performance. Ultimately, it is a judgment call, based on your workload, your hardware and I/O subsystem, and on your experience.

On the other hand, if you have too few effective indexes or you are “missing” important indexes that SQL Server could effectively use to satisfy its query workload, you will also see bad overall query performance. You will see table or index scans where a seek could have satisfied a query that only returned a few rows of data. This can cause increased memory, I/O, and CPU pressure, depending on the situation. It can also cause extra locking and blocking while these extra scans are occurring.

What you want to have is a happy balance between too many and two few relational indexes. You want to eliminate indexes that are not being used, and add indexes that SQL Server thinks it needs (using your own good judgment and knowledge of your workload). After all, my two miniature dachshunds always think they need more doggie treats, but I sometimes know better!

Here are a couple of queries that will help you reach this sweet spot for your relational indexes. I like to call these two the “dynamic duo”, since they are so useful for judging whether you need to adjust your overall index strategy at the database level.

After I look at that, I tend to try to focus on a single table in more detail before I make any changes. You should not just start blindly dropping or adding indexes based on these two queries alone. You should evaluate your existing indexes on that table and consider your complete workload, and apply some good human judgment before you make index changes.

The queries below will help focus your attention on a single table. In this case, I am looking at the ActivityEvent table.

Remember, sp_helpindex does not show information about included columns or filtered indexes. One work-around is to script out the index creation DDL statements for each existing index to see what is really there. You can also use Kimberly Tripp’s (blog|twitter) excellent sp_helpindex2 procedure to pickup that information.

Comments

Posted by Jason Brimhall on 7 January 2011

Good timing with Gails article today and Michelle Uffords at Simpletalk recently.