Why, when and how to rebuild and reorganize SQL Server indexes

The purpose of the SQL Server index is pretty much the same as in its distant relative – the book index – it allows you to get to the information quickly, but instead of navigating through the book, it indexes a SQL Server database.

SQL Server indexes are created on a column level in both tables and views. Its aim is to provide a “quick to locate” data based on the values within indexed columns. If an index is created on the primary key, whenever a search for a row of data based on one of the primary key values is performed, the SQL Server will locate searched value in the index, and then use that index to locate the entire row of data. This means that the SQL Server does not have to perform a full table scan when searching for a particular row, which is much more performance intensive task –consuming more time and using more SQL Server resources.

Relational indexes can be created even before there is data in the specified table, or even on tables and views in another database.

CREATE INDEX MyIndex ON MyTable (Column1);

More on CREATE INDEX Transact-SQL can be found on the MSDN.

After indexes are created, they will undergo automatic maintenance by the SQL Server Database Engine whenever insert, update or delete operations are executed on the underlying data.

Even so, these automatic modifications will continuously scatter the information in the index throughout the database – fragmenting the index over time. The result – indexes now have pages where logical ordering (based on the key-value) differs from the physical ordering inside the data file. This means that there is a high percentage of free space on the index pages and that SQL Server has to read a higher number of pages when scanning each index. Also, ordering of pages that belong to the same index gets scrambled and this adds more work to the SQL Server when reading an index – especially in IO terms.

The Index fragmentation impact on the SQL Server can range from decreased efficiency of queries – for servers with low-performance impact, all the way to the point where SQL Server completely stops using indexes and resorts to the last-straw solution – full table scans for each and every query. As mentioned before, full table scans will drastically impact SQL Server performance and this is the final alarm to remedy index fragmentation on the SQL Server.

The solution to fragmented indexes is to rebuild or reorganize indexes.

But, before considering maintenance of indexes, it is important to answer two main questions:

1. What is the degree of fragmentation?

2. What is the appropriate action? Reorganize or rebuild?

Detecting fragmentation

Generally, in order to solve any problem, it is essential to first and foremost locate it, and isolate the affected area before applying the correct remedy.

Fragmentation can be easily detected by running the system function sys.dm_db_index_physical_stats which returns the size and the fragmentation information for the data and indexes of tables or views in SQL Server. It can be run only against a specific index in the table or view, all indexes in the specific table or view, or vs. all indexes in all databases:

The results returned after running the procedures include following information:

• avg_fragmentation_in_percent – average percent of incorrect pages in the index
• fragment_count – number of fragments in index
• avg_fragment_size_in_pages – average number of pages in one fragment in an index

Analyzing detection results

After the fragmentation has been detected, the next step is to determine its impact on the SQL Server and if any course of action needs to be taken.

There is no exact information on the minimal amount of fragmentation that affects the SQL Server in a specific way to cause performance congestion, especially since the SQL Server environments greatly vary from one system to another.

However, there is a generally accepted solution based on the percent of fragmentation (avg_fragmentation_in_percent column from the previously described sys.dm_db_index_physical_stats function)

• Fragmentation is less than 10% – no de-fragmentation is required. It is generally accepted that in majority of environments index fragmentation less than 10% in negligible and its performance impact on the SQL Server is minimal.
• Fragmentation is between 10-30% – it is suggested to perform index reorganization
• Fragmentation is higher than 30% – it is suggested to perform index rebuild

Here is the reasoning behind the thresholds above which will help you to determine if you should perform index rebuild or index reorganization:

Index reorganization is a process where the SQL Server goes through the existing index and cleans it up. Index rebuild is a heavy-duty process where an index is deleted and then recreated from scratch with an entirely new structure, free from all piled up fragments and empty-space pages.

While index reorganization is a pure cleanup operation that leaves the system state as it is without locking-out affected tables and views, the rebuild process locks the affected table for the whole rebuild period, which may result in long down-times that could not be acceptable in some environments.

With this in mind, it is clear that the index rebuild is a process with a ‘stronger’ solution, but it comes with a price – possible long locks on affected indexed tables.

On the other side, index reorganization is a ‘lightweight’ process that will solve the fragmentation in a less effective way – since cleaned index will always be second to the new one fully made from scratch. But reorganizing index is much better from the efficiency standpoint since it does not lock the affected indexed table during the course of operation.

Servers with regular maintenance periods (e.g. regular maintenance over weekend) should almost always opt for the index rebuild, regardless of the fragmentation percent, since these environments will hardly be affected by the table lock-outs imposed by index rebuilds due to regular and long maintenance periods.

How to reorganize and rebuild index:

Using SQL Server Management Studio:

1. In the Object Explorer pane navigate to and expand the SQL Server, and then the Databases node
2. Expand the specific database with fragmented index
3. Expand the Tables node, and the table with fragmented index
4. Expand the specific table
5. Expand the Indexes node
6. Right-click on the fragmented index and select Rebuild or Reorganize option in the context menu (depending on the desired action):

   

7. Click the OK button and wait for the process to complete

Reorganize indexes in a table using Transact-SQL

Provide appropriate database and table details and execute following code in SQL Server Management Studio to reorganize all indexes on a specific table:

USE MyDatabase;
GO

ALTER INDEX ALL ON MyTable REORGANIZE;
GO

Rebuild indexes in a table using Transact-SQL

Provide appropriate database and table details and execute following code in SQL Server Management Studio to rebuild all indexes on a specific table:

USE MyDatabase;
GO

ALTER INDEX ALL ON MyTable REBUILD;
GO

See more

To fix SQL index fragmentation, consider ApexSQL Defrag – a SQL Server index monitoring, analysis, maintenance, and defragmentation tool.