<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Published on
16,561 Points
10,261 Views
3 Endorsements
Last Modified:
Approved
Community Pick
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

/**********************************************************************************/

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

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.
3
Author:H
  • 2
3 Comments

Expert Comment

by:grahamco
When i attempt to execute the re-index/reorg script on my test server it only returns the database name it does not execute. Is there another step necessary that i'm missing?
0

Expert Comment

by:grahamco
disregard my last, it did run.
0
LVL 11

Expert Comment

by:Senior IT System Engineer
Hi,

When I tried to run the reporting script on my SQL Server DB which contains space in the name as below, it failed:

Screenshot of the existing DB:
List of Database
Script:
------------------------- 
DECLARE @CurrentDB SYSNAME 
DECLARE curdatabase CURSOR fast_forward FOR 
  SELECT NAME 
  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  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 

Open in new window


Result:
Msg 911, Level 16, State 1, Line 1
Database 'VMware' does not exist. Make sure that the name is entered correctly.

Open in new window


So what can I do to fix that SQL script ?

Thanks.
0

Featured Post

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Viewers will learn how the fundamental information of how to create a table.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month