More Index Information
By: Ben Snaidero
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.
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.
- How to get Index Usage Information in SQL Server
- Retaining Historical Index Usage Statistics for SQL Server
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.
- Using SQL Server DMVs to Identify Missing Indexes
- Missing Index Feature of SQL Server 2008 Management Studio
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.
- Deeper Insight into Unused Indexes for SQL Server
- Discovering Unused Indexes
- Safely Dropping Unused SQL Server Indexes
- Find Unused SQL Server Indexes with Policy Based Management
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.
- Script to Manage SQL Server Rebuilds and Reorganize for Index Fragmentation
- Managing SQL Server Database Fragmentation
- Fixing Index Fragmentation in SQL Server 2005 and SQL Server 2008
- SQL Server Index Fragmentation Overview
Last Update: 7/25/2018