Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Fixing Index Fragmentation in SQL Server 2005 and SQL Server 2008


By:   |   Read Comments (10)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > Fragmentation and Index Maintenance

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem
In my last tip, Index Fragmentation Report in SQL Server 2005 & 2008, I discussed what fragmentation is, its different types, its performance impact and what are different methods available in SQL Server 2005 and 2008 to identify fragmentation levels. So now once you have identified the high fragmentation level in your database, which could be a bottleneck in your SQL Server performance, what is the next step of fixing this high fragmentation. In this tip, I am going to discuss the different methods and its feasibility to fix the identified high fragmentation levels.

Solution
Fragmented indexes can be de-fragmented in two ways depending on the level of fragmentation and size of the tables in terms of rows.

  • Reorganize Index - The reorganize operation doesn't take a lot of system resources and can be done while users are accessing the table that the index exists on, that's the reason it is an "online" operation. The reorganize process reorganizes the leaf nodes of the index physically to match it with the logical order, this physical order matching the logical order improves the performance of index scans. The indexes can be reorganized using ALTER INDEX...REORGANIZE command (more details on this command and its usage are given below). This command is the same as DBCC INDEXDEFRAG of previous versions (though it still available for backward compatibility), but there is one difference, it has some additional features like large objects compaction (LOB_COMPACTION) and it is an online operation. Also, reorganize does NOT update statistics.

Script : Index Reorganize

ALTER INDEX { index_name | ALL }
ON <object>
REORGANIZE 
[ PARTITION = partition_number ]
[ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
-- index_name | ALL -> name of index or all index on given object to be reorganized
-- <object> -> Name of the object on which the index(es) exist(s)
-- partition_number -> can be specified only if the index_name is a partitioned 
--index and specifies the partition which you need to reorganize
-- LOB_COMPACTION -> Specifies that all pages that contain large object (LOB) 
--data are compacted. The default is ON.
  • Rebuild Index - Rebuilding an index means dropping an existing index and creating a new one altogether. Any fragmentation that was in the older index is gone with the drop and in the new index the logical order matches the physical order. Because the existing index is removed and created afresh, users would not be able to access the table and performance may degrade while the index is being built. (If the index you are rebuilding is a cluster index, then your tables will be unavailable to the user during the rebuild process though if it is non-clustered index only a shared lock will be placed on the table so users will be able to access the table, but would not be allowed to do modifications on it). In other words, users will not be able to lock the table during the time the index rebuild is occurring. This could be a major performance bottleneck when rebuilding indexes. Though beginning with SQL Server 2005 you can rebuild your indexes in an online fashion, so that other users are able to access the table and index while the rebuild is being performed. Since you can access the indexes during the rebuild operation, you are not limited to only rebuilding indexes during off-peak hours, though there is some trade-off needed as discussed in the section below.

You might be wondering how SQL Server makes available the index while it is being rebuilt. Well, it uses Row versioning behind the scenes. When you create an index online, it does not drop the existing index and create a new one, but rather it keeps the original index (source) to be used by the users and creates an index (target) parallel to it. The target index resembles the source index which is now a write-only index. Any modifications are applied to the target as well as on the source index. But during the rebuild only the source index will be available for use by the users. Once the index rebuild operation is complete, the original (source) index will be dropped and the meta-data will be updated to make the newly created (target) available for read and write operations. Of course when you plan to rebuild your indexes online, you need to consider its trade-off of having almost double the storage size for indexes to maintain two indexes (source and target) simultaneously (also additional temporary disk might be required for sorting operations).

Indexes can be rebuilt using these two techniques:

  • CREATE INDEX Command - One way is to simply drop the index using a DROP INDEX statement followed by a CREATE INDEX statement. Though you can combine these two commands by using the DROP_EXISTING clause of CREATE INDEX command as given below in the script table. You can use the DROP_EXISTING clause to rebuild the index, add or drop columns, modify options, modify column sort order, or change the partition scheme or filegroup. DROP_EXISTING enhances performance when you re-create a clustered index, with either the same or different set of keys, on a table that also has non-clustered indexes. DROP_EXISTING replaces the execution of a DROP INDEX statement on the old clustered index followed by the execution of a CREATE INDEX statement for the new clustered index. The benefit it gives, the non-clustered indexes are rebuilt only once, and even then only if the index definition has changed. With this command you can rebuild the index online.

Script : Index Rebuild using CREATE INDEX Command

ALTER INDEX { index_name | ALL }
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
ON <object>
WITH (DROP_EXISTING = { ON | OFF });
-- index_name -> name of index on a given object to be rebuild
-- <object> -> Name of the object on which the index(es) exist(s)
-- DROP_EXISTING -> Specifies that the named, preexisting clustered, or nonclustered is 
--dropped and rebuilt. The default is OFF.
-- Note -> Along with DROP_EXISTING clause you can use several other clauses with 
--CREATE INDEX command, for more details refer to link provided below.
  • Using ALTER INDEX command - This statement replaces the DBCC DBREINDEX statement. The ALTER INDEX statement allows for the rebuilding of the clustered and non-clustered indexes on the table. The drawback with this statement is that you cannot change the index definition the way you can do with the CREATE INDEX command. Though with this command you can rebuild the index online. ALTER INDEX cannot be used to repartition an index or move it to a different filegroup. This statement cannot be used to modify the index definition at all, such as adding or deleting columns or changing the column order. Use CREATE INDEX with the DROP_EXISTING clause to perform these operations as stated above.

Script : Index Rebuild using ALTER INDEX Command

ALTER INDEX { index_name | ALL }
ON <object>
REBUILD WITH (
{
| FILLFACTOR = fillfactor 
| SORT_IN_TEMPDB = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| ONLINE = { ON | OFF } 
| MAXDOP = max_degree_of_parallelism
| DATA_COMPRESSION = { NONE | ROW | PAGE } 
[ ON PARTITIONS ( { <partition_number_expression> | <range> } 
[ , ...n ] ) ]
}
-- FILLFACTOR -> how full the leaf level of each index page would be during 
--index creation or alteration. 
-- SORT_IN_TEMPDB -> Specifies whether to store the sort results in tempdb. The default is OFF.
-- STATISTICS_NORECOMPUTE -> Specifies whether distribution statistics are recomputed. 
--The default is OFF which means Automatic statistics updating are enabled.
-- ONLINE -> Specifies whether underlying tables and associated indexes are available 
--for queries and data modification during the index operation. Only available in 
--SQL Server Enterprise edition, has default value OFF.
-- MAXDOP -> Overrides the max degree of parallelism configuration option for the duration 
--of the index operation. To learn more about MAXDOP option click here.
-- DATA_COMPRESSION -> Available only in SQL Server 2008, specifies the data compression 
--option for the specified index, partition number, or range of partitions.
--Note -> There are some more clauses which you can use with ALTER INDEX
--command, for more details refer to link provided below.

Notes

  • Rebuilding the Clustered Index (CI) does not rebuild the Non-Clustered Indexes (NCI) unless the keyword ALL is specified, you will have do it separately.
  • If you rebuild your indexes online, you must ensure that you have enough available disk space to hold the index that is being created along with the pre-existing index (source and target indexes simultaneously). After the rebuild operation, the old index will be dropped though. Also, rebuilding indexes online takes a significant amount more time and resources than just rebuilding the index. This is usually a considerable tradeoff since the table will remain available during the rebuild operation.
  • Also the underlying table cannot be altered, truncated, or dropped while an online index operation is in process.
  • For partitioned indexes built on a partition scheme, you can use either of these methods (Reorganize and Rebuild) on a complete index or on a single partition of an index.
  • In general, fragmentation on small indexes is often not controllable, in other words rebuilding or reorganizing small indexes often does not reduce fragmentation. That is because the pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight different objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index.
  • Index rebuilding can be either online or offline.
  • Index reorganizing is always executed online.
  • The create index operation can be minimally logged if the database recovery model is set to either bulk-logged or simple.
  • An index cannot be reorganized or rebuilt if the filegroup in which it resides is offline or set to read-only.


Example Script to Rebuild / Reorganize Fragmented Indexes

Let's see an example, the script provided below reorganizes and rebuilds the indexes if the fragmentation level is higher than the given threshold. You can define the threshold for reorganizing as well as for rebuilding and the script will work accordingly. Please note this script may take several minutes to several hours depending on the level of fragmentation and size of the tables in terms of rows. Kindly test it on your dev or test environment before executing on your production environment.

--Description : This script reorganizes and rebuilds the index if the fragmentation level is higher the given threshold
-- You can define the threshold for reorganize as well as for rebuild and script will work accordingly
-- INPUTS : @fillfactor - While rebuilding index what would be FILLFACTOR for new index
-- @FragmentationThresholdForReorganizeTableLowerLimit - Fragmentation Level lower threshold to check for reorganizing the table, if the fragmentation is higher than this level, it will be considered for reorganize
-- @@FragmentationThresholdForRebuildTableLowerLimit - Fragmentation Level lower threshold to check for rebuilding the table, if the fragmentation is higher than this level, it will be considered for rebuild
-- NOTES : PRINT statements are all queued up and don't show up until the entire script is printed. However, there is an alternative to PRINTing messages. 
-- You can raise an error that isn't really an error (code of 0) and you'll get the same effect--message will be printed immediately.
DECLARE @cmd NVARCHAR(1000
DECLARE @Table VARCHAR(255
DECLARE @SchemaName VARCHAR(255)
DECLARE @IndexName VARCHAR(255)
DECLARE @AvgFragmentationInPercent DECIMAL
DECLARE @fillfactor INT 
DECLARE 
@FragmentationThresholdForReorganizeTableLowerLimit VARCHAR(10)
DECLARE @FragmentationThresholdForRebuildTableLowerLimit VARCHAR(10)
DECLARE @Message VARCHAR(1000)

SET NOCOUNT ON

--You can specify your customized value for reorganize and rebuild indexes, the default values
--of 10 and 30 means index will be reorgnized if the fragmentation level is more than equal to 10 
--and less than 30, if the fragmentation level is more than equal to 30 then index will be rebuilt
SET @fillfactor 90 
SET @FragmentationThresholdForReorganizeTableLowerLimit '10.0' -- Percent
SET @FragmentationThresholdForRebuildTableLowerLimit '30.0' -- Percent

BEGIN TRY

-- ensure the temporary table does not exist
IF (SELECT OBJECT_ID('tempdb..#FramentedTableList')) IS NOT NULL
DROP TABLE #FramentedTableList;

SET @Message 'DATE : ' CONVERT(VARCHARGETDATE()) + ' - Retrieving indexes with high fragmentation from ' DB_NAME() + ' database.'
RAISERROR(@Message01WITH NOWAIT

SELECT OBJECT_NAME(IPS.OBJECT_IDAS [TableName]avg_fragmentation_in_percentSI.name [IndexName]
schema_name(ST.schema_idAS [SchemaName]AS IsProcessed INTO #FramentedTableList
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) IPS
JOIN sys.tables ST WITH (nolockON IPS.OBJECT_ID ST.OBJECT_ID
JOIN sys.indexes SI WITH (nolockON IPS.OBJECT_ID SI.OBJECT_ID AND IPS.index_id SI.index_id
WHERE ST.is_ms_shipped AND SI.name IS NOT NULL
AND 
avg_fragmentation_in_percent >= CONVERT(DECIMAL@FragmentationThresholdForReorganizeTableLowerLimit
ORDER BY avg_fragmentation_in_percent DESC

SET 
@Message 'DATE : ' CONVERT(VARCHARGETDATE()) + ' - Retrieved indexes with high fragmentation from ' DB_NAME() + ' database.'

RAISERROR(@Message01WITH NOWAIT
RAISERROR(''01WITH NOWAIT

WHILE EXISTS ( SELECT FROM #FramentedTableList WHERE IsProcessed )
BEGIN

  SELECT TOP 
@Table TableName@AvgFragmentationInPercent avg_fragmentation_in_percent
  
@SchemaName SchemaName@IndexName IndexName
  
FROM #FramentedTableList
  
WHERE IsProcessed 0

  
--Reorganizing the index
  
IF((@AvgFragmentationInPercent >= @FragmentationThresholdForReorganizeTableLowerLimit) AND (@AvgFragmentationInPercent @FragmentationThresholdForRebuildTableLowerLimit))
  
BEGIN
    SET 
@Message 'DATE : ' CONVERT(VARCHARGETDATE()) + ' - Reorganizing Index for [' @Table '] which has avg_fragmentation_in_percent = ' CONVERT(VARCHAR@AvgFragmentationInPercent) + '.'
    
RAISERROR(@Message01WITH NOWAIT
    
SET @cmd 'ALTER INDEX ' @IndexName ' ON [' RTRIM(LTRIM(@SchemaName)) + '].[' RTRIM(LTRIM(@Table)) + '] REORGANIZE' 
    
EXEC (@cmd)
    
--PRINT @cmd 
    
SET @Message 'DATE : ' CONVERT(VARCHARGETDATE()) + ' - Reorganize Index completed successfully for [' @Table '].' 
    
RAISERROR(@Message01WITH NOWAIT
    
RAISERROR(''01WITH NOWAIT
  
END
  
--Rebuilding the index
  
ELSE IF (@AvgFragmentationInPercent >= @FragmentationThresholdForRebuildTableLowerLimit )
  
BEGIN
    SET 
@Message 'DATE : ' CONVERT(VARCHARGETDATE()) + ' - Rebuilding Index for [' @Table '] which has avg_fragmentation_in_percent = ' CONVERT(VARCHAR@AvgFragmentationInPercent) + '.'
    
RAISERROR(@Message01WITH NOWAIT
    
SET @cmd 'ALTER INDEX ' @IndexName ' ON [' RTRIM(LTRIM(@SchemaName)) + '].[' RTRIM(LTRIM(@Table)) + '] REBUILD WITH (FILLFACTOR = ' CONVERT(VARCHAR(3),@fillfactor) + ', STATISTICS_NORECOMPUTE = OFF)' 
    
EXEC (@cmd)
    
--PRINT @cmd
    
SET @Message 'DATE : ' CONVERT(VARCHARGETDATE()) + ' - Rebuild Index completed successfully for [' @Table '].'
    
RAISERROR(@Message01WITH NOWAIT
    
RAISERROR(''01WITH NOWAIT
  
END

  UPDATE 
#FramentedTableList
  
SET IsProcessed 
  
WHERE TableName @Table
  
AND IndexName @IndexName
END

DROP TABLE 
#FramentedTableList 

END TRY

BEGIN CATCH
  
PRINT 'DATE : ' CONVERT(VARCHARGETDATE()) + ' There is some run time exception.'
  
PRINT 'ERROR CODE : ' CONVERT(VARCHARERROR_NUMBER()) 
  
PRINT 'ERROR MESSAGE : ' ERROR_MESSAGE()
END CATCH
 

Next Steps



Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, August 15, 2013 - 2:18:58 PM - rick Back To Top

i've tried to chain this to run against all DBs on an instance using sp_msforeachdb but there are a couple errors and a lot of 's to escape.  can someone post code to auto-run this on all dbs on an instance?  i know the issue with expanding logs, so adequate testing would have to be done prior to running the updated code.


Monday, July 01, 2013 - 1:06:36 PM - Ed Lyons Back To Top

Get ERROR: 

Msg 102, Level 15, State 1, Line 38

Incorrect syntax near '('.


Wednesday, July 27, 2011 - 11:11:44 PM - Kyle Back To Top

You missed one very important difference between Alter Index Rebuild and Create index. The former can be chained up in one atomic transaction, where as the latter can't.


Monday, September 27, 2010 - 10:53:28 PM - mIke rose Back To Top
I ran into an issue where the script would fail for indexes that contained ~ in the name to resolve it I updated lines 63 and 75 as follows.   



SET @cmd = 'ALTER INDEX [' + @IndexName + '] ON [' + RTRIM(LTRIM(@SchemaName)) + '].[' + RTRIM(LTRIM(@Table)) + '] REORGANIZE'  

 



SET

 

@cmd = 'ALTER INDEX [' + @IndexName + '] ON [' + RTRIM(LTRIM(@SchemaName)) + '].[' + RTRIM(LTRIM(@Table)) + '] REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ', STATISTICS_NORECOMPUTE = OFF)'




Tuesday, September 21, 2010 - 7:15:05 AM - Arshad Back To Top
Trying Copy and Paste one more time. I just tried and it p-a-r-s-e-d successfully.


Tuesday, September 21, 2010 - 7:13:48 AM - Arshad Back To Top
Trying Copy and Paste one more time. I just tried and it p***d successfully.


Tuesday, September 21, 2010 - 6:30:56 AM - Karanja Back To Top
Msg 102, Level 15, State 1, Line 38
Incorrect syntax near '('.

 

38. FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) IPS

 

Copied the script as is???


Monday, February 15, 2010 - 8:38:34 AM - lechkun Back To Top

Yes, I see.  Looking at the indexes in question, I see that they are less that 4 pages in size. 

Thanks for getting back with, it is much appreciated!!


Saturday, February 13, 2010 - 2:20:53 AM - arshad0384 Back To Top

The script allows you to specify your customized value for reorganize and rebuild indexes, the default values of 10 and 30 means index will be reorgnized if the fragmentation level is more than equal to 10 and less than 30, if the fragmentation level is more than equal to 30 then index will be rebuilt.

SET @FragmentationThresholdForReorganizeTableLowerLimit = '10.0' -- Percent
SET @FragmentationThresholdForRebuildTableLowerLimit = '30.0' -- Percent

Coming to your doubt, In general, fragmentation on small indexes is often not controllable, in other words rebuilding or reorganizing small indexes often does not reduce fragmentation. That is because the pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight different objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index. Please refer to the Notes section of tip for more such notes.

Hope it helps.


Friday, February 12, 2010 - 10:51:03 AM - lechkun Back To Top

It seems that when I run the script it identifies the indexes and re-orgs them.  However, when I immediately re-run the script, it identifies the exact same indexes with the exact same fragmentation.  I figure that the statistics aren't set with the reorg, so I put in an update statistics command after the reorg to update the index statistics, but there's no difference. (btw: the DB option is set for auto update statistics).

This is a very useful script; however, I not certain if it is really working.

 


Learn more about SQL Server tools