More Index Information

Overview

In addition to the sections we already covered, the following items are other things you should be aware of when working with SQL Server indexes.

Explanation

Index Usage

Monitoring index usage is an important part of a database administrator’s role.  Although you might add an index to fix a specific query, you need to make sure the whole system still performs as expected.  You may add an index with the wrong column order, and although it fixes the query, it could cause you additional maintenance.  The following DMVs, sys.dm_db_index_operational_stats and sys.dm_db_index_usage_stats, can provide you with some good information on the way the indexes in your system are being used both by DML operations as well as regular SELECT statements. 

The following tips go into a little more detail on this topic:

Missing Indexes

A database administrator is not always going to have a user tell them a specific query/operation is slow.  Nor is it always easy to figure out from an application exactly what backend query call is causing the slowness.  It’s also best if we can be more proactive when it comes to performance tuning, and there are a couple of different ways that we can identify indexes that are missing from queries in our system. 

The following tips show two different ways we can identify indexes that are missing from tables in our database:

Unused Indexes

Another performance issue that often gets overlooked when it comes to performance tuning is unused indexes.  Sometimes, a database administrator or developer will try to help performance by indexing almost all the columns in a table, just in case a query ever needs to use them.  In most cases, tables are queried/filtered/joined on only a few select columns.  Having too many indexes on a table can slow down INSERT/UPDATE/DELETE performance, as with every operation, the index also has to be updated.  If an index isn’t ever used by a query, all these extra operations could be avoided.  We can use the DMVs in SQL Server to determine if, in fact, an index is ever used by a query and even how often it’s used. 

The following tips give more details on how this can be done:

Index Fragmentation

We discussed fragmentation earlier in the tutorial, but it can’t be stressed enough that it’s a topic that should never be overlooked.  Every DBA should have a process in place to both identify fragmentation in indexes as well perform maintenance on the index when a certain level of fragmentation is identified.  An index with fragmentation, either external or internal, can lead to poor performance and also (although less important) unnecessary use of disk space. 

The following tips dive deeper into different ways that we can fix any fragmentation that is found in our indexes:

Related Posts

One comment

  1. Hi Ben, this is great material and very helpful for future reference. It would be interesting to add a section on the new json index as well.

Leave a Reply

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