USE DBACHECKDB GO IF OBJECT_ID('IndexCheckTbl','U') IS NOT NULL DROP TABLE IndexCheckTbl CREATE TABLE IndexCheckTbl ( IndexCheckID INT IDENTITY (1,1) PRIMARY KEY NOT NULL ,objectid int ,indexid int ,partitioncount bigint ,schemaname nvarchar(130) ,objectname nvarchar(130) ,indexname nvarchar(130) ,partitionnum bigint ,partitions bigint ,page_count bigint ,frag float ,command nvarchar(4000) , LogDate datetime CONSTRAINT DF_IndexCheckTbllogdate DEFAULT (getdate()) ) USE GURMEN GO SET NOCOUNT ON; GO 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 @page_count bigint; DECLARE @frag float; DECLARE @command nvarchar(4000); -- Declare the cursor for the list of partitions to be processed. DECLARE partitions CURSOR FOR -- 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, page_count as PageCnt, avg_fragmentation_in_percent AS frag FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 AND page_count>100 -- Open the cursor. OPEN partitions; -- Loop through the partitions. WHILE (1=1) BEGIN; FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum,@page_count, @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 ' INSERT INTO DBACHECKDB.dbo.IndexCheckTbl (objectid, indexid, partitioncount, schemaname, objectname, indexname, partitionnum, partitions, page_count, frag, command) VALUES(@objectid, @indexid, @partitioncount, @schemaname, @objectname, @indexname, @partitionnum, @partitions, @page_count, @frag, @command) IF @frag >= 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH(FILLFACTOR = 90)' INSERT INTO DBACHECKDB.dbo.IndexCheckTbl (objectid, indexid, partitioncount, schemaname, objectname, indexname, partitionnum, partitions, page_count, frag, command) VALUES(@objectid, @indexid, @partitioncount, @schemaname, @objectname, @indexname, @partitionnum, @partitions, @page_count, @frag, @command) IF @partitioncount > 1 SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)) INSERT INTO DBACHECKDB.dbo.IndexCheckTbl (objectid, indexid, partitioncount, schemaname, objectname, indexname, partitionnum, partitions, page_count, frag, command) VALUES(@objectid, @indexid, @partitioncount, @schemaname, @objectname, @indexname, @partitionnum, @partitions, @page_count, @frag, @command) PRINT (@command); --EXECUTE (@command); SELECT 'ReportServerTempDB',N'Executed: ' + @command +' Frag:'+cast(@frag as varchar) PRINT N'Executed: ' + @command +' Frag:'+cast(@frag as varchar); END; -- Close and deallocate the cursor. CLOSE partitions; DEALLOCATE partitions; go
