SpDefragmentIndexes

/* IMPORTANT: Be sure to save this document on WordPad or other text-editors with a ".sql" extension */

/****** Object:  StoredProcedure [dbo].[SpDefragmentIndexes]    Script Date: 4/19/2021 12:21:57 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*---------------------------------------------------------------------------*/
/* This procedure reorganizes and rebuilds the index if the fragmentation    */
/* level is higher the given threshold.  You can set the threshold for       */
/* reorganize as well as for rebuild and script will work accordingly        */
/* Parameters:                                                               */
/* @FragmentationThresholdForReorganizeTableLowerLimit - Fragmentation Level */
/* lower threshold to check for reorganizing the table, if the fragmentation */
/* is higher than this level, it will be considered for reorganize.          */
/* @FragmentationThresholdForRebuildTableLowerLimit - Fragmentation Level    */
/* lower threshold to check for rebuilding the table, if the fragmentation is*/
/* higher than this level, it will be considered for rebuild.                */
/* NOTES: Print statements are all queued up and don't show up until the     */
/* entire script is printed. However, there is an alternative to Printing    */
/* messages.                                                                 */
/* You can raise an error that isn't really an error (code of 0) and you'll  */
/* get the same effect--message will be printed immediately.                 */
/*---------------------------------------------------------------------------*/
ALTER PROC [dbo].[SpDefragmentIndexes](@FragmentationThresholdForReorganizeTableLowerLimit varchar(10), @FragmentationThresholdForRebuildTableLowerLimit varchar(10))
AS
DECLARE @cmd nvarchar(1000)
DECLARE @Table varchar(255)
DECLARE @SchemaName varchar(255)
DECLARE @IndexName varchar(255)
DECLARE @AvgFragmentationInPercent decimal
DECLARE @fillfactor int
DECLARE @minFactor decimal
DECLARE @maxFactor decimal
DECLARE @tempFactor decimal

SET NOCOUNT ON

SET @fillfactor = 90

-- Factors for Rebuilding/Reorganizing the Index; Make sure both are
-- between 10, 100 % */
SET @minFactor = 10.0
SET @maxFactor = 100.0

-- Rebuild Limit
SET @tempFactor = CONVERT(decimal, @FragmentationThresholdForRebuildTableLowerLimit)
IF @tempFactor < @minFactor SET @tempFactor = @minFactor
ELSE IF @tempFactor > @maxFactor SET @tempFactor = @maxFactor
SET @FragmentationThresholdForRebuildTableLowerLimit = CONVERT(varchar(10), @tempFactor)

-- Reorganize Limit
SET @tempFactor = CONVERT(decimal, @FragmentationThresholdForReorganizeTableLowerLimit)
IF @tempFactor < @minFactor SET @tempFactor = @minFactor
ELSE IF @tempFactor > @maxFactor SET @tempFactor = @maxFactor
SET @FragmentationThresholdForReorganizeTableLowerLimit =  CONVERT(varchar(10), @tempFactor)

-- Ensure the temporary table does not exist
IF (SELECT OBJECT_ID('tempdb..#FragmentedTableList')) IS NOT NULL
DROP TABLE #FragmentedTableList;

SELECT OBJECT_NAME(IPS.OBJECT_ID) AS [TableName], avg_fragmentation_in_percent, SI.name [IndexName],
schema_name(ST.schema_id) AS [SchemaName], 0 AS IsProcessed into #FragmentedTableList
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) IPS
JOIN sys.tables ST WITH (nolock) ON IPS.OBJECT_ID = ST.OBJECT_ID
JOIN sys.indexes SI WITH (nolock) ON IPS.OBJECT_ID = SI.OBJECT_ID AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0 AND SI.name IS NOT NULL
AND avg_fragmentation_in_percent >= CONVERT(decimal, @FragmentationThresholdForReorganizeTableLowerLimit)
ORDER BY avg_fragmentation_in_percent DESC


WHILE EXISTS ( SELECT 1 FROM #FragmentedTableList WHERE IsProcessed = 0 )
BEGIN

SELECT TOP 1 @Table = TableName, @AvgFragmentationInPercent = avg_fragmentation_in_percent,
@SchemaName = SchemaName, @IndexName = IndexName
FROM #FragmentedTableList
WHERE IsProcessed = 0

--Reorganizing the index
IF((@AvgFragmentationInPercent >= @FragmentationThresholdForReorganizeTableLowerLimit) AND (@AvgFragmentationInPercent < @FragmentationThresholdForRebuildTableLowerLimit))
BEGIN
SET @cmd = 'ALTER INDEX [' + @IndexName + '] ON [' + RTRIM(LTRIM(@SchemaName)) + '].[' + RTRIM(LTRIM(@Table)) + '] REORGANIZE'
EXEC (@cmd)
--Print @cmd
END
--Rebuilding the index
ELSE IF (@AvgFragmentationInPercent >= @FragmentationThresholdForRebuildTableLowerLimit )
BEGIN
SET @cmd = 'ALTER INDEX [' + @IndexName + '] ON [' + RTRIM(LTRIM(@SchemaName)) + '].[' + RTRIM(LTRIM(@Table)) + '] REBUILD WITH (FILLFACTOR = ' + CONVERT(varchar(3),@fillfactor) + ', STATISTICS_NORECOMPUTE = OFF)'
EXEC (@cmd)
--Print @cmd
END

UPDATE #FragmentedTableList SET IsProcessed = 1 WHERE TableName = @Table AND IndexName = @IndexName
END

DROP TABLE #FragmentedTableList
GO