USE [somedatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_Fix_Indexes]
AS
BEGIN
SET NOCOUNT ON;
-- Reorganize Indexes at 10% Fragmentation
DECLARE @ReorgThreshold float;
SET @ReorgThreshold = 10.0;
-- Rebuild Indexes at 30% Fragmentation
DECLARE @RebuildThreshold float;
SET @RebuildThreshold = 30.0;
-- Only Rebuild Indexes on tables with 50 Rows
DECLARE @RowThreshold smallint;
SET @RowThreshold = 50;
DECLARE @objectId int;
DECLARE @indexId int;
DECLARE @partitionCount bigint;
DECLARE @schemaName nvarchar(300);
DECLARE @objectName nvarchar(300);
DECLARE @indexName nvarchar(300);
DECLARE @partitionNum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @pageCount int;
DECLARE @command nvarchar(4000);
BEGIN TRY
-- Get Index Statistics
SELECT object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag,
page_count AS page_count
INTO #temporary_storage
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > @ReorgThreshold
AND index_id > 0
AND page_count > @RowThreshold;
DECLARE partitions CURSOR FOR SELECT * FROM #temporary_storage;
OPEN partitions;
WHILE(@@FETCH_STATUS=0)
BEGIN
FETCH NEXT FROM partitions
INTO @objectId, @indexId, @partitionNum, @frag, @pageCount;
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;
IF @frag > @ReorgThreshold AND @frag < @RebuildThreshold
BEGIN
SET @command = N'ALTER INDEX ' + @indexName + N' ON ' + @schemaName + N'.' + @objectName + N' REORGANIZE';
IF @partitionCount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionNum AS nvarchar(10));
EXEC (@command);
END
ELSE IF @frag > @RebuildThreshold
BEGIN
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));
EXEC (@command);
END
END
CLOSE partitions;
DEALLOCATE partitions;
DROP TABLE #temporary_storage;
END TRY
BEGIN CATCH
-- do nothing, but something is wonky...
END CATCH;
END;