In this article, we will learn how to identify and resolve Index Fragmentation in SQL Server. Index fragmentation identification and index maintenance are important parts of the database maintenance task. Microsoft SQL Server keeps updating the index statistics with the Insert, Update or Delete activity over the table. The index fragmentation is the index performance value in percentage, which can be fetched by SQL Server DMV. According to the index performance value, users can take the indexes in maintenance by revising the fragmentation percentage with the help of Rebuild or Reorganize operation.
Why the Index Fragmentation percentage varies?
Index Fragmentation percentage varies when the logical page orders don’t coordinate with the physical page order in the page allocation of an index. With the data modification in the table, information can be resized on the data page. The page was top full before the update operation on the table. However, free space could be found on the data page with an update operation on the table. Users can observe the disturbing page order with the massive delete operation on the table. Along with the update and delete operations, the data page won’t be a top-full or empty page. Therefore, non-utilized free space raises the order mismatch between logical page and physical page with increasing the fragmentation, and that can cause worst query performance and consumes more server resources as well.
More essential to spell out that the index fragmentation affects query performance only with the page scan. In such cases, it raises the chances of the poor performance of another SQL requests as well, because query with the high fragmented index over the table takes more time to execute and consumes more resources such as Cache, CPU, and IO. Therefore, the rest of the SQL requests find it difficult to finish the operation with the inconsistent server resources. Even blocking can occur by the Update and Delete operation because the optimizer won’t gather the information of the index fragmentation while generating the execution plan for the query.
There can be a number of indexes created on a single table with the combination of various columns, and each index can have a different fragmentation percentage. Now, before making it appropriate or taking an index in maintenance, users have to find that threshold value from the database. The below T-SQL statement is an efficient way to find it with object details.
Find Index Fragmentation status using the T-SQL statement
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT S.name as 'Schema', T.name as 'Table', I.name as 'Index', DDIPS.avg_fragmentation_in_percent, DDIPS.page_count FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS INNER JOIN sys.tables T on T.object_id = DDIPS.object_id INNER JOIN sys.schemas S on T.schema_id = S.schema_id INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id AND DDIPS.index_id = I.index_id WHERE DDIPS.database_id = DB_ID() and I.name is not null AND DDIPS.avg_fragmentation_in_percent > 0 ORDER BY DDIPS.avg_fragmentation_in_percent desc |
Here, we can see that the maximum average fragmentation percentage is noticeable as 99%, which must be engaged with an action to reduce the fragmentation with the choices of either REBUILD or REORGANIZE. REBUILD or REORGANIZE is the index maintenance command that can be executed with the ALTER INDEX statement. Users can perform this command using SSMS as well.
Rebuild and Reorganize Index using SQL Server Management Studio (SSMS)
Find and expand the table in Object Explorer >> Open Indexes >> Right-click on the target index >> Rebuild or Reorganize.
As visible in the above image, REBUILD and REORGANIZE are the two available choices to play out the trim operation over the page. Ideally, this operation ought to be done in the off-peak time to avoid its impact on other transactions and users. Microsoft SQL Server Enterprise Edition supports index online and offline features with index REBUILD.
REBUILD INDEX
INDEX REBUILD always drops the index and reproduce it with new index pages. This activity can be run in parallel using an online option (Enterprise Edition) with the ALTER INDEX command, which does not affect the running requests and tasks of a similar table.
REBUILD Index can be set online or offline using the below SQL commands:
|
1 2 3 4 5 |
--Basic Rebuild Command ALTER INDEX Index_Name ON Table_Name REBUILD --REBUILD Index with ONLINE OPTION ALTER INDEX Index_Name ON Table_Name REBUILD WITH(ONLINE=ON) | WITH(ONLINE=ON) |
If a user performs the REBUILD INDEX offline, then the object resource (Table) of the index won’t be accessible till the end of REBUILD process completion. It affects numerous other transactions as well, which are associated with this object. Rebuild index operation recreates the index. Therefore, it generates new statistics and appends the log records of the index in the database transaction log file too.
For example, before rebuilding the index, let’s take the current allotment of pages for the index of the AdventureWorks database, Sales.OrderTracking table and IX_OrderTracking_CarrierTrackingNumber named index.
|
1 2 3 4 5 |
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_id, previous_page_page_id, next_page_page_id FROM sys.dm_db_database_page_allocations(DB_ID('AdventureWorks'), OBJECT_ID('Sales.OrderTracking'),NULL, NULL, 'DETAILED') IX INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id WHERE si.name = 'IX_OrderTracking_CarrierTrackingNumber' ORDER BY allocated_page_page_id |
Here, 1961 pages exist in the database file for this index, and the first 5 pages are the 861, 862, 1627, 1628, and 1904 in order of the page number. Now, let’s rebuild the index using SSMS.
Index REBUILD operation is completed successfully and take page allocation references for the same index with the help of the same T-SQL query again.
|
1 2 3 4 5 6 |
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_id, previous_page_page_id, next_page_page_id FROM sys.dm_db_database_page_allocations(DB_ID('AdventureWorks'), OBJECT_ID('Sales.OrderTracking'),NULL, NULL, 'DETAILED') IX INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id WHERE si.name = 'IX_OrderTracking_CarrierTrackingNumber' ORDER BY allocated_page_page_id |
After rebuilding the index, the refreshed page count is 1457, which was 1961 before. If you check the first 5 allocated pages of the same index, it has been changed with the new page references. It presumes that the index is dropped and made once again. We should check the refreshed fragmentation percentage for the same index, and as can be seen below, it’s 0.1% now.
REBUILD clustered index over the table affects other indexes of the table as well because the REBUILD clustered index rebuilds the non-clustered index of the table as well. Perform rebuild operation on all indexes of the table or database together; a user can use DBCC DBREINDEX() command.
|
1 |
DBCC DBREINDEX ('DatabaseName', 'TableName'); |
REORGANIZED INDEX
The REORGANIZE INDEX command reorders the index page by expelling the free or unused space on the page. Ideally, index pages are reordered physically in the data file. REORGANIZE does not drop and create the index but simply restructure the information on the page. REORGANIZE does not have any offline choice, and REORGANIZE does not affect the statistics compared to the REBUILD option. REORGANIZE performs online always.
For example, before performing the REORGANIZE over the index, let’s take the fragmentation reading for the database ‘AdventureWorks’, table ‘Sales.OrderTracking’ and Index named ‘IX_OrderTracking_SalesOrderID’.
Here, the index fragmentation percentage is 98.39 before REORGANIZE. The below list in the image is the allocation pages to the index.
|
1 2 3 4 5 6 |
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_id, previous_page_page_id, next_page_page_id FROM sys.dm_db_database_page_allocations(DB_ID('AdventureWorks'), OBJECT_ID('Sales.OrderTracking'),NULL, NULL, 'DETAILED') IX INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id WHERE si.name = 'IX_OrderTracking_CarrierTrackingNumber' ORDER BY allocated_page_page_id |
Here, a total of 459 pages are listed in the above image, and the first five pages are 1065, 1068, 1069, 1944, and 1945. Now, let’s perform the REORGANIZE command on the index using the below T-SQL statement and look at the page allocation again.
|
1 |
ALTER INDEX IX_OrderTracking_SalesOrderID ON Sales.OrderTracking REORGANIZE |
Here, the total page count is decreased to 331, which was 459 before. Furthermore, we do not see new pages in the first five pages list, which implies data is just restructured – not refilled again. Even you could see new pages as well, it happens in the situation when the big index is heavily fragmented, and reshuffling over the data use a new page.
To perform the REORGANIZE index operation on all indexes of the table or database together, the user can use the DBCC INDEXDEFRAG() command:
|
1 |
DBCC INDEXDEFRAG('DatabaseName', 'TableName'); |
As seen, there is a substantial difference between the Index REBUILD and REORGANIZE. Here the users have a choice to choose one of the alternatives as per the index fragmentation percentage. We can understand that there are no documented standards; however, the database administrator follows the standard equation as per the requirement of the Index size and type of information.
Usual determination of the use of the equation :
- When the Fragmentation percentage is between 15-30: REORGANIZE
- When the Fragmentation is greater than 30: REBUILD
REBUILD option is more useful with the ONLINE option when the database is not available to take index maintenance in off-peak hours.
Conclusion
Index Fragmentation is an internal fragmentation in the data file. Core parameters of quick performance of your database are the Database Architecture, Database Design, and Query writing. A Good index design with maintenance always boosts the query performance in the database engine.
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server - July 17, 2020
- How to automate Table Partitioning in SQL Server - July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 - July 6, 2020