Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Check Index Fragmentation Based on Page Fullness and Fill Factor. Inspired by: https://www.erikdarlingdata.com/2019/10/because-your-index-maintenance-script-is-measuring-the-wrong-thing/
/**************************************************************************
Check Index Fragmentation based on Page Fullness and Fill Factor
***************************************************************************
Author: Eitan Blumin | https://www.eitanblumin.com
Version History:
2020-01-07 First version
Description:
This script was inspired by Erik Darling's blog post here:
https://www.erikdarlingdata.com/2019/10/because-your-index-maintenance-script-is-measuring-the-wrong-thing/
-----------------
!!! WARNING !!!
-----------------
This script uses "SAMPLED" mode for checking fragmentation,
which can potentially cause significant IO stress on a
large production server.
Use at your own risk!
**************************************************************************/
DECLARE
@MinPageCount INT = 1000
,@MinUserUpdates INT = 1000
,@MinFragmentationToReduceFillFactor100 INT = 50
,@MaxFragmentationToSetFillFactor100 INT = 20
,@MaxSpaceUsedForFillFactor100 INT = 90
,@MaxSpaceUsedForFillFactorLessThan100 INT = 75
,@OnlineRebuild BIT = 0
,@SortInTempDB BIT = 0
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT, ARITHABORT, XACT_ABORT ON;
IF @OnlineRebuild = 1 AND CONVERT(nvarchar,SERVERPROPERTY('Edition')) NOT LIKE N'Enterprise%' AND CONVERT(nvarchar,SERVERPROPERTY('Edition')) NOT LIKE N'Developer%'
BEGIN
RAISERROR(N'Online Rebuild is not supported in this SQL Server edition.',16,1);
GOTO Quit;
END
DECLARE @CommandTemplate NVARCHAR(MAX)
SET @CommandTemplate = N'RAISERROR(N''{DATABASE}.{TABLE} - {INDEX}'',0,1) WITH NOWAIT;
ALTER INDEX {INDEX} ON {TABLE}
REBUILD WITH(SORT_IN_TEMPDB='
+ CASE WHEN @SortInTempDB = 1 THEN N'ON' ELSE N'OFF' END
+ N', ONLINE=' + CASE WHEN @OnlineRebuild = 1 THEN N'ON' ELSE N'OFF' END
+ N'{FILLFACTOR});
GO'
SELECT
DatabaseName = DB_NAME()
, SchemaName = OBJECT_SCHEMA_NAME(t.object_id)
, TableName = t.name
, IndexName = ix.name
, Remediation =
REPLACE(REPLACE(REPLACE(REPLACE(@CommandTemplate
, N'{DATABASE}', QUOTENAME(DB_NAME()))
, N'{TABLE}', QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + N'.' + QUOTENAME(t.name))
, N'{INDEX}', QUOTENAME(ix.name))
, N'{FILLFACTOR}',
CASE
WHEN ix.fill_factor = 0 AND ps.avg_fragmentation_in_percent >= @MinFragmentationToReduceFillFactor100 THEN N', FILLFACTOR=90'
WHEN ix.fill_factor > 0 AND ps.avg_fragmentation_in_percent <= @MaxFragmentationToSetFillFactor100 THEN N', FILLFACTOR=100'
ELSE N'' END)
, ix.fill_factor
, RowsCount = (SELECT SUM(rows) FROM sys.partitions AS p WHERE p.object_id = t.object_id AND p.index_id = ix.index_id)
, us.user_updates
, us.last_user_update
, ps.avg_fragmentation_in_percent
, ps.avg_page_space_used_in_percent
, ps.record_count
, ps.page_count
, ps.compressed_page_count
, t.object_id
, ix.index_id
, ps.partition_number
FROM sys.dm_db_index_usage_stats AS us
INNER JOIN sys.tables AS t
ON us.object_id = t.object_id
INNER JOIN sys.indexes AS ix
ON ix.object_id = t.object_id
AND ix.index_id = us.index_id
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), t.object_id, ix.index_id, NULL, 'SAMPLED') AS ps
WHERE us.database_id = DB_ID()
AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
AND t.is_ms_shipped = 0
AND us.user_updates >= @MinUserUpdates
AND ps.page_count >= @MinPageCount
AND
(
ps.avg_page_space_used_in_percent <= @MaxSpaceUsedForFillFactorLessThan100
OR
(
ix.fill_factor = 0
AND ps.avg_page_space_used_in_percent <= @MaxSpaceUsedForFillFactor100
AND ps.avg_fragmentation_in_percent >= @MinFragmentationToReduceFillFactor100
)
)
Quit:
@EitanBlumin

This comment has been minimized.

Copy link
Owner Author

EitanBlumin commented Jan 9, 2020

Relevant post here by Mike Byrd with an interesting algorithm implementing an "incremental" change in fill factor based on fragmentation and extended events monitoring page splits:
https://www.sqlservercentral.com/articles/a-self-tuning-fill-factor-technique-for-sql-server-part-1

It should be possible to implement something similar based on page fullness instead, as an alternative to the simple 100-90 fill factor algorithm that I'm currently using.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.