More Index Information


MENU
By:
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.

Index Usage

Monitoring index usage is an important part of a database administrators 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 details on this topic.

Missing Indexes

A database administrator is not always going to have a user tell him 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 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.


Last Update: 7/25/2018

<< Previous

Next >>



Comments For This Article

















get free sql tips
agree to terms