By: Greg Robidoux | Updated: 2022-05-26 | Comments | Related: > 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:
sys.dm_db_index_physical_stats.
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. Take a look at these articles 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 operations) Defrag - 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. |
| 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 | This article talks 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. |
| 12 | Non used indexes | Do you have any indexes that are not being used? With SQL Server you can use the 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
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2022-05-26
