By: Artemakis Artemiou | Comments (34) | Related: > Fragmentation and Index Maintenance
Problem
Indexes are one of the main database objects in SQL Server that massively contribute towards performance. By using the proper indexes, you can avoid fully scanning millions of records in tables in order to find what you are looking for. Instead of scanning the tables, you can traverse the index tree (index seek operation) and find what you are looking for much faster.
Even though indexes are very handy and necessary in performant database designs, they need maintenance. One of the reasons for that is fragmentation. The SQL Server Database Engine automatically maintains indexes whenever insert, update or delete modifications take place. However, over time and especially when heavy data modifications take place, index data becomes scattered in the database and thus fragmented. This fragmentation affects the performance of the index seek process, because these indexes do not have the exact logical ordering with the physical ordering inside the database's data file(s).
One way to handle this issue is by rebuilding fragmented indexes. If you do this using a Maintenance Plan, you will rebuild every single index in the database instead of rebuilding only the fragmented indexes (this has changed in SQL Server 2016). This tip provides a fully parameterized T-SQL script which identifies only the fragmented indexes in a database or in all databases in a SQL Server instance, and either generates a report with the defragmentation commands for review and controlled execution, or directly reorganizes or rebuilds the fragmented indexes based on Microsoft's recommendations. The script also supports AlwaysOn AG-enabled SQL Server instances.
Solution
Before presenting the script, let's first discuss a bit about the process of rebuilding indexes. First of all, never rebuild indexes that do not need to be rebuilt. As per Microsoft's recommendation, when an index has an average fragmentation percentage (use sys.dm_db_index_physical_stats to get these stats) larger than 5% and smaller or equal to 30% you do not need to rebuild the index. In this case, you just need to reorganize it. When the index has an average fragmentation percentage over 30%, then yes, you need to rebuild it. In more modern SQL Server setups, like for example AlwaysOn Availability Groups, you must make additional checks in your index maintenance scripts. For example you must check if a database is the primary replica or not. If it is the primary replica then you can proceed with the index maintenance.
The T-SQL Script presented in this tip handles all the above. You can run it on clustered SQL Server instances, AlwaysOn Availability Group enabled instances, etc. It has built-in logic that based on Microsoft's recommendations dynamically generates reorganization or rebuild statements only for the fragmented indexes.
SQL Server Index Rebuild and Reorganize Script
The script uses the following parameters:
@reportOnly (required)
Values:
- 0: The script will reorganize or rebuild the fragmented indexes.
- 1: The script will just output the index reorganization or rebuild commands without running them.
@databaseToCheck (optional)
Values:
- NULL: It will scan all databases with compatibility level SQL Server 2005 (90) or later for fragmented indexes.
- 'DatabaseName': It will scan only the given database for fragmented indexes.
@fragmentationThreshold (required)
Description: It maintains only the indexes that have average fragmentation percentage equal or higher from the given value.
Value Range: 5-100
@indexFillFactor (required)
Description: The percentage of the data page to be filled up with index data.
Recommended Value Range: 90-100
@indexStatisticsScanningMode (required)
Description: The scanning mode for index statistics
Available Values: 'DEFAULT', NULL, 'LIMITED', 'SAMPLED', or 'DETAILED'.
Recommended Value: 'SAMPLED'
@sortInTempdb (required)
Values:
- 'ON': Sorts intermediate index results in TempDB.
- 'OFF': Sorts intermediate index results in user database's log file.
@verboseMode (optional)
Values:
0: It does not output additional information on the index reorganization/rebuild process.
1: It outputs additional information on the index reorganization/rebuild process.
Also, please note the following conditions:
- You must be a SysAdmin in order to execute the script.
- The script supports only SQL Server 2005 or later.
- If you execute this script in a SQL Server 2005 instance or later,
any databases with compatibility level 2000 (80) or earlier will be
automatically excluded from the index reorganization/rebuild process.
The script is provided below:
---- -- Script that reorganizes or rebuilds all indexes having an average fragmentation -- percentage above a given threshold. It also works in the case -- where Availability Groups are enabled as it determines if the -- relevant databases are the primary replicas. -- -- This script supports only SQL Server 2005 or later. -- Also, if you execute this script in a SQL Server 2005 instance -- or later, any databases with compatibility level 2000 (80) or earlier -- will be automatically excluded from the index reorganization/rebuild process. ---- --Initial check - You must be SysAdmin DECLARE @isSysAdmin INT SET @isSysAdmin=(SELECT IS_SRVROLEMEMBER ('sysadmin')); --Initial check - You must be using SQL Server 2005 or later DECLARE @SQLServerVersion INT SET @SQLServerVersion=(SELECT CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)))-1) AS INT)); IF @isSysAdmin=1 AND @SQLServerVersion >= 9 BEGIN -- -- Variable/parameters Declaration -- DECLARE @dbname NVARCHAR(128); DECLARE @ReorganizeOrRebuildCommand NVARCHAR(MAX); DECLARE @dbid INT; DECLARE @indexFillFactor VARCHAR(5); DECLARE @fragmentationThreshold VARCHAR(10); DECLARE @indexStatisticsScanningMode VARCHAR(20); DECLARE @verboseMode BIT; DECLARE @reportOnly BIT; DECLARE @sortInTempdb VARCHAR(3); DECLARE @isHadrEnabled BIT; DECLARE @databaseToCheck VARCHAR(250) DECLARE @dynamic_command NVARCHAR(1024); DECLARE @dynamic_command_get_tables NVARCHAR(MAX); --Initializations - Do not change SET @databaseToCheck=NULL; SET @dynamic_command = NULL; SET @dynamic_command_get_tables = NULL; SET @isHadrEnabled=0; SET NOCOUNT ON; --------------------------------------------------------- --Set Parameter Values: You can change these (optional) - --Note: The script has default parameters set - --------------------------------------------------------- --if set to 1: it will just generate a report with the index reorganization/rebuild statements --if set to 0: it will reorganize or rebuild the fragmented indexes SET @reportOnly = 0; --optional: if not set (NULL), it will scann all databases --If name is set (i.e. 'testDB') it will just scan the given database SET @databaseToCheck = NULL; --maintains only the indexes that have average fragmentation percentage equal or higher from the given value SET @fragmentationThreshold = 15; --fill factor - the percentage of the data page to be filled up with index data SET @indexFillFactor = 90; --sets the scanning mode for index statistics --available values: 'DEFAULT', NULL, 'LIMITED', 'SAMPLED', or 'DETAILED' SET @indexStatisticsScanningMode='SAMPLED'; --if set to ON: sorts intermediate index results in TempDB --if set to OFF: sorts intermediate index results in user database's log file SET @sortInTempdb='ON'; --if set to 0: Does not output additional information about the index reorganization/rebuild process --if set to 1: Outputs additional information about the index reorganization/rebuild process SET @verboseMode = 0; ------------------------------ --End Parameter Values Setup - ------------------------------ -- check if given database exists and if compatibility level >= SQL 2005 (90) IF @verboseMode=1 PRINT 'Checking if database '+@databaseToCheck+' exists and if compatibility level equals or greater 2005 (90)'; -- if given database does not exist, raise error with severity 20 -- in order to terminate script's execution IF @databaseToCheck IS NOT NULL BEGIN DECLARE @checkResult INT SET @checkResult=(SELECT COUNT(*) FROM master.sys.databases WHERE [name]=RTRIM(@databaseToCheck)); IF @checkResult<1 RAISERROR('Error executing index reorganization/rebuild script: Database does not exist' , 20, 1) WITH LOG; DECLARE @checkResult2 INT SET @checkResult=(SELECT [compatibility_level] FROM master.sys.databases WHERE [name]=RTRIM(@databaseToCheck)); IF @checkResult<90 RAISERROR('Error executing index reorganization/rebuild script: Only databases with SQL Server 2005 or later compatibility level are supported' , 20, 1) WITH LOG; END IF @verboseMode=1 PRINT 'Initial checks completed with no errors.'; -- Temporary table for storing index fragmentation details IF OBJECT_ID('tempdb..#tmpFragmentedIndexes') IS NULL BEGIN CREATE TABLE #tmpFragmentedIndexes ( [dbName] sysname, [tableName] sysname, [schemaName] sysname, [indexName] sysname, [databaseID] SMALLINT , [objectID] INT , [indexID] INT , [AvgFragmentationPercentage] FLOAT, [reorganizationOrRebuildCommand] NVARCHAR(MAX) ); END -- Initialize temporary table DELETE FROM #tmpFragmentedIndexes; -- Validate parameters/set defaults IF @sortInTempdb NOT IN ('ON','OFF') SET @sortInTempdb='ON'; -- Check if instance has AlwaysOn AGs enabled SET @isHadrEnabled=CAST((SELECT ISNULL(SERVERPROPERTY('IsHadrEnabled'),0)) AS BIT); -- if database not specified scan all databases IF @databaseToCheck IS NULL BEGIN DECLARE dbNames_cursor CURSOR FOR SELECT s.[name] AS dbName , s.database_id FROM master.sys.databases s WHERE s.state_desc = 'ONLINE' AND s.is_read_only != 1 AND s.[name] NOT IN ( 'master', 'model', 'tempdb' ) AND s.[compatibility_level]>=90 ORDER BY s.database_id; END ELSE -- if database specified, scan only that database BEGIN DECLARE dbNames_cursor CURSOR FOR SELECT s.[name] AS dbName , s.database_id FROM master.sys.databases s WHERE s.state_desc = 'ONLINE' AND s.is_read_only != 1 AND s.[name]=RTRIM(@databaseToCheck) END -- if Always On Availability Groups are enabled, check for primary databases -- (thus exclude secondary databases) IF @isHadrEnabled=1 BEGIN DEALLOCATE dbNames_cursor; -- if database not specified scan all databases IF @databaseToCheck IS NULL BEGIN DECLARE dbNames_cursor CURSOR FOR SELECT s.[name] AS dbName , s.database_id FROM master.sys.databases s LEFT JOIN master.sys.dm_hadr_availability_replica_states r ON s.replica_id = r.replica_id WHERE s.state_desc = 'ONLINE' AND s.is_read_only != 1 AND UPPER(ISNULL(r.role_desc, 'NonHadrEnabled')) NOT LIKE 'SECONDARY' AND s.[name] NOT IN ( 'master', 'model', 'tempdb' ) AND s.[compatibility_level]>=90 ORDER BY s.database_id; END ELSE -- if database specified, scan only that database BEGIN DECLARE dbNames_cursor CURSOR FOR SELECT s.[name] AS dbName , s.database_id FROM master.sys.databases s LEFT JOIN master.sys.dm_hadr_availability_replica_states r ON s.replica_id = r.replica_id WHERE s.state_desc = 'ONLINE' AND s.is_read_only != 1 AND UPPER(ISNULL(r.role_desc, 'NonHadrEnabled')) NOT LIKE 'SECONDARY' AND s.[name]=RTRIM(@databaseToCheck); END END -- -- For each database included in the cursor, -- gather all tables that have indexes with -- average fragmentation percentage equal or above @fragmentationThreshold -- OPEN dbNames_cursor; FETCH NEXT FROM dbNames_cursor INTO @dbname, @dbid; WHILE @@fetch_status = 0 BEGIN --If verbose mode is enabled, print logs IF @verboseMode = 1 BEGIN PRINT '' PRINT 'Gathering index fragmentation statistics for database: ['+ @dbname + '] with id: ' + CAST(@dbid AS VARCHAR(10)); END; SET @dynamic_command_get_tables = N' USE [' + @dbname+ N']; INSERT INTO #tmpFragmentedIndexes ( [dbName], [tableName], [schemaName], [indexName], [databaseID], [objectID], [indexID], [AvgFragmentationPercentage], [reorganizationOrRebuildCommand] ) SELECT DB_NAME() as [dbName], tbl.name as [tableName], SCHEMA_NAME (tbl.schema_id) as schemaName, idx.Name as [indexName], pst.database_id as [databaseID], pst.object_id as [objectID], pst.index_id as [indexID], pst.avg_fragmentation_in_percent as [AvgFragmentationPercentage], CASE WHEN pst.avg_fragmentation_in_percent > 30 THEN ''ALTER INDEX [''+idx.Name+''] ON [''+DB_NAME()+''].[''+SCHEMA_NAME (tbl.schema_id)+''].[''+tbl.name+''] REBUILD WITH (FILLFACTOR = '+@indexFillFactor+', SORT_IN_TEMPDB = '+@sortInTempdb+', STATISTICS_NORECOMPUTE = OFF);'' WHEN pst.avg_fragmentation_in_percent > 5 AND pst.avg_fragmentation_in_percent <= 30 THEN ''ALTER INDEX [''+idx.Name+''] ON [''+DB_NAME()+''].[''+SCHEMA_NAME (tbl.schema_id)+''].[''+tbl.name+''] REORGANIZE;'' ELSE NULL END FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , '''+@indexStatisticsScanningMode+''') as pst INNER JOIN sys.tables as tbl ON pst.object_id = tbl.object_id INNER JOIN sys.indexes idx ON pst.object_id = idx.object_id AND pst.index_id = idx.index_id WHERE pst.index_id != 0 AND pst.alloc_unit_type_desc IN ( N''IN_ROW_DATA'', N''ROW_OVERFLOW_DATA'') AND pst.avg_fragmentation_in_percent >= '+ @fragmentationThreshold + ''; -- if verbose mode is enabled, print logs IF @verboseMode=1 BEGIN PRINT 'Index fragmentation statistics script: '; PRINT @dynamic_command_get_tables; END -- gather index fragmentation statistics EXEC (@dynamic_command_get_tables); -- bring next record from the cursor FETCH NEXT FROM dbNames_cursor INTO @dbname, @dbid; END; CLOSE dbNames_cursor; DEALLOCATE dbNames_cursor; ------------------------------------------------------------ -- if 'report only' mode is enabled IF @reportOnly=1 BEGIN SELECT dbName , tableName , schemaName , indexName , AvgFragmentationPercentage , reorganizationOrRebuildCommand FROM #tmpFragmentedIndexes ORDER BY AvgFragmentationPercentage DESC; END ELSE -- if 'report only' mode is disabled, then execute -- index reorganize/rebuild statements BEGIN DECLARE reorganizeOrRebuildCommands_cursor CURSOR FOR SELECT reorganizationOrRebuildCommand FROM #tmpFragmentedIndexes WHERE reorganizationOrRebuildCommand IS NOT NULL ORDER BY AvgFragmentationPercentage DESC; OPEN reorganizeOrRebuildCommands_cursor; FETCH NEXT FROM reorganizeOrRebuildCommands_cursor INTO @ReorganizeOrRebuildCommand; WHILE @@fetch_status = 0 BEGIN IF @verboseMode = 1 BEGIN PRINT '' PRINT 'Executing script:' PRINT @ReorganizeOrRebuildCommand END EXEC (@ReorganizeOrRebuildCommand); FETCH NEXT FROM reorganizeOrRebuildCommands_cursor INTO @ReorganizeOrRebuildCommand; END; CLOSE reorganizeOrRebuildCommands_cursor; DEALLOCATE reorganizeOrRebuildCommands_cursor; PRINT '' PRINT 'All fragmented indexes have been reorganized/rebuilt.' PRINT '' END END ELSE BEGIN PRINT ''; PRINT 'Error: You need to be SysAdmin and use SQL Server 2005 or later in order to use this script.'; PRINT ''; END --End of Script
Example Use of SQL Server Index Rebuild and Reorganize Script
Report Only
Let's see an example of using the script with @reportOnly=1 and with @fragmentationThreshold=15 for the sample database 'AdventureWorks2014'. These settings were changed and the script was run.
The script generated a report with 39 fragmented indexes.
A total of 32 indexes needed to be rebuilt:
And a total of 7 indexes needed to be reorganized:
Actual Rebuild and Reorganize Indexes
Now let's run the same example, but this time using the script with @reportOnly=0, @fragmentationThreshold=15 and database is set to 'AdventureWorks2014' as before.
As you can see, all indexes were reorganized / rebuilt.
Summary
The above script is very powerful and can help you maintain your indexes not only in standalone or clustered indexes of SQL Server, but also in AlwaysOn-Enabled instances that use Availability Groups.
Reorganizing and rebuilding indexes is not always the "magic" solution. It can sure help performance, but you need to maintain the health of your SQL Server instances in every aspect. You need to keep statistics updated, take into consideration how the storage system you use is organized and operates, set the proper parameters for your database settings and much more.
Nevertheless, index reorganization and specially rebuilds is one of the top choices for many DBAs as it is a fast way to fix (even temporarily sometimes) performance issues. This tip can help you through this process by simplifying the entire procedure.
Next Steps
Here are some additional thoughts:
- This script is a starting point. This can be run as is or you can add to it to meet your specific needs.
- To address index fragmentation for more complex needs, consider a table driven solution to meet the unique index needs of each table.
- Study the parameters the script offers and understand your options when addressing index fragmentation.
- Run first in a test / dev environment and once comfortable with the code think about converting it to a stored procedure or how you would use this in your environment.
- By using parts of the script, you can create even more sophisticated solutions for index fragmentation.
Also review the following tips and resources:
- MSDN Article: Reorganize and Rebuild Indexes/a>
- MSDN Article: sys.dm_db_index_physical_stats
- SQSQL Server Maintenance Tips
- SQL Server Indexing Tips
- SQL Server Fragmentation and Index Maintenance Tips
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips