How to rebuild all the indexes of a database in SQL Server

Index Fragmentation in SQL Server and How to Manage it

by

In this article, we will be discussing about index fragmentation in SQL Server and how you can manage it.

SQL Server automatically maintains indexes whenever insert, delete, or update operations are performed on the underlying data.

 

1. The Two Types of Index Fragmentation in SQL Server

Though, in the cases where the underlying data contain very large volumes of information, the information in the indexes become scattered over time. This is known as Index Fragmentation. Index fragmentation can degrade performance so the indexes must be properly maintained.

There are two types of Index Fragmentation: (i) External, and (ii) Internal.

External fragmentation is when the logical order of the pages in an index does not match the physical order.

Internal fragmentation is when the index pages are not filled to the current fill factor level.

Either way, when you are dealing with Index fragmentation (internal, external, or both) it means that you will most probably also face performance degradation on the database which uses those indexes.

To this end, you need to frequently check the fragmentation percentage of the indexes and take the necessary actions whenever is needed. These actions include rebuilding or reorganizing
the fragmented indexes.

 

2. Collecting Index Fragmentation Statistics

So let’s take one thing at a time. First of all you need to find the fragmentation percentage for the indexes in a database or table.

There are two ways of doing that: (i) by using the ‘DBCC SHOWCONTIG‘ command, and (ii) by using the ‘sys.dm_db_index_physical_stats‘ Dynamic Management View (DMV). The former is supported in all current versions of SQL Server, while the latter is supported in SQL Server 2005 and later.

The DBCC SHOWCONTIG command provides important information about indexes. To this end you can get information like: Object Name, Object ID, Index Name, Number of Pages, Extends, Logical Fragmentation, Extended Fragmentation, etc.

Though, the sys.dm_db_index_physical_stats DMV provides even more information and is more robust. To this end, you can get index statistics like: database_id, object_id, index_id, partition_number, index_type_desc, index_depth, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count, avg_page_space_used_in_percent, record_count, compressed_page_count, etc.

Actually, the sys.dm_db_index_physical_stats DMV replaces DBCC SHOWCONTIG.

The above DMV takes five input parameter specifying the following: the database id, object id, index id, partition mode (if you want to get information for a specific partition of an object), and the mode which specifies the scan level during the statistics collection process.

 

2.1 DBCC SHOWCONTIG Usage (SQL Server 2000)

Syntax Examples:

-- OPTION 1 - Getting Index Fragmentation Information for a single table
USE [DATABASE_NAME]
DBCC SHOWCONTIG('TABLE_NAME') WITH ALL_LEVELS, TABLERESULTS, NO_INFOMSGS

-- OPTION 2 - Getting Index Fragmentation Information for an entire database
USE [DATABASE_NAME]
DBCC SHOWCONTIG WITH ALL_LEVELS, TABLERESULTS, NO_INFOMSGS

Index Fragmentation Percentage in ‘DBCC SHOWCONTIG’ results:

The important information here is the LogicalFragmentation column. As the column’s name explains, it displays the fragmentation percentage for the specific index.

 

2.2 sys.dm_db_index_physical_stats Usage (SQL Server 2005 or later)

Syntax Examples

-- OPTION 1 - Getting Index Fragmentation Information for a specific index
SELECT *
FROM sys.Dm_db_index_physical_stats(Db_id('DATABASE_NAME'),Object_id('DATABASE.SCHEMA.TABLE_NAME'),INDEX_ID,NULL,NULL)

-- OPTION 2 - Getting Index Fragmentation Information for a single table
SELECT *
FROM sys.Dm_db_index_physical_stats(Db_id('DATABASE_NAME'),Object_id('DATABASE.SCHEMA.TABLE_NAME'),NULL,NULL,NULL)

-- OPTION 3 - Getting Index Fragmentation Information for an entire database
SELECT *
FROM sys.Dm_db_index_physical_stats(Db_id('DATABASE_NAME'),NULL,NULL,NULL,NULL)

Index Fragmentation Percentage in ‘sys.dm_db_index_physical_stats’ results:

The important information here is the avg_fragmentation_in_percent column. Just like the LogicalFragmentation column of DBCC SHOWCONTIG, it displays the fragmentation percentage for the specific index.

 

3. Evaluating and utilizing Index Fragmentation Statistics

So, with the above two methods, we saw how we can get various index statistics and the most important of them (in the context of this post); the Logical Index Fragmentation.

The question now is what do we do with this information, how can it help for deciding how to defragment the necessary indexes and restore the degraded performance of the database?

The question actually, is when to decide that you need to reorganize indexes, and when to rebuild them.

As the following MSDN Library article suggests whenever the avg_fragmentation_in_percent is between 5-30% then you need to reorganize the index. When the avg_fragmentation_in_percent is greater than 30%, then you need to rebuild the index.

Pseudocode
------------
Get index fragmentation statistics for database/table/index

IF Logical Fragmentation >=5 AND Logical Fragmentation <= 30 THEN
REORGANIZE INDEX(es)

ELSE IF Logical Fragmentation >30 THEN
REBUILD INDEX(es)

END IF

 

3.1 Rebuilding an Index

Rebuilding an index is an efficient way to reduce fragmentation. It automatically drops and re-creates the indexthus removing fragmentation. It also reclaims disk space and reorders the index rows in contigous pages.

This process can run Online or Offline. Note that Online index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions.

Syntax examples for rebuilding an index in SQL Server 2005 or later:

-- Rebuild a specific index with using parameters
USE [DATABASE_NAME];
GO
ALTER INDEX [INDEX_NAME] ON [SCHEMA.TABLE]
REBUILD WITH (FILLFACTOR=[FILL_FACTOR_VALUE_BETWEEN_0_100], ONLINE=[ON|OFF]);
GO

-- Rebuild all indexes in a table with using parameters
USE [DATABASE_NAME];
GO
ALTER INDEX ALL ON [SCHEMA.TABLE]
REBUILD WITH (FILLFACTOR=[FILL_FACTOR_VALUE_BETWEEN_0_100], ONLINE=[ON|OFF]);
GO

* If you do not want to use parameters, then just remove the ‘WITH …’ part.

* In SQL Server 2000 you cannot use the ALTER INDEX but you can use the DBCC DBREINDEX statement instead.

 

Syntax examples for rebuilding an index in SQL Server 2000:

-- Rebuild a specific index in a given table
USE [DATABASE_NAME];
DBCC DBREINDEX ([TABLE_NAME], '[INDEX_NAME]',[FILL_FACTOR_VALUE_BETWEEN_0_100])
GO

-- Rebuild all the indexes in a given table
USE [DATABASE_NAME];
DBCC DBREINDEX ([TABLE_NAME], '', [FILL_FACTOR_VALUE_BETWEEN_0_100])
GO

 

3.2 Reorganizing an Index

Reorganizing an index physically reorders the leaf-level pages to match the logical, left to right, order of the leaf nodes. It also compacts the index pages based on the existing fill factor value.

Syntax examples for reorganizing an index in SQL Server 2005 or later:

-- Reorganize a specific index in a given table
USE [DATABASE_NAME];
GO
ALTER INDEX [INDEX_NAME] ON [SCHEMA.TABLE_NAME]
REORGANIZE ;
GO

-- Reorganize all indexes in a given table
USE [DATABASE_NAME];
GO
ALTER INDEX ALL ON [SCHEMA.TABLE_NAME]
REORGANIZE ;
GO

* In SQL Server 2000 you cannot use the ALTER INDEX but you can use the DBCC INDEXDEFRAG statement instead.

 

Syntax example for reorganizing an index in SQL Server 2000:

DBCC INDEXDEFRAG ([DATABASE_NAME], [TABLE_NAME], [INDEX_NAME]);
GO

 

Strengthen your SQL Server Administration Skills – Enroll to our Online Course!

Check our online course on Udemy titled “Essential SQL Server Administration Tips”
(special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
Lifetime Access / Live Demos / Downloadable Resources and more!

Learn More

4. Remarks and Conclusions

Even though DBCC SHOWCONTIG, DBCC DBREINDEX, and DBCC INDEXDEFRAG are still supported in the latest versions of SQL Server (2005 and 2008), they will be removed in a future version of SQL Server as they are replaced by sys.dm_db_index_physical_stats and ALTER INDEX respectively. To this end, it is advised to avoid using those features in new development work. Also you will need to have this in mind with respect to any database applications you may maintain.

The reorganization of indexes always runs online, while the rebuild provides you with the option to run online or offline. Though, note that in order to be able to rebuild indexes online you must use SQL Server Enterprise, Developer, and Evaluation editions.

Index defragmentation is very important, especially in large databases where insert, delete or update operations are frequently performed. Usually DBAs include automated defragmentation processes in SQL Server maintenance plans in order to prevent performance degradation due to index fragmentation.

We hope you found this article useful!

 

 

Featured Online Courses:

 

Read Also:

 

Check our other related SQL Server Performance articles.

Subscribe to our newsletter and stay up to date!

Check out our latest software releases!

Check out Artemakis’s eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Views: 5,819

2 thoughts on “Index Fragmentation in SQL Server and How to Manage it”

  1. Really useful thank you! The only problem I find is that when I run the following T-SQL :

    USE AdventureWorks;
    GO
    ALTER INDEX ALL ON Purchasing.Vendor
    REORGANIZE;
    GO

    I receive a “Query executed succesfully” result.. but the problem is that the fragmentation level stays the same.. (which were pretty high before executing the query)

    Is there anything I need to do before executing the query?

    thanks for helping

  2. Hi David,

    Thank you for your comment.
    The best approach for fully defragmenting the indexes on a table/database, is using the REBUILD option as it drops and recreates the indexes.

    In your example, I would use the following code:

    USE AdventureWorks;
    GO
    ALTER INDEX ALL ON Purchasing.Vendor
    REBUILD WITH (FILLFACTOR = 80, ONLINE = ON );
    GO

    Note that when you use the REBUILD option, you are also allowed to specify the fill factor, which also indirectly determines how much of the index(es) space can be used for rebuilding the indexes and leaving the rest of the space available for new index data.

    If you experiment with different fill factor values, you will see that you will get different fragmentation levels depending on the value of the fill factor you specify each time during the index rebuild process.

    Hope this helps!

    Cheers!
    Artemakis

Comments are closed.