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](Saving data of the above view in the table where avg_fragmentation_in_percent is greater than 10.
[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]
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
