SQL Server uses indexes to sort and organize table data. It creates indexes based on ordering values from one or more selected columns. SQL Server automatically creates indexes when you define a primary key or unique constraint. You can use indexes to manage and track rows in a table or an indexed view. Indexes in general improve the performance on most data scans. However, when tables have records inserted or updated, the indexes become fragmented, which can adversely affect the query and response time. This is because, when we access data through an index, SQL Server read each index page in the specified range to retrieve the indexed values, and when highly fragmented, SQL Server database engine has to search many more pages, more levels, to retrieve the required data. This results in poor performance and causes your application to respond slowly.

That’s why, as a database administrator, it is one of our responsibilities to create efficient re-indexing strategy for our databases that will ensure that the database works fine and performs well. Although, SQL Server offers maintenance plans where you can add re-indexing task to re-index all indexes of all databases hosted on the SQL Server instance. The problem with this re-indexing task is that it is not suitable for very large databases (VLDBs) because it re-indexes all indexes of database, regardless of whether they are fragmented or not. Re-building all indexes can cause the following two issues:

• When you re-build an index, it clears the stats for that index from SQL Server cache. This means you will loose the statistics information for indexes that are not fragmented.
• Index maintenance can take longer to re-index the database indexes because you are effectively re-building indexes that do not need re-building.

Therefore, due to these problems, we need to create our own indexing strategy outside of the maintenance plans.

Checkout the part-2 of my three part article series on index-related dynamic management views and function here, in which I discussed about following index-related dynamic management function (DMF): sys.dm_db_index_physical_stats, which is useful to detect fragmentation in specific index, all indexes in a table or indexed view, or all indexes in databases, or all indexes in all databases. In this article, I also shared a stored procedure sp_rebuildindex that can be used to rebuild indexes based on fragmentation level.

This article is published on SSWUG.org.