Introduction

There is a lot of talk about what fill factor should be set for an index.  We know for indexes that are ever increasing the fill factor should be set to 100% but for those indexes that are on things like last name or birthday it gets a bit trickier to decide a fill factor.  We do not want to set a default across the whole server that some has said is best practice of 90% or even worse 80% but we do not want to have a lot of page splits and index fragmentation either. Having a fill factor set to low can hurt because then you are just reading empty space into memory and storing empty data on disk.  For more information on fill factor and page splits go here.

So what is the solution?  Well it really depends.  But the solution I wrote walks you through

• Setting the default fill factor
• Tracking page splits
• Lowering the fill factor

Setting Default Fill Factor

So first let’s set the default on the server to 100% by running the following T-SQL code:

EXEC sys.sp_configure N'fill factor (%)', N'100'
GO
RECONFIGURE WITH OVERRIDE
GO

Tracking Page Splits

Next we have to setup some things to track our page splits.  We use an extended events session to track page splits in a ring buffer and read that data into a table once a week for temporary usage.  We then take that data and translate into what index that page split came from and store that information for further processing.

First we setup an extended events session to track page splits. This is small light weight event session that I have running on servers that process over 30K transactions per second.

CREATE EVENT SESSION [XE_TrackPageSplits]
    ON SERVER
    ADD EVENT sqlserver.transaction_log
        ( SET collect_database_name = ( 0 )
         WHERE ( [operation] = ( 11 ))
        )
    ADD TARGET package0.ring_buffer
        ( SET max_events_limit = ( 0 ), max_memory = ( 5120 ))
    WITH (   MAX_MEMORY = 4096KB ,
             EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS ,
             MAX_DISPATCH_LATENCY = 30 SECONDS ,
             MAX_EVENT_SIZE = 0KB ,
             MEMORY_PARTITION_MODE = NONE ,
             TRACK_CAUSALITY = OFF ,
             STARTUP_STATE = ON
         );
GO

Next let’s create two tables in our DBA database to one read data in from the extended events session and the other to translate it into useful information:

CREATE TABLE [dbo].[IndexDefragPageSplitsTemp](
	[alloc_unit_id] [nvarchar](100) NULL,
	[database_id] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE dbo.[IndexDefragPageSplits](
	[RowID] [INT] IDENTITY(1,1) NOT NULL,
	[database_name] [NVARCHAR](128) NOT NULL,
	[schema_name] [NVARCHAR](128) NOT NULL,
	[table_name] [NVARCHAR](128) NOT NULL,
	[index_name] [NVARCHAR](128) NOT NULL,
	[index_id] [INT] NOT NULL,
	[fill_factor] [TINYINT] NOT NULL,
	[timestamp] [SMALLDATETIME] NOT NULL,
	[num_splits] [INT] NOT NULL,
 CONSTRAINT [PK_IndexDefragPageSplits_RowID] PRIMARY KEY CLUSTERED 
(
	[RowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE dbo.[IndexDefragPageSplits] ADD  CONSTRAINT [DF_IndexDefragPageSplits_timestamp]  DEFAULT (GETDATE()) FOR [timestamp]
GO

Next setup a job that runs every hour to run the procedure to read the data from the extended events session. This procedure reads in the allocation unit and database id for each page split in the last hour from the extended events into the IndexDefragPageSplitsTemp table. Then we use some dynamix SQL to find out what index and current fill factor the current index has and store it in table IndexDefragPageSplits.

USE [DBA]
GO
CREATE PROCEDURE [dbo].[IndexDefrag_InsertPageSplits]
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX);

TRUNCATE TABLE dbo.IndexDefragPageSplitsTemp;

INSERT INTO dbo.IndexDefragPageSplitsTemp 
	(
		alloc_unit_id,
		database_id
	)
	SELECT
		event_data_XML.value('(event/data[@name="alloc_unit_id"]/value)[1]', 'nvarchar(100)') AS alloc_unit_id,
		event_data_XML.value('(event/data[@name="database_id"]/value)[1]', 'int') AS database_id
	FROM (  SELECT
				name, 
				target_name, 
				CAST(xet.target_data AS xml) as event_data_xml
			FROM sys.dm_xe_session_targets AS xet
			INNER JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address)
			WHERE
				xe.name = 'XE_TrackPageSplits'
		) AS tab
	WHERE
		event_data_XML.value('(event/@timestamp)[1]', 'datetime') >= DATEADD(HOUR, -1, GETDATE())
		AND event_data_XML.value('(event/data[@name="database_id"]/value)[1]', 'int') > 4;

SET @SQL = (
	SELECT
	REPLACE(REPLACE(
		'INSERT INTO dbo.IndexDefragPageSplits 
			(database_name, 
			schema_name, 
			table_name, 
			index_name, 
			index_id, 
			fill_factor, 
			num_splits 
			)
			SELECT ''' + d.name + ''' AS database_name, 
				s.name as schema_name,
				o.name AS table_name,
				i.name AS index_name,
				i.index_id AS index_id, 
				i.fill_factor, ' 
				+ CAST(COUNT(*) AS NVARCHAR(10)) + ' AS num_splits
			FROM ' + d.name + '.sys.allocation_units AS au 
			JOIN ' + d.name + '.sys.partitions p ON au.container_id = p.partition_id
			JOIN ' + d.name + '.sys.objects AS o ON p.object_id = o.object_id
			JOIN ' + d.name + '.sys.schemas AS s ON s.schema_id = o.schema_id 
			JOIN ' + d.name + '.sys.indexes AS i ON o.object_id = i.object_id AND p.index_id = i.index_id
			WHERE
				o.is_ms_shipped = 0
				AND au.allocation_unit_id = ' + CAST(ps.alloc_unit_id AS NVARCHAR(17)) + '
			GROUP BY s.name,
				o.name,
				i.name,
				i.index_id, 
				i.fill_factor; ',
		CHAR(13), ''), CHAR(10), '')
	FROM dbo.IndexDefragPageSplitsTemp AS ps
	INNER JOIN sys.databases AS d
		ON ps.database_id = d.database_id
	GROUP BY
			ps.alloc_unit_id,
			ps.database_id,
			d.name
	FOR XML PATH('')
);

EXEC(@SQL);
GO

Lowering Fill Factor

Now to keep things from lowering the fill factor below a certain percentage, to set a default percent, and set the percentage of the table that has to have page splits before it lowers the fill factor I have a config table with values in them to control the process. So I’ve decided everything stays at 100% fill factor, 10% of the table would have to had page splits in a week, and the lowest fill factor that can set is 80%.

CREATE TABLE dbo.[IndexDefragConfig](
        [minPageCount] [int] NOT NULL,
	[UseFillFactor] [TINYINT] NOT NULL,
	[SplitPagePct] [TINYINT] NOT NULL,
	[MinFillFactor] [TINYINT] NULL
) ON [PRIMARY]
GO
INSERT INTO dbo.[IndexDefragConfig]
VALUES (1000, 100, 10, 80);

Then we setup a table to hold the index specific fill factor that it is being lowered to and have a procedure that runs weekly to populate the table and rebuild the index at the new fill factor. The procedure uses a cursor to go through each index in the table collected and lower the fill factor by 2% if the thresholds are met. Then it rebuilds the index with the new fill factor.

CREATE TABLE [dbo].[IndexDefragConfigSpecifics](
	[DatabaseName] [NVARCHAR](128) NULL,
	[IndexName] [NVARCHAR](128) NULL,
	[UseFillFactor] [TINYINT] NOT NULL
) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[IndexDefrag_LowerFillFactor]
AS
SET NOCOUNT ON

DECLARE @database_name nvarchar(128),
	@object_name NVARCHAR(387),
	@schema_name NVARCHAR(128),
	@table_name NVARCHAR(128),
	@index_name NVARCHAR(128),
	@index_id INT,
	@fill_factor TINYINT,
	@num_splits INT,
	@page_count INT,
	@min_page_count INT,
	@percentage DECIMAL(9,2),
	@SQL NVARCHAR(MAX),
	@SplitPagePct TINYINT,
	@MinFillFactor TINYINT,
	@fillFactorSQL_Param NVARCHAR(1000);

SELECT
	@min_page_count = minPageCount,
	@SplitPagePct = SplitPagePct,
	@MinFillFactor = MinFillFactor
FROM dbo.IndexDefragConfig;
	
DECLARE split_cursor CURSOR READ_ONLY
FOR
SELECT	database_name,
		schema_name, 
		table_name,
		index_name,
		index_id,
		SUM(num_splits) num_splits
FROM	dbo.IndexDefragPageSplits
WHERE	timestamp >= GETDATE() - 7
GROUP BY database_name,
		schema_name, 
		table_name,
		index_name,
		index_id;

OPEN split_cursor;

FETCH NEXT FROM split_cursor
	INTO @database_name,
		@schema_name,
		@table_name, 
		@index_name, 
		@index_id, 
		@num_splits;

WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		SET @object_name = @database_name + '.' + @schema_name + '.' + @table_name;

		SELECT @page_count = SUM(page_count) 
		FROM sys.dm_db_index_physical_stats(DB_ID(@database_name), OBJECT_ID(@object_name), NULL, NULL, 'LIMITED')
		WHERE index_id = @index_id;

		IF @page_count  > @min_page_count 
		BEGIN
			SELECT @percentage = @num_splits / 1.0 / @page_count * 100;

			PRINT CAST(@percentage AS NVARCHAR(max)) + ' ' + @database_name + ' ' + @object_name + ' Index: ' + @index_name;
		END

		SELECT
			@SQL =
				'SELECT @fillFactor_OUT = fill_factor
				FROM ' + @database_Name + '.sys.indexes i
				INNER JOIN ' + @database_Name + '.sys.objects o on o.object_id = i.object_id
				WHERE o.name = ''' + CAST(@table_name AS VARCHAR(128)) + '''
				AND index_id = ' + CAST(@index_ID AS VARCHAR(10)) + ';',
			@fillFactorSQL_Param = '@fillFactor_OUT INT OUTPUT';

		EXECUTE sp_executesql @SQL,
			@FillFactorSQL_Param,
			@FillFactor_OUT = @Fill_Factor OUTPUT;

		--decide percentage to insert into specifics table but not below xx%
		IF ( @percentage > @SplitPagePct AND @fill_factor > @MinFillFactor )
		BEGIN
			SET @sql = '
					MERGE dbo.IndexDefragConfigSpecifics AS target
					USING (SELECT ''' + @database_name + ''', ''' + 
						@index_name + ''') 
					AS source (DatabaseName, IndexName)
					ON (target.DatabaseName = source.DatabaseName AND target.IndexName = source.IndexName)
					WHEN MATCHED THEN 
					UPDATE SET UseFillFactor = UseFillFactor - 2
					WHEN NOT MATCHED THEN
					INSERT (DatabaseName, IndexName, UseFillFactor)
					VALUES (source.DatabaseName, source.IndexName, ' + CAST(@fill_factor AS NVARCHAR(3)) + ' - 2 );

					USE ' + @database_name + '
					GO
					ALTER INDEX ' + @index_name + ' ON ' + @schema_name + '.' + @table_name + ' REBUILD WITH FILLFACTOR = (' + CAST(@fill_factor - 2 AS NVARCHAR(3)) + ')' 

			EXEC (@sql);
		END
	END

	FETCH NEXT FROM split_cursor
	INTO
		@database_name,
		@schema_name,
		@table_name, 
		@index_name, 
		@index_id, 
		@num_splits;
END

CLOSE split_cursor;
DEALLOCATE split_cursor;
GO

Conclusion

Since setting up this system we have noticed less reorganizations and rebuilds of indexes needed after a certain amount of time past and a sweet spot was found.  The percent that the fill factor is adjusted (2) could be changed to something more custom to your system or even added to the config table to really take advantage of this across a diverse environment.