Sql Server Index Rebuild

I had to tune our application database that was running on Sql Server 2005. The first step that I choose to rebuild the indexes. I used sys.dm_db_index_physical_stats view to get size and fragmentation information for the data and indexes of the specified table or view.


As data in this view are refreshed with server restart, I created a table to hold this information.

CREATE TABLE testdb.[dbo].[index_stat](
    [id] [int]  IDENTITY(1,1),
    [objectid] [int] NULL,
    [indexid] [int] NULL,
    [partitionnum] [int] NULL,
    [frag] [float] NULL,
    [start_time] [datetime] NULL,
    [completed_time] [datetime] NULL
) ON [PRIMARY]

Saving data of the above view in the table where avg_fragmentation_in_percent is greater than 10. 

use testdb
insert into testdb.dbo.index_stat([objectid],[indexid],[partitionnum],[frag])
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
FROM sys.dm_db_index_physical_stats (5, NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

Now I created the index_rebuild_proc to rebuild the indexes where fragmentation is above 30.0 and reorganize where fragmentation is between 10.0 and 30.0.

-- Ensure a USE statement has been executed first.
CREATE PROCEDURE index_rebuild_proc
AS
DECLARE @id int;
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.


-- Declare the cursor for the list of partitions to be processed.
DECLARE indexes_cur CURSOR FOR SELECT id, objectid, indexid, partitionnum, frag FROM testdb.dbo.index_stat where completed_time is NULL ;
-- Open the cursor.
OPEN indexes_cur;
-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM indexes_cur
           INTO @id, @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));
        if getdate() < cast(CONVERT(VARCHAR(20),GETDATE(),101) + ' 05:00:00' as datetime)
        BEGIN
         
            update testdb.dbo.index_stat set start_time = getdate() where id = @id;
            --EXEC (@command);
            PRINT N'Executed: ' + @command;
            update testdb.dbo.index_stat set completed_time = getdate() where id = @id;
        END
    END;
-- Close and deallocate the cursor.
CLOSE indexes_cur;
DEALLOCATE indexes_cur;
GO

This procedure will update start_time column of testdb.dbo.index_stat as soon as it starts rebuilding of that index and similarly update completed_time column of the table after completion of rebuilding process. This helps me to monitor the progress of the procedure. Again, as I am going to add this procedure in job scheduler, so I add a condition so that the process might stop as soon as server time passes 05:00 AM.


Now, execute the procedure.

exec index_rebuild_proc