SQL Server 2005 How to Auto ReIndex / Defragment your Database Tables.

 The objects below can be created in a database of your choosing.  I personally use a database called AdminData it is a catch all for all things related to database administration. Here is a summary of what you're going to create and how you will use it.

1.  Create a new table called TableStats to store statistical data about the indexes being optimized. It is used to determine what indexes need to be rebuilt and record the last time the index was optimized.

2.  Create the stored procedure Acme_Database_Optimize which will call all the other procedures and control how database optimizing will occur.

3. Create the stored procedure ACME_BuildFragList which will insert into TableStats information about the tables.

4. Create the stored procedure ACME_DeFragTables which will read the TableStats records and rebuild the indexes.

5. Schedule to call Acme_Database_Optimize.

Let's get started: more information will be given as we go. First change references for ACME to something of your choosing maybe your company name.

Create the Table: The table below will be used to record statistical data from the job runs. This data is retrieved from the tables sys.dm_db_index_physical_stats, sysindexes, and sys.objects

 
USE [AdminData]
CREATE TABLE [dbo].[TableStats](
                [TblStatsID] [int] IDENTITY(1,1) NOT NULL,
                [Database_Name] [varchar](50) NOT NULL,
                [Table_Name] [varchar](100) NOT NULL,
                [Index_Name] [varchar](100) NOT NULL,
                [Index_ID] [int] NOT NULL,
                [Partition_Number] [int] NULL,
                [Database_ID] [int] NOT NULL,
                [Object_ID] [int] NOT NULL,
                [Load_Time] [smalldatetime] NULL,
                [DeFrag_Time] [smalldatetime] NULL,
                [Index_Type_Desc] [varchar](25) NULL,
                [Index_Depth] [int] NULL,
                [Index_Level] [int] NULL,
                [AVG_Fragmentation_In_Percent] [numeric](18, 12) NULL,
                [Fragment_Count] [int] NULL,
                [AVG_Fragment_Size_In_Pages] [numeric](18, 12) NULL,
                [Page_Count] [int] NULL,
                [AVG_Page_Space_Used_In_Percent] [numeric](18, 12) NULL,
                [Record_Count] [bigint] NULL,
                [MIN_Record_Size_In_Bytes] [int] NULL,
                [MAX_Record_Size_In_Bytes] [int] NULL,
                [AVG_Record_Size_In_Bytes] [int] NULL,
                [schemaID] [int] NULL,
                [Owner] [varchar](25) NULL,
 CONSTRAINT [PK_TableStats] PRIMARY KEY CLUSTERED
(
                [TblStatsID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 GO

Acme_Database_Optimize: This is the main controlling job and is the only job called from the schedule. It has a number of variables is passes to the other jobs to control how indexing will take place. I will list them here.

1.     Fragmax: Set this to a percentage  that when greater than will execute the reindex of the table. 10 is a good choice.  

2.      FragType:  Lock, NoLock , or DEFAULT  use NoLock  for daytime run it will execute the reorganize option, Lock does a complete rebuild, default can use either and will be based on fragmentation > 30

3.      FastFlag:  Y or N Use for faster processing note record counts and other columns will not be collected this controls the Limited / sampled option of the dm_db_index_physical_stats function.

4.      DatabaseList: List of databases to be reindexed

5.      UpdateUsage: Y or N Use for new databases and use sparingly. Executes dbcc updateusage. Normally does not need to be ran

6.      UpdateStatistics: Y or N Use to update statistics as a last step. Executes sp_updatestats.

 USE [AdminData]
GO
/****** Object:  StoredProcedure [dbo].[Acme_Database_Optimize]    Script Date: 02/29/2012 09:11:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Acme_Database_Optimize]
@Fragmax as int,
@FragType as varchar(7), -- 'Lock','NoLock' or 'DEFAULT' use NoLock for daytime run, Lock does reindex, default can use either based on fragmentation > 30
@FastFlag char(1), -- 'Y' or 'N' Use for faster processing note record couts and other columns will not be collected
@DatabaseList as nvarchar(2000),    -- List of databases
@UpdateUsage char(1),    -- 'Y' or 'N' Use for new databases and use sparingly. Executes dbcc updateusage. Normally will only be ran once.
@UpdateStatistics char(1) -- 'Y' or 'N' Use to update statistics as a last step. Executes sp_updatestats.
as

DECLARE
@DBName varchar(128),
@strTmp varchar(255),
@SQLString as nvarchar(2000),
@ParmDefinition nvarchar(2000)

if @Fragmax = '' set @Fragmax = 10
If @FragType = '' set @FragType = 'NoLock'
If @FastFlag = '' set @FastFlag = 'Y'
If @UpdateUsage = '' set @UpdateUsage = 'N'


If @FragType <> 'NoLock' and @FragType <> 'Lock' and @FragType <> 'Default'
    BEGIN
        print 'Fragtype is not valid program will end'
        GOTO ENDIT
    END

set @strTmp = 'Max Frag Percent set at ' + cast(@Fragmax as varchar(10)) + '%'
PRINT @strTmp

set @databaselist = '(''' + replace(@databaselist,',',''',''') + ''')' -- Replace commas with quote comma quote

set @SQLString = N'select @DBNameOUT = min(Name) from master.dbo.sysdatabases where name in' + @databaselist;    -- create sql string
set @ParmDefinition = N'@DBNameOUT varchar(128) OUTPUT';                                                        -- Create Output parameter
exec sp_executesql @SQLString, @ParmDefinition, @DBNameOut=@DBName OUTPUT;                                        -- Execute SQL

WHILE @DBNAme IS NOT NULL BEGIN
    PRINT '----------------------------------------------------------------------'
   

    SET @strTmp = 'Start processing ' + @DBName + ' At ' + cast(getdate() as varchar(30))
    PRINT @strTmp
    if @UpdateUsage = 'Y'
        BEGIN
        SET @strTmp = '***** Executing DBCC UPDATEUSAGE for ' + @DBName + ' At ' + cast(getdate() as varchar(30))
        PRINT @strTmp
        DBCC UPDATEUSAGE(@DBName)
        END
    ELSE
        BEGIN
        SET @strTmp = '***** DBCC UPDATEUSAGE WILL NOT BE EXECUTED'
        PRINT @strTmp
        END
   
    SET @strTmp = '***** Update Fragmentation Levels for ' + @DBName + ' At ' + cast(getdate() as varchar(30))
    PRINT @strTmp
    exec Acme_BuildFragList @DBName, @FastFlag -- Job updated DBCCStatistics with current Fragmentation statistical data

    SET @strTmp = '***** Reindexing Tables for ' + @DBName + ' At ' + cast(getdate() as varchar(30))
    PRINT @strTmp
    exec Acme_DeFragTables @fragMax, @DBName, @FragType  -- Execute dbcc dbreindex on Tables where the frag% > @fragMax
    If @UpdateStatistics = 'Y'
        BEGIN
        SET @strTmp = '***** Updating statistics for ' + @DBName + ' At ' + cast(getdate() as varchar(30))
        PRINT @strTmp
        exec('use [' + @DBName + '] exec sp_updatestats @Resample = ''resample''') -- Update statistics on all Tables
        END
    ELSE
        BEGIN
        SET @strTmp = '***** Statistics will not be updated '
        PRINT @strTmp
        END
       
    SET @strTmp = 'End processing ' + @DBName + ' At ' + cast(getdate() as varchar(30))
    PRINT @strTmp

    set @SQLString = N'select @DBNameOUT = min(Name) from master.dbo.sysdatabases where name in' + @databaselist + 'and [Name] > @DBNameOut';
    exec sp_executesql @SQLString, @ParmDefinition, @DBNameOut=@DBName OUTPUT;                                        -- Get Next Database

    END

ENDIT:

ACME_BuildFragList: This job will execute the sys.dm_db_index_physical_stats function for the database being passed on all indexes and add new records to the TableStats table in AdminData. The FastFlag will determine how much data is recorded and how long this job will execute. Setting the fastflag to true will record less data but enough to efficiently keep you indexes defragmented. Set to false if you like more detailed data to be recorded and don't mind the speed of execution.  

USE [AdminData]
GO

ALTER PROCEDURE [dbo].[Acme_BuildFragList](@DBName varchar(128), @FastFlag char(1))
AS

Declare
@ScanType nvarchar(20),
@db_id int,
@SQL nvarchar(4000),
@ReturnCode int

If @FastFlag = 'Y'
    BEGIN
    set @ScanType = 'LIMITED'
    END
ELSE
    BEGIN
    set @ScanType = 'SAMPLED'
    END
SET @db_id = DB_ID(@DBName);

SET @SQL = N'use ' + @DBName + '; insert into AdminData.dbo.TableStats
    (Database_Name, Table_Name, Index_Name,Index_ID,Partition_Number,Database_ID,
ObJect_ID,Load_Time,Index_Type_Desc,Index_Depth,Index_Level,AVG_Fragmentation_IN_Percent,
Fragment_Count,AVG_Fragment_Size_In_Pages,Page_Count,AVG_Page_Space_Used_In_Percent,
Record_Count,MIN_Record_Size_In_Bytes,MAX_Record_Size_In_Bytes,AVG_Record_Size_In_Bytes, schemaID, Owner)
SELECT ''' + @DBName + ''', object_name(IPS.object_Id), isnull(IDX.name,''HEAP''),  index_id, Partition_number, Database_ID, IPS.Object_ID, getdate(),
    Index_Type_Desc, Index_Depth,
    Index_Level,
    cast(avg_fragmentation_In_Percent as numeric(18,12)), Fragment_Count,
    cast(AVG_Fragment_Size_IN_Pages as numeric(18,12)),
    Page_Count,
    cast(AVG_Page_Space_Used_IN_Percent as numeric(18,12)), Record_Count,
    MIN_record_size_in_bytes, MAX_record_size_in_bytes, AVG_record_size_in_bytes,Ob.schema_id, schema_name(Ob.schema_id)
    FROM sys.dm_db_index_physical_stats(' + cast(@db_id as nvarchar(10)) + ', NULL, NULL, NULL,''' + @ScanType  + ''') IPS,
    sysindexes IDX,
    sys.objects Ob
    where IDX.ID = IPS.object_ID
    and IDX.indid = IPS.index_ID
    and Ob.object_id = IPS.object_ID'

exec dbo.sp_executesql @statement = @SQL
set @ReturnCode = @@ERROR
return @ReturnCode

Acme_DeFragTables: This job does the actual defraging of the indexes. It uses a few options to determine how this is accomplished.

 1.      MaxFrag:         Set to a percentage that when fragmentation is greater than will defrag the index.

2.      DBName:         Database to be defragmented.

3.      FragType:        If set to Lock it will rebuild the index. if set to nolock will reorganize the index. If set to default will reorganize if less that 30% fragmented and will rebuild if > 30% fragmented. This is based on my own personal testing on which is faster.

 USE [AdminData]
GO
/****** Object:  StoredProcedure [dbo].[Acme_DeFragTables]    Script Date: 02/29/2012 09:14:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Acme_DeFragTables]
(@MaxFrag int = 10, @DBName varchar(128) = NULL, @FragType varchar(7))
as
DECLARE @TblStatsID int,
    @Database_Name varchar(50),
    @Owner varchar(25),
    @Table_Name varchar(100),
    @Index_Name varchar(100),
    @Object_ID int,
    @Index_ID int,
    @Partition_Number int,
    @Partition_Count int,
    @AVG_Fragmentation_IN_Percent numeric(18,12),
    @SQL nvarchar(2000),
       @ParmDefinition nvarchar(2000),
    @MSG varchar(200),
    @ERR int

DECLARE FragmentedTables CURSOR FAST_FORWARD
    FOR
    SELECT     TblStatsID,
        Database_Name,
        Owner,
        Table_Name,
        Index_Name,
        [object_ID],
        index_ID,
        Partition_Number,
        AVG_Fragmentation_IN_Percent
        from admindata.dbo.TableStats  
        where     AVG_Fragmentation_In_Percent > @MaxFrag
        AND Database_Name = @DBName
        AND    Load_Time > GetDate() -.5
        AND    DeFrag_Time IS NULL
        order by AVG_Fragmentation_In_Percent desc
Set @ERR = 0
OPEN FragmentedTables

FETCH NEXT
FROM FragmentedTables
INTO @TblStatsID, @Database_Name, @Owner, @Table_Name, @Index_Name, @object_ID, @index_ID, @Partition_Number, @AVG_Fragmentation_IN_Percent  


WHILE @@FETCH_STATUS = 0 BEGIN

    -- Determine if partitioned
    set @SQL = N'select @Partition_CountOUT = Count(*)
            From [' + @Database_Name + '].sys.partitions
            Where object_id = ' + cast(@Object_ID as varchar(20)) + ' And index_id = ' + cast(@Index_ID as varchar(20)) + ';'
    set @ParmDefinition = N'@Partition_countOUT int OUTPUT';
    exec dbo.sp_executesql @SQL, @ParmDefinition, @Partition_CountOUT=@Partition_Count OUTPUT;

    IF @FragType = 'Lock'
        BEGIN    -- REBUILD INDEX
            if @Partition_Count = 1  
                BEGIN -- non partitioned index
                set @SQL = N'alter index [' + @Index_Name + '] on [' + @Database_Name + '].' + @Owner + '.' + @Table_Name + '
                REBUILD WITH(FILLFACTOR = 90, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);'
                set @MSG = 'Rebuilding Index: ' + @Table_Name + '.' + @Owner + '.' + @Index_Name
                END
            else
                BEGIN -- partitioned index
                set @SQL = N'alter index [' + @Index_Name + '] on [' + @Database_Name + '].' + @Owner + '.' + @Table_Name + '
                REBUILD Partition = ' + cast(@Partition_Number as varchar(20)) + ';'
                set @MSG = 'Rebuilding Index: ' + @Table_Name + '.' + @Owner + '.' + @Index_Name + ' Partition: ' + cast(@Partition_Number as varchar(20))
                END
        END    

    IF @FragType = 'NoLock' -- REORGANIZE INDEX
        BEGIN
            IF  @Partition_Count = 1
                BEGIN -- non partitioned index
                set @SQL = N'alter index [' + @Index_Name + '] on [' + @Database_Name + '].' + @Owner + '.' + @Table_Name + '
                REORGANIZE;'
                set @MSG = 'Reorganizing Index: ' + @Table_Name + '.' + @Owner + '.' + @Index_Name
                END
            ELSE
                BEGIN -- partitioned index
                set @SQL = N'alter index [' + @Index_Name + '] on [' + @Database_Name + '].' + @Owner + '.' + @Table_Name + '
                REORGANIZE Partition = ' + cast(@Partition_Number as varchar(20)) + ';'
                set @MSG = 'Reorganizing Index: ' + @Table_Name + '.' + @Owner + '.' + @Index_Name + ' Partition: ' + cast(@Partition_Number as varchar(20))
                END
        END
    
    IF @FragType = 'DEFAULT'  -- Default based on Fragmentation Percent% > 30
        BEGIN
            IF @AVG_Fragmentation_IN_Percent > 30
                BEGIN    -- Rebuild Index
                    if @Partition_Count = 1  
                        BEGIN -- non partitioned index
                        set @SQL = N'alter index [' + @Index_Name + '] on [' + @Database_Name + '].' + @Owner + '.' + @Table_Name + '
                        REBUILD WITH(FILLFACTOR = 90, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);'
                        set @MSG = 'Rebuilding Index: ' + @Table_Name + '.' + @Owner + '.' + @Index_Name
                        END
                    else
                        BEGIN -- partitioned index
                        set @SQL = N'alter index [' + @Index_Name + '] on [' + @Database_Name + '].' + @Owner + '.' + @Table_Name + '
                        REBUILD Partition = ' + cast(@Partition_Number as varchar(20)) + ';'
                        set @MSG = 'Rebuilding Index: ' + @Table_Name + '.' + @Owner + '.' + @Index_Name + ' Partition: ' + cast(@Partition_Number as varchar(20))
                        END
                END    -- END Rebuild Index
            ELSE
                BEGIN -- Reorganize Index
                    IF  @Partition_Count = 1
                        BEGIN -- non partitioned index
                        set @SQL = N'alter index [' + @Index_Name + '] on [' + @Database_Name + '].' + @Owner + '.' + @Table_Name + '
                        REORGANIZE;'
                        set @MSG = 'Reorganizing Index: ' + @Table_Name + '.' + @Owner + '.' + @Index_Name
                        END
                    ELSE
                        BEGIN -- partitioned index
                        set @SQL = N'alter index [' + @Index_Name + '] on [' + @Database_Name + '].' + @Owner + '.' + @Table_Name + '
                        REORGANIZE Partition = ' + cast(@Partition_Number as varchar(20)) + ';'
                        set @MSG = 'Reorganizing Index: ' + @Table_Name + '.' + @Owner + '.' + @Index_Name + ' Partition: ' + cast(@Partition_Number as varchar(20))
                        END
                END  -- End Reorganize Index
        END -- END DEFAULT    

    print @MSG
    BEGIN TRY
        exec dbo.sp_executesql @statement = @SQL
    END TRY
    BEGIN CATCH
        GOTO ERRORS
    END CATCH
    UPDATE AdminData.dbo.TableStats
        set Defrag_Time = getdate()
        where TblStatsID = @TblStatsID
    Print 'Status = OK'
    Print ' '
    
    FETCH NEXT
    FROM FragmentedTables
    INTO @TblStatsID, @Database_Name, @Owner, @Table_Name, @Index_Name, @object_ID, @index_ID, @Partition_Number, @AVG_Fragmentation_IN_Percent  

END -- While
GOTO ENDIT
ERRORS:
          Print 'Program failed'
        print 'ErrorNumber: ' + Cast(ERROR_NUMBER() as varchar(50))
        print 'ERROR_SEVERITY:' + Cast(ERROR_SEVERITY() as varchar(100))
        print 'ErrorState:' + cast(ERROR_STATE() as varchar(100))
        print 'ErrorProcedure:' + cast(ERROR_PROCEDURE() as varchar(200))
        print 'ErrorLine:' + cast(ERROR_LINE() as varchar(200))
        Print 'ErrorMessage:' + cast(ERROR_MESSAGE() as varchar(2000))
        set @ERR = ERROR_NUMBER()
ENDIT:
CLOSE FragmentedTables
DEALLOCATE FragmentedTables

RETURN @ERR    

Script to call Acme_Database_Optimize
USE [AdminData]
GO
DECLARE    @return_value int
EXEC        @return_value = [dbo].[Acme_Database_Optimize]
        @Fragmax = 10,
        @FragType = N'DEFAULT',    -- Lock = dbcc dbreindex, NoLock = dbcc indexdefrag, DEFAULT = > 30 index else reorganize    
        @FastFlag = N'N',        -- DBCC SHOWCONTIG with Fast option Less data recorded but faster.
        @DatabaseList = N'HR,Sales' ,     -- Comma separated list of databases to be re-indexed
        @UpdateUsage = N'N',        -- will execute DBCC UPDATEUSAGE(@DBName)
        @UpdateStatistics = N'Y'    -- will execute  exec sp_updatestats


PRINT 'Acme_Database_Optimize has completed. Return Code = ' + cast(@return_value as varchar(20))