MICROSOFT SQL SERVER REBUILD/REORG ALL INDEXES ON ALL DATABASES >10% FRAGMENTED: Microsft SQL Server, Microsft SQL Server 2005, Microsft SQL Server 2008, Windows Server 2008, Windows Server 2003

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

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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:

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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:

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.

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

Microsft SQL Server

Microsft SQL Server 2005

Microsft SQL Server 2008

Windows Server 2008

Windows Server 2003

MS SQL Server

About The Author

Comments

Add your Comment

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Access Proven Solutions

Read In-Depth How-To Guides

Watch Step by Step Tutorials

And Much More!

Top MS SQL Server Experts