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)
-- 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)
-- 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.
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
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!
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:
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- The Philosophy and Fundamentals of Computer Programming
- .NET Programming for Beginners – Windows Forms with C#
- Introduction to Data Science and SQL Server Machine Learning
- Introduction to Azure SQL Database for Beginners
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- How to Import and Export Data in SQL Server Databases
- Learn How to Install and Start Using SQL Server in 30 Mins
- A Guide on How to Start and Monetize a Successful Blog
- The Importance of Database Indexes
- Handling Disk Space Issues During Heavy Index Rebuild Operations
- SQL Server Index Rebuild Scripts
- Where are temporary tables stored in SQL Server?
- SQL Server 2016: TempDB Enhancements
- tempdb growth
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:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018) and a Udemy Instructor. He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.