Monday, April 20, 2009

Index Fragmentation in SQL Server (Reorganizing/Rebuilding Indexes)

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

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.

Eirherway, 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


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.

I hope you found this article useful.

Until next time!

- Artemakis
[Ads]
Check out my latest eBook on SQL Server:
Tuning SQL Server - Ebook

Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

2 comments:

David Rainville said...

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

Artemakis Artemiou [MVP] said...

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