Just sharing a cool script I stumbled across while searching for SQL Server index fragmentation scripts (Omer van Kloeten’s Blog). I’ve cleaned it up a bit and turned it into a Stored Procedure so that I can execute it as a SQL Server Agent Job (Not sure if this is a good idea or not so please leave comments).
CREATE PROC usp_FixIndexFragmentation
(
/* Use online rebuild if detected edition is Enterprise or Developer. */
@Online BIT = 1,
/* Log recommendations Only */
@LogOnly BIT = 1,
/* Specifies a percentage that indicates how full the Database Engine should
make the leaf level of each index page during index creation or alteration.
Fillfactor must be an integer value from 1 to 100. The default is 0. */
@FillFactor TINYINT = 50,
/* Specifies the scan level that is used to obtain statistics. Valid inputs are
DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED. */
@Index_physical_stats_mode VARCHAR(20) = 'Limited',
/* Total number of index or data pages. */
@Index_physical_stats_page_count BIGINT = 500,
/* Logical fragmentation for indexes, or extent fragmentation for heaps in the
IN_ROW_DATA allocation unit. The value is measured as a percentage and
takes into account multiple files */
@Avg_fragmentation_percent TINYINT = 40
)
AS
BEGIN
SET NOCOUNT ON
DEClARE @Rows BIGINT = 0
DEClARE @Counter BIGINT = 1
DECLARE @TSQLStatement NVARCHAR(MAX)
IF OBJECT_ID('tempdb..#TmpTable') IS NOT NULL
DROP TABLE #TmpTable
CREATE TABLE #TmpTable
(
RowNo BIGINT IDENTITY(1,1), TSQLStatement VARCHAR(MAX)
)
IF NOT EXISTS
(
SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LogTable]')
AND type in (N'U')
)
BEGIN
CREATE TABLE [dbo].[LogTable]
(
[TSQLStatement] [varchar](max) NULL,
[FRAGMENTATION] [float] NULL,
[Executed] [datetime] NULL,
[ExecutedBy] [varchar](100) NULL
) ON [PRIMARY]
END
INSERT INTO LogTable
OUTPUT INSERTED.TSQLStatement INTO #TmpTable
SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +
CASE
WHEN ps.avg_fragmentation_in_percent @Avg_fragmentation_percent THEN
CASE
WHEN @Online = 1 AND
(
CAST(SERVERPROPERTY('Edition') AS VARCHAR) LIKE 'Enterprise%'
OR
CAST(SERVERPROPERTY('Edition') AS VARCHAR) LIKE 'Developer%'
OR
CAST(SERVERPROPERTY('Edition') AS VARCHAR) LIKE 'Evaluation%'
)
THEN 'REBUILD WITH (ONLINE = ON, FILLFACTOR = '
+ CAST(@FillFactor AS VARCHAR(10)) + ') '
ELSE 'REBUILD WITH (FILLFACTOR = '
+ CAST(@FillFactor AS VARCHAR(10)) + ') '
END
ELSE 'REORGANIZE '
END +
CASE
WHEN pc.partition_count 1
THEN ' PARTITION = ' + CAST(ps.partition_number AS NVARCHAR(MAX))
ELSE ''
END,
PS.AVG_FRAGMENTATION_IN_PERCENT, GETDATE(), SUSER_NAME()
FROM sys.indexes AS ix INNER JOIN sys.tables t
ON t.object_id = ix.object_id INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id INNER JOIN
(
SELECT object_id, index_id, avg_fragmentation_in_percent, partition_number
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL,
@Index_physical_stats_mode)
WHERE page_count @Index_physical_stats_page_count
) ps
ON t.object_id = ps.object_id AND ix.index_id = ps.index_id INNER JOIN
(
SELECT object_id, index_id, COUNT(DISTINCT partition_number) AS partition_count
FROM sys.partitions
GROUP BY object_id, index_id
) pc
ON t.object_id = pc.object_id AND ix.index_id = pc.index_id
WHERE ps.avg_fragmentation_in_percent 10 AND ix.name IS NOT NULL
ORDER BY ps.avg_fragmentation_in_percent DESC
SET @Rows = @@ROWCOUNT
IF @LogOnly = 0
BEGIN
WHILE (@Counter = @Rows)
BEGIN
SELECT @TSQLStatement = TSQLStatement
FROM #TmpTable
WHERE RowNo = @Counter
EXECUTE sp_executesql @TSQLStatement
SET @Counter += 1
END
END
SET NOCOUNT OFF
END
