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.
Hi Tracy, great post, very informative and nicely done. Thanks
Hi Tracy – I do not see minPageCount column in IndexDefragConfig table. Is it suppose to be ‘UseFillFactor’ ?
SELECT
@min_page_count = minPageCount,
@SplitPagePct = SplitPagePct,
@MinFillFactor = MinFillFactor
FROM dbo.IndexDefragConfig;
Thanks
Sorry I left that column out our config table has more columns used for other processes. I have corrected the post. The UseFillFactor still only suppose to be in the IndexDefragConfigSpecifics table.