The following example is an extention of item D above. The following items were added:
- Setting threshold variables at the top of the script.
- Defining an optional fill factor to use when rebuilding.
- Optionally displaying a report of intentions rather than executing code.
- Locking hints to reduce unneeded overhead.
- A table variable rather than temp table, to reduce overhead.
All of the notes from item D still apply otherwise. The script is initially configured to display a report only.
-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON
-- =======================================================
-- || Configuration variables:
-- || - 10 is an arbitrary decision point at which to
-- || reorganize indexes.
-- || - 30 is an arbitrary decision point at which to
-- || switch from reorganizing, to rebuilding.
-- || - 0 is the default fill factor. Set this to a
-- || a value from 1 to 99, if needed.
-- =======================================================
DECLARE @reorg_frag_thresh float SET @reorg_frag_thresh = 10.0
DECLARE @rebuild_frag_thresh float SET @rebuild_frag_thresh = 30.0
DECLARE @fill_factor tinyint SET @fill_factor = 80
DECLARE @report_only bit SET @report_only = 1
-- Variables required for processing.
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)
DECLARE @intentions nvarchar(4000)
DECLARE @table_var TABLE(
objectid int,
indexid int,
partitionnum int,
frag float
)
-- Conditionally select tables and indexes from the
-- sys.dm_db_index_physical_stats function and
-- convert object and index IDs to names.
INSERT INTO
@table_var
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 (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE
[avg_fragmentation_in_percent] > @reorg_frag_thresh AND
index_id > 0
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR
SELECT * FROM @table_var
-- 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 WITH (NOLOCK)
JOIN sys.schemas as s WITH (NOLOCK)
ON s.[schema_id] = o.[schema_id]
WHERE
o.[object_id] = @objectid
SELECT
@indexname = QUOTENAME([name])
FROM
sys.indexes WITH (NOLOCK)
WHERE
[object_id] = @objectid AND
[index_id] = @indexid
SELECT
@partitioncount = count (*)
FROM
sys.partitions WITH (NOLOCK)
WHERE
[object_id] = @objectid AND
[index_id] = @indexid
-- Build the required statement dynamically based on options and index stats.
SET @intentions =
@schemaname + N'.' +
@objectname + N'.' +
@indexname + N':' + CHAR(13) + CHAR(10)
SET @intentions =
REPLACE(SPACE(LEN(@intentions)), ' ', '=') + CHAR(13) + CHAR(10) +
@intentions
SET @intentions = @intentions +
N' FRAGMENTATION: ' + CAST(@frag AS nvarchar) + N'%' + CHAR(13) + CHAR(10)
IF @frag < @rebuild_frag_thresh BEGIN
SET @intentions = @intentions +
N' OPERATION: REORGANIZE' + CHAR(13) + CHAR(10)
SET @command =
N'ALTER INDEX ' + @indexname +
N' ON ' + @schemaname + N'.' + @objectname +
N' REORGANIZE'
END
IF @frag >= @rebuild_frag_thresh BEGIN
SET @intentions = @intentions +
N' OPERATION: REBUILD' + CHAR(13) + CHAR(10)
SET @command =
N'ALTER INDEX ' + @indexname +
N' ON ' + @schemaname + N'.' + @objectname +
N' REBUILD'
END
IF @partitioncount > 1 BEGIN
SET @intentions = @intentions +
N' PARTITION: ' + CAST(@partitionnum AS nvarchar(10)) + CHAR(13) + CHAR(10)
SET @command = @command +
N' PARTITION=' + CAST(@partitionnum AS nvarchar(10))
END
IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100 BEGIN
SET @intentions = @intentions +
N' FILL FACTOR: ' + CAST(@fill_factor AS nvarchar) + CHAR(13) + CHAR(10)
SET @command = @command +
N' WITH (FILLFACTOR = ' + CAST(@fill_factor AS nvarchar) + ')'
END
-- Execute determined operation, or report intentions
IF @report_only = 0 BEGIN
SET @intentions = @intentions + N' EXECUTING: ' + @command
PRINT @intentions
EXEC (@command)
END ELSE BEGIN
PRINT @intentions
END
END
-- Close and deallocate the cursor.
CLOSE partitions
DEALLOCATE partitions
GO