Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Index Maintenance Checklist


By:   |   Read Comments   |   Related Tips: More > Fragmentation and Index Maintenance

Problem
In a previous tip we discussed 10 items that should be addressed to manage your SQL Server environment. These items included; backups, security, disaster recovery, etc...  The third item on the list was to maintain indexes.  There have been several tips on the MSSQLTips.com website about index maintenance, but we will take a look at a checklist of things to perform to ensure your indexes are not degrading performance on your system.

Solution
The following checklist is a list of items that you should consider when implementing integrity checks.

# Item Steps
1 Fragmentation One of the biggest performance hits that you will see on your databases, next to not having indexes, is indexes that are very fragmented.  Fragmentation causes issues where it takes SQL Server more time to traverse the index tree to find the necessary records.  This can be checked using one of these two methods:
  • DBCC SHOWCONTIG (SQL 2000 or 2005)
  • sys.dm_db_index_physical_stats (SQL 2005)

Another problem with indexes that are very fragmented is that it takes up a lot more space to maintain these indexes and therefore impacting just about all aspects of your database.

In addition to the two commands above, there are other tools in the market that allow you to determine fragmentation issues.

Take a look at this tip for more information.

2

Index Rebuilds or Defrags

Once you have identified which indexes are fragmented and need some assistance you need to determine whether you use the index rebuild or defrag operation.  There are a couple of key differences between these operations:
  • Rebuild - a complete rebuild of the index, cleanest approach, but requires the index to be offline (Enterprise Edition offers online operation)
  • Defrage - only defrags part of the index, not as clean as rebuild, but this is an online operation so index is still available

Read this tip for more information.

3 Placing Indexes on Separate File Groups Another potential performance gain would be to place your non-clustered indexes on a separate file group from your clustered index or heap table.  This will allow SQL Server to hit different files and hopefully different disks to reduce any IO bottleneck that may occur.

The key here is the filegroups are on different physical disks to really get the IO improvement.

4

Over indexed tables

Indexing is a great way to ensure SQL Server finds the data as quick as possible.  But on the flip side too many indexes on a table may degrade performance.  For example let's say a table has 10 indexes. Each time a new record is added, deleted or updated there is a potential that all 10 indexes need to be maintained. So too many indexes is not always helpful.

Take a look at this tip to get a listing of all your indexes, so you can analyze what is out there.

5 Under indexed tables The opposite is also true, too few indexes can hurt performance by having to do unnecessary table scans.  The way to determine this is to use profiler to capture your longest running queries and then examine the execution plans to see where indexes could help.  You could also take advantage of the Index Tuning Wizard or the Database Engine Tuning Advisor.
6 Duplicate indexes Another thing to look for is duplication of indexes.  I have seen this occur where a clustered index or primary key is created and then another index is created for the exact same columns.  Take the time to review your indexes and remove any duplication.  This will cut down on the time it will take SQL Server to maintain these additional indexes.

Take a look at this tip to get a listing of all your indexes, so you can analyze your indexes.

7 Clustered indexes or not In most cases all tables should have a clustered index.  This is a good practice as well as allowing you to physically store the data according to the clustered index.  This can help on queries where you need to return ranges of data.  Also, clustered indexes are helpful to combat fragmentation issues that may occur with heap tables.

Take a look at this tip to get a listing of all your indexes, so you can analyze your indexes.

8 Scheduling Maintenance Above we mentioned about how to find out if there is fragmentation and the options of using rebuilds or defrags.  Once you have determined this you should setup a scheduled time of when you will address these issues.  The best time to run these is off hours when the load is low. Also, how frequently this is run really depends upon how fragmented your indexes become and how quickly they become fragmented. In some cases there are indexes that probably never need to be rebuilt, but often it is easier to apply maintenance across the board.
9 Covering indexes Covering indexes include more then just the column that you are searching on.  Let's say for example you want to search by LastName. You could create an index on just LastName, but you know that there is always the need to get FirstName, City and State.  So based on this you could create an index that has all four columns, so when the query is executed it can get all of its data from the covering index instead of having to lookup the data from the clustered index.
10 Ascending and Descending indexes A recent tip talked about ascending and descending indexes.  This tip showed how it doesn't really matter if the index is created in either ascending or descending order for one column, but how key this can be when there are multiple columns and there is a need to have one column ascending and another descending. Keep this in mind when you think about how the indexes will be used.
11 Indexes on columns that are not very selective Another issue that I see quite often is indexes being put on columns that are not very selective. What this means is that there are so few differences in the values that are stored in the column that SQL Server will never use the index.  Some examples of this include gender codes (M,F) or something else where there are too few values for the index to be useful.  These columns should not be indexed directly, but could be used as a secondary column in an index for a covering index or used as an included column in SQL Server 2005.
12 Non used indexes Do you have any indexes that are not being used? This is not that easy to determine with SQL Server 2000, but there are some third party tools that can collect this data and show you which indexes are being used and which ones are not being used.  With SQL Server 2005 you can use the new DMV sys.dm_db_index_usage_stats.  This will give you an idea of which indexes are being used and how they are being used. Take a look at this tip How to get index usage information for more information.

 

Next Steps

  • This list should give you a good foundation for what should be done to maintain your indexes.  Take a look through the list and mark off each item that you have in place.
  • Review the list to determine which items you do not have covered and how you can go about getting these implemented.
  • Stay tuned for other administration checklists.
  • Check out these other tips


Last Update:






About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools