MICROSOFT SQL SERVER REBUILD/REORG ALL INDEXES ON ALL DATABASES >10% FRAGMENTED

Index Fragmentation can be a major factor in performance degradation... Very simply put, as data is being written to the database, it needs to find free space to write (or replace) that data into. Ideally that space is contiguous, but sometimes it isnt. So an insert needs to find some free space, or, an update (or delete) might overflow the previous space used for that data and needs to find somewhere else to put the data.

As more data is needing to use (or creates) non-contiguous space, then it can cause fragmentation. When that happens, a read or search has to jump around the database to retrieve the requested data. There is a lot more to it, but that should give you an idea.

This is a sql script I wrote based off this MSDN article : http://msdn.microsoft.com/en-us/library/ms188917%28v=SQL.90%29.aspx

The above article contains a script the will rebuild/reorg indexes above 10% fragmented on just the current database.  I took the script much further, see details below.

This script will:

-      rebuild all indexes >30% fragmented
-      regorg all indexes >10% fragmented and <30% Fragmented
-      The script will run on all databases on the entire sql instance dynamically using a
       cursor based of list from master.sys.databases
-      The script excludes the following databases
       ('master','tempdb','msdb','model','AdventureWorks','AdventureWorksDW')


The script runs great as a sql server agent job and can completely replace the standard sql server maintenance plan job the reorg/rebuilds all indexes regardless of fragmentation.

SQL REBUILD / REORG SCRIPT:

/***************************************************************************************************/
                      -- Cursor WHILE loop for all db-s on the SQL Server instance
                      DECLARE @CurrentDB sysname
                      DECLARE curDatabase CURSOR FAST_FORWARD FOR 
                      SELECT name FROM master.sys.databases 
                      --SELECT * FROM master.sys.databases 
                      WHERE name not in ('master','tempdb','msdb','model','AdventureWorks','AdventureWorksDW')
                      OPEN curDatabase
                      FETCH NEXT FROM curDatabase INTO @CurrentDB
                      WHILE ( @@FETCH_STATUS = 0)
                      BEGIN
                      /********************************/
                      --print @CurrentDB
                      --
                      declare @sql1 varchar(100)
                      select @sql1 = 'USE ' + @CurrentDB + ';' + '
                      
                      '
                      --EXEC sp_sqlexec @sql1
                      --print @sql1
                      
                      declare @sql2 varchar(max)
                      select @sql2 = 
                      -- Ensure a USE <databasename> statement has been executed first.
                      '
                      SELECT DB_NAME() AS DataBaseName;
                      SET NOCOUNT ON;
                      DECLARE @objectid int;
                      DECLARE @indexid int;
                      DECLARE @partitioncount bigint;
                      DECLARE @schemaname nvarchar(130); 
                      DECLARE @objectname nvarchar(130); 
                      DECLARE @indexname nvarchar(130); 
                      DECLARE @partitionnum bigint;
                      DECLARE @partitions bigint;
                      DECLARE @frag float;
                      DECLARE @command nvarchar(4000); 
                      -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
                      -- and convert object and index IDs to names.
                      SELECT
                          object_id AS objectid,
                          index_id AS indexid,
                          partition_number AS partitionnum,
                          avg_fragmentation_in_percent AS frag
                      INTO #work_to_do
                      FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'')
                      WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
                      
                      -- Declare the cursor for the list of partitions to be processed.
                      DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
                      
                      -- Open the cursor.
                      OPEN partitions;
                      
                      -- Loop through the partitions.
                      WHILE (1=1)
                          BEGIN;
                              FETCH NEXT
                                 FROM partitions
                                 INTO @objectid, @indexid, @partitionnum, @frag;
                              IF @@FETCH_STATUS < 0 BREAK;
                              SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
                              FROM sys.objects AS o
                              JOIN sys.schemas as s ON s.schema_id = o.schema_id
                              WHERE o.object_id = @objectid;
                              SELECT @indexname = QUOTENAME(name)
                              FROM sys.indexes
                              WHERE  object_id = @objectid AND index_id = @indexid;
                              SELECT @partitioncount = count (*)
                              FROM sys.partitions
                              WHERE object_id = @objectid AND index_id = @indexid;
                      
                      -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
                              IF @frag < 30.0
                                  SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REORGANIZE'';
                              IF @frag >= 30.0
                                  SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REBUILD'';
                              IF @partitioncount > 1
                                  SET @command = @command + N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10));
                              EXEC (@command);
                      		--PRINT (@command);
                              PRINT N''Executed: '' + @command;
                      
                      -- These section commented out for reorganizing only and no rebuilding.
                      --            SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REORGANIZE'';
                      --		--PRINT (@command);
                      --		EXEC (@command);
                      --        PRINT N''Executed: '' + @command;
                      
                          END;
                      
                      -- Close and deallocate the cursor.
                      CLOSE partitions;
                      DEALLOCATE partitions;
                      
                      -- Drop the temporary table.
                      DROP TABLE #work_to_do;
                      
                      '
                      declare @sql varchar(max)
                      select @sql = @sql1 + @sql2
                      
                      EXEC sp_sqlexec @Sql
                      
                      --print @sql
                      
                      FETCH NEXT FROM curDatabase INTO @CurrentDB
                      END -- curDatabase WHILE loop
                      CLOSE curDatabase
                      DEALLOCATE curDatabase
                      
                      /**********************************************************************************/
                      

Select all Open in new window

In Conclusion:

- In most cases/environments this script only needs to be run once per week.
- I schedule it to run every Saturday night/Sunday Morning at 12:30AM
- I like to create three steps in my sql agent job
      #1 Report of Index Fragmentation prior to reorg/rebuild  (I included a copy of my report script article)
      #2 The actual Reorg/Rebuild Step
      #3 Report of Index Fragmentation after to reorg/rebuild (I included a copy of my report script at bottom of article)

Keep in mind you may see some indexes that stay over 30% fragmented. This is most likely due to the way sql server creates objects.  These particular objects are probably really small. When they are 1st created the object has a preallocate amount of free space in it which can skew the results in "sys.dm_db_index_physical_stats".

You really need to understand your own data and decide if fragmentation is hurting performance. In an environment where you do have the luxury of running a "maintenance" job over the weekend, then there should be no reason not to do the required housekeeping. If you dont, then you do need to plan your time more carefully, because some of the above tasks can take a while to run.

Your best place to start is with the "report" script shown below. It will highlight those indexes which may be causing you problems. Remember check those row counts (or page counts) to see if it is significant enough for you to worry about.

BEFORE/AFTER REPORT SQL SCRIPT:

-------------------------
                      DECLARE @CurrentDB sysname
                      DECLARE curDatabase CURSOR FAST_FORWARD FOR 
                      SELECT name FROM master.sys.databases 
                      --SELECT * FROM master.sys.databases 
                      WHERE name not in ('master','tempdb','msdb','model','AdventureWorks','AdventureWorksDW')
                      OPEN curDatabase
                      FETCH NEXT FROM curDatabase INTO @CurrentDB
                      WHILE ( @@FETCH_STATUS = 0)
                      BEGIN
                      --print @CurrentDB
                      --
                      declare @sql1 varchar(100)
                      select @sql1 = 'USE ' + @CurrentDB + ';' + '
                      
                      '
                      
                      --EXEC sp_sqlexec @sql1
                      --print @sql1
                      
                      declare @sql2 varchar(max)
                      select @sql2 = 
                      -- Ensure a USE <databasename> statement has been executed first.
                      '
                      SET NOCOUNT ON
                      
                      SELECT DB_NAME() AS DataBaseName;
                      
                      
                      IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''#work_to_do'') AND type in (N''U''))
                      DROP TABLE #work_to_do
                      
                      
                      SELECT
                          object_id AS objectid,
                          index_id AS indexid,
                          partition_number AS partitionnum,
                          avg_fragmentation_in_percent AS frag
                      INTO #work_to_do
                      FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'')
                      WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
                      
                      
                      select rtrim(name) as objectname,frag as frag_percent,type,create_date,objectid from #work_to_do a , sys.objects b
                      where a.objectid=b.object_id
                      order by frag desc
                      '
                      
                      
                      declare @sql varchar(max)
                      select @sql = @sql1 + @sql2
                      
                      EXEC sp_sqlexec @Sql
                      
                      --print @sql
                      
                      
                      
                      
                      
                      FETCH NEXT FROM curDatabase INTO @CurrentDB
                      END -- curDatabase WHILE loop
                      CLOSE curDatabase
                      DEALLOCATE curDatabase
                      

Select all Open in new window

So, hope you find these scripts useful, and please check / test first and make sure your own maintenance solution works well for you in your environment.