Script to Manage SQL Server Rebuilds and Reorganize for Index Fragmentation

By:   |   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:

Sample Index Maintenance Operation - Report Only Part1

And a total of 7 indexes needed to be reorganized:

Sample Index Maintenance Operation - Report Only Part2

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.

Sample Index Maintenance Operation - Execution Outcome

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:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Artemakis Artemiou Artemakis Artemiou is a Senior SQL Server and Software Architect, Author, and a former Microsoft Data Platform MVP (2009-2018).

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Tuesday, March 5, 2024 - 2:26:18 PM - Artemakis Artemiou Back To Top (92043)
Hi Pradeep,

It's great to hear that your index optimization jobs are running smoothly. Dealing with high fragmentation levels, especially when they exceed 50%, can indeed pose a challenge.

Regarding your question about alternative strategies, you might also consider:

1. Focus on Critical Indexes: Instead of rebuilding everything, you may consider prioritizing indexes that really affect performance.

2. Rebuild Indexes of Critical Tables More Often: For tables with lots of changes, you may consider rebuilding their indexes more frequently. This helps keep important tables optimized without doing everything at once.

3. Try Reorganizing Indexes: Sometimes, under certain conditions, just reorganizing indexes can reduce fragmentation without a full rebuild. It's quicker and can be done online, so it's less disruptive.

4. Keep an Eye on Things: Watch the index fragmentation levels and performance metrics regularly. This helps you catch issues early and keep things running smoothly.

5. Check the relevant Storage: Make sure the storage setup can handle index maintenance well. Things like disk speed and setup can affect how well the indexes rebuild.

In addition, it's worth noting that sometimes it can be also helpful to combine multiple techniques for handling index fragmentation. By carefully assessing the specific needs and circumstances, you can tailor your approach to achieve the best results for your database environment.

Best regards,
Artemakis

Monday, February 12, 2024 - 11:16:31 AM - Pradeep Back To Top (91940)
Hi Artemakis,
I have a doubt regarding index and fragmentation level . In my project every server is configured with index optimize job which contains olla holangram scripts. The job is running successfully every week but when i see dm_db_index_phyiscal stats most of the table/ objects I can see fargmention level is more than 50%. It will be difficult to rebuild each and every table it will consume more time . What will be best alternative if you suggest it will be helpful.

Tuesday, October 10, 2023 - 9:38:13 AM - Greg Robidoux Back To Top (91644)
The script was updated to reflect the comment from Artemakis.

-Greg

Tuesday, October 10, 2023 - 6:01:31 AM - Artemakis Artemiou Back To Top (91643)
Hello AndySugs,

That is correct. There's a spelling mistake in the comment line.

Setting @verboseMode=1 outputs additional information about the index reorganization/rebuild process.

Here's the corrected comment line in the relevant code block:

--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;


Best regards,
Artemakis

Friday, October 6, 2023 - 8:08:37 PM - AndySugs Back To Top (91632)
Hi Artemakis,

In the script should the bottom comment line below where it outputs additional info for @verboseMode not be set to one rather than zero?

--if set to 0: Does not output additional information about the index reorganization/rebuild process
--if set to 0: Outputs additional information about the index reorganization/rebuild process
SET @verboseMode = 0;

Sunday, June 12, 2022 - 11:06:06 AM - Artemakis Artemiou Back To Top (90155)
Hi Steven,

Thank you for your kind words and feedback.

Yes, indeed, the script does not support columnstore indexes and that's why you get the error.

A workaround, is to modify the script, so that it excludes columnstore indexes from the index rebuild/reorganization T-SQL statement generation.

For example, you can use in the WHERE clause the condition: "idx.type not in (5,6)"

Then, you could handle the rebuild/reorganization of any columnstore indexes using another mechanism.

Best Regards,
Artemakis

Thursday, June 9, 2022 - 10:18:07 AM - Steven Back To Top (90149)
Hi,
Very useful script. Thanks for that. It works on most of my databases. Unfortunately, for one specific database, I always get this error:
ALTER INDEX REBUILD statement failed because specifying FILLFACTOR is not allowed when rebuilding a columnstore index. Rebuild the columnstore index without specifying FILLFACTOR.
Am I doing something wrong?
Thanks


Monday, June 15, 2020 - 7:41:17 AM - Artemakis Artemiou Back To Top (86005)

Hi Troy,

Thank you for your comment.

In the official SQL Server documentation, Microsoft advises about the best method for removing index fragmentation in SQL Server as per below:

When you have an index fragmentation avg_fragmentation_in_percent value > 5% and < = 30%, then the suggestion is to Reorganize the index.

In a different case, where you have an index fragmentation avg_fragmentation_in_percent value > 30%, the suggestion is to Rebuild the index.

Note that the above recommendations, sometimes might differ for some more specialized index fragmentation cases, therefore, in such cases, another approach is to experiment in order to determine the best threshold for your environment.

You can read more about these recommendations on the following MS Docs article: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes

Best Regards,

Artemakis


Friday, June 12, 2020 - 3:16:36 PM - Troy Frericks Back To Top (85997)

Can you explain the function of the hardcoded values (30, 5) in this snipit?

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


Thursday, May 21, 2020 - 4:02:30 AM - Artemakis Artemiou Back To Top (85711)

Hi Melikşah,

Thank you for your comment and your suggestion.

Yes, you are correct, by checking for types 5 and 6 in sys.indexes you can handle columnstore indexes in the script.

Since these index types were added to SQL Server 2012 and later, I plan in the near future, to create a new version of the script and test it prior to updating the tip.

Best Regards,

Artemakis


Friday, May 15, 2020 - 7:39:31 AM - Melikşah Bardakci Back To Top (85671)

 Hi Artemakis

Thanks for the script.

I quickly read the comments and see the problem with columnstore indexes.

 

I believe below modification will solve the problem. You can check and update your post if you want.

idx.type not in (5,6) = Detecting ColumnStore indexes

KR,

Melikşah Bardakci

 

 

WHEN idx.type not in (5,6)

     THEN CASE 

      WHEN pst.avg_fragmentation_in_percent > 15 

  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

ELSE

CASE

WHEN pst.avg_fragmentation_in_percent > 15 

THEN ''ALTER INDEX [''+idx.Name+''] ON [''+DB_NAME()+''].[''+SCHEMA_NAME (tbl.schema_id)+''].[''+tbl.name+''] REBUILD;''

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

END


Monday, May 4, 2020 - 3:06:12 AM - Artemakis Artemiou Back To Top (85555)

Hi Roy,

Thank you for reading my article and thank you for your question. 

The approach I would follow for this, would be to find the outdated statistics right after I run the index rebuilt/reorganize script, and update them if necessary.

There is a great tip on MSSQLTips that you can check regarding this. This tip, helps you find the outdated statistics in SQL Server and update them.

You can check the tip at: https://www.mssqltips.com/sqlservertip/2628/how-to-find-outdated-statistics-in-sql-server-2008/

Cheers,

Artemakis 


Friday, May 1, 2020 - 2:56:52 PM - Roy Sánchez Benavides Back To Top (85530)

Good morning Artemakis, according to the recommendations after reorganizing indexes, the statistics should be updated. In this case where some indexes are reorganized and others are rebuilt (according to the experts, "rebuild" updates the index statistics but not the column statistics), based on the article script, how and where should I add the update script of statistics ?. Thanks in advance.


Friday, March 13, 2020 - 3:51:28 AM - Artemakis Artemiou Back To Top (85098)

Hi Tommy,

Thank you for your kind words.

In the same session where you execute the script, you can find all the fragmented index information along with the rebuild/reorganization commands, in the temporary table #tmpFragmentedIndexes

Therefore, I suppose you could use Database Mail in order to get the records from the #tmpFragmentedIndexes table.

There is a relevant tip on MSSQLTips you could check about Database Mail: https://www.mssqltips.com/sqlservertip/2347/send-email-in-a-tabular-format-using-sql-server-database-mail/

Cheers,

Artemakis


Wednesday, March 11, 2020 - 5:22:16 AM - Tommt Back To Top (85054)

Hi Artemakis

nice script

is it possble to get the report on mail from the script that is outputed?

Regards

Tommy


Friday, September 13, 2019 - 2:51:11 PM - Artemakis Artemiou Back To Top (82416)

Hi Siddhesh,

Thank you for your comment and kind words.

Yes indeed, if the user that runs the script is a db_owner on the specific database, the index rebuild/reorganize process can run.

The concept however, based on which the specific script was created, was that index maintenance is usually performed by a Database Administrator, which is often a SysAdmin and not a db_owner on any database. 

Also, in the case where a SQL Server instance has hundreds of databases and you would like to run the index fragmentation check and the index rebuild/reorganize procedure for all of them at once, if the user is not a SysAdmin, you would need to grant the db_owner role to the user for all these databases.

Thanks again for your feedback.

Best Regards,

Artemakis


Friday, September 13, 2019 - 10:36:40 AM - Siddhesh Back To Top (82409)

Nice script, but just a note you don't really need sysadmin role. dbowner role on the DB you want to rebuild/reorganize index is enough. 


Monday, February 25, 2019 - 1:49:44 PM - Artemakis Artemiou Back To Top (79122)

Hi Jack,

Thank you very much for your kind words.

You can definitely try Greg's suggestion and see how it goes.

Also, please note the following facts regarding this script:

  • This script supports only SQL Server 2005 or later. Any databases with compatibility level "SQL Server 2000 (80)" or earlier are automatically excluded from the index reorganization/rebuild process. So for example, if you run the script on a SQL Server 2008 R2 instance, but there are databases with compatibility level 80 (SQL 2000), then these databases will be automatically excluded by the script's logic.
  • You can try setting the @fragmentationThreshold = 0 to see if some databases/indexes are excluded due to their possibly low fragmentation percentage. 

Regarding databases with one or more underscores in their name (i.e. Database_Name_123), the script includes them as well (I have just re-tested it :)

So please check the above and let us know how it goes.

Best Regards,

Artemakis


Monday, February 25, 2019 - 8:58:43 AM - Greg Robidoux Back To Top (79116)

Hi Jack,

one thing you could try is to change the cursor that is used to get a list of databases.

Try to change this line from:

DECLARE dbNames_cursor CURSOR
FOR

to

DECLARE dbNames_cursor CURSOR READ_ONLY
FOR

-Greg


Monday, February 25, 2019 - 6:31:58 AM - Jack Whittaker Back To Top (79113)

A very useful script, which I use all the time. 
A puzzle however - although I usually run it with @databasetocheck = NULL, sometimes it ignores databases.  I notice because there are no results for a specific key database in ReportOnly mode

Also sometimes I notice that there is a high level of fragmentation on some DBs, which I would have expected the script to address
It may be a coincidence, but the databases it seems to miss most often have an underscore ion the name e.g. ABX_GDPR and ABX_Archive
Any ideas?


Thursday, January 10, 2019 - 11:35:56 AM - Artemakis Artemiou Back To Top (78699)

Hi James,

Thank you for your kind words!

I'm glad you find the script useful!

That's the beauty of T-SQL scripts, they can be easily used in automated processes, such as maintenance plans. 

Cheers,

Artemakis


Thursday, January 10, 2019 - 10:21:42 AM - James V Hagans Back To Top (78694)

This is great, just what I was looking for.  Plugging into Maintenance Plans!

Thanks for publishing.


Saturday, May 27, 2017 - 7:14:15 AM - Artemakis Artemiou Back To Top (56104)

Hi Animesh,

 

Thank you for your kind words.

 

About point 1, this check is not really necessary because the script finds these objects (indexes) using system tables and dynamic management views prior to constructing the reorganization/rebuild T-SQL statements. By the time the script finds them, it means that they exist.

 

Regarding point 2, note that whenever the T-SQL script is executed, if there is any error, is reported by SQL Server Database Engine. If, however, you want to have more control over each index reorganization/rebuild statement, you can set the script's parameter "SET @reportOnly = 1;" and then you will be able to control via SSMS the execution of each one of those T-SQL statements.

 


Wednesday, May 24, 2017 - 9:59:00 AM - Animesh Back To Top (56005)

 This is really helpful. Thanks!

We need to add two thing on this, please help me out how to do:

1.We want to add a check just before the execution of the ReorganizeOrRebuildCommand whether the object exsists or not

2.If there is any error during the execution of any index ReorganizeOrRebuildCommand, we need the error to be logged in the output and go forward with the next index.

 


Monday, May 15, 2017 - 12:28:37 PM - Artemakis Artemiou Back To Top (55783)

Hi karthik,

 

Thank you for your comment.

 

You are correct. The script does not support columnstore indexes.

 

The reason is that the ALTER INDEX...REBUILD statement for columnstore indexes does not accept the WITH(FILLFACTOR = ..., SORT_IN_TEMPDB = ..., STATISTICS_NORECOMPUTE = ...) parameters.

 

There is however a workaround:

 

You can set @reportOnly = 1;

 

and then just for the columnstore indexes you can remove from the "reorganizationOrRebuildCommand" REBUILD T-SQL statements the "WITH" keyword along with its parameters. 

 

For example, for columnstore index "idx_tblColumnStore" you can replace the below index rebuild statement:

 

ALTER INDEX [idx_tblColumnStore] ON [dbo].[tblColumnStore] REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF);

 

with the below:

 

ALTER INDEX [idx_tblColumnStore] ON [columnstore].[dbo].[tblColumnStore] REBUILD;

 

 

Regards,

Artemakis

 

P.S.: The ALTER INDEX...REORGANIZE statements work for columnstore indexes as well.

 


Friday, May 12, 2017 - 11:27:38 PM - karthik Back To Top (55710)

The code doesnt work for Columnstored Index !!!

 


Monday, January 30, 2017 - 1:24:55 PM - Artemakis Artemiou Back To Top (45736)

 

Thank you for your kind words Lawrence!

 

Cheers,

Artemakis


Monday, January 30, 2017 - 1:22:49 PM - Artemakis Artemiou Back To Top (45735)

Hi Stacy,

Thank you very much for your kind words!

I'm really glad I helped!

 

Kind regards,

Artemakis Artemiou

 


Saturday, January 28, 2017 - 7:53:03 AM - Stacy Back To Top (45674)

Thank you so much! This worked perfectly!

 


Saturday, January 14, 2017 - 8:04:41 AM - Lawrence Back To Top (45292)

 Very nice script!

 


Saturday, January 14, 2017 - 3:46:05 AM - Artemakis Artemiou Back To Top (45289)

Hi Jeffrey,

Thank you for your comment.

If you just copy-paste the script it will work with the default settings. That means that it will rebuild the fragmented indexes for all databases in the SQL instance.

 

If you want the script to scan all databases then you can use the below (this is the default setting):

SET @databaseToCheck =  NULL;

 

If you need to scan a single database, for example database 'TestDB1', then you can use the below:

SET @databaseToCheck =  'TestDB1';

 

Hope this helps.

 


Friday, January 13, 2017 - 11:28:07 AM - Jeffrey Back To Top (45272)

We are having trouble with the script executing successfully and have decided to split out one large database and have it run alone in a job and all the other databases run together.  In the first job I have the option

--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 =  (select name from sys.databases where name <>'databasename');

And in the other job I have done the following

--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 =  ‘databasename’

Do you believe this will work.  your help is apprecited.

The failure is that it stops when processing the  

 

 


Thursday, September 29, 2016 - 10:58:39 AM - Artemakis Artemiou Back To Top (43458)

Hi Michail,

 

Thank you for your comment. It is an excellent question. This is a very common phenomenon, especially in the case of small indexes.

 

Below I am quoting the relevant MSDN article which can be found at: https://msdn.microsoft.com/en-us/library/ms189858(v=sql.105).aspx

 

"In general, fragmentation on small indexes is often not controllable. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index."

 

As a sidenote, if we are talking about tables that are not partitioned, do not try to update the index statistics after the rebuild, because in the case where an index rebuild command is executed, it also updates the index statistics.

 

Kind regards,

Artemakis


Thursday, September 29, 2016 - 4:28:38 AM - Michail Back To Top (43444)

 After the script has a lot of indexes are not defragmented. If you run the script that some indices are corrected several times, but many still remain defragmented.

 

 















get free sql tips
agree to terms