In my TSQL2sDay index summary post, that I’d be writing a few posts on the information that is contained in sys.dm_db_index_operational_stats. The posts are the following:
- Index Black Ops Part 1 – Locking and Blocking
- Index Black Ops Part 2 – Page IO Latch, Page Latch
- Index Black Ops Part 3 – Index Usage
- Index Black Ops Part 4 – Index Overhead and Maintenance
- Index Black Ops Part 5 – Page Splits
- Index Black Ops Part 6 – Fill Factor vs. Page Splits
We are now to the last post of this series. In this post we will be investigating the relationship between fill factor and page splits. If you aren’t familiar with page splits then take a chance to read the post Index Black Ops Part 5 – Page Splits.
Expanding An Idea
As mentioned in the last post, when data is updated on an index it can lead to page splits. I had originally intended to talk about fill factor with the last post but the length of that post dictated the need for another. So in this post we will use the ideas from that post to see the effect of FILL FACTOR on an index and sys.dm_db_index_operational_stats.
Below I am going to go through three pairs of scripts. Each pair will cover a different fill factor level to see how adjusting it will affect the indexes and page splits that occur. All of the scripts will be using tempdb.
The first script in each pair will perform the following steps:
- Create dbo.FillFactorTable in tempdb with a clustered and non-clustered index.
- Insert 10,000 rows into dbo.FillFactorTable.
- Rebuild the indexes on dbo.FillFactorTable. This will apply the fill factor assigned to the index to the index.
- Query sys.dm_db_index_operational_stats for leaf and non-leaf allocations.
- Query sys.dm_db_index_physical_stats for fragmentation and page count.
After that, the second script in each pair will perform the following steps:
- Update dbo.FillFactorTable to possibly cause page splits. That’s called foreshadowing.
- Query sys.dm_db_index_operational_stats for leaf and non-leaf allocations.
- Query sys.dm_db_index_physical_stats for fragmentation and page count.
Through these scripts, we should be able to demonstrate the effect of fill factor on the occurrence of page splits.
Starting at 0 or 100
Out of the box, SQL Server defaults indexes to a fill factor of 0. This is the same as having a fill factor of 100. To start the demonstrations, run the first script with fill factor 100.
USE tempdb;
GO
IF OBJECT_ID('dbo.FillFactorTable') IS NOT NULL
DROP TABLE dbo.FillFactorTable;
CREATE TABLE dbo.FillFactorTable
(
ID int
,Value varchar(200)
,CreateDate datetime
,CONSTRAINT PK_FillFactorTable PRIMARY KEY (ID)
WITH FILLFACTOR = 100
);
CREATE INDEX IX_FillFactorTable_Value ON dbo.FillFactorTable (Value)
WITH FILLFACTOR = 100;
WITH
l0 AS (SELECT 0 AS C UNION ALL SELECT 0),
l1 AS (SELECT 0 AS C FROM L0 AS A CROSS JOIN L0 AS B),
l2 AS (SELECT 0 AS C FROM l1 AS A CROSS JOIN l1 AS B),
l3 AS (SELECT 0 AS C FROM l2 AS A CROSS JOIN l2 AS B),
l4 AS (SELECT 0 AS C FROM l3 AS A CROSS JOIN l3 AS B),
l5 AS (SELECT 0 AS C FROM l4 AS A CROSS JOIN l4 AS B),
nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM l5)
INSERT INTO dbo.FillFactorTable
SELECT TOP (10000)
n, REPLICATE('X',100), GETDATE()
FROM nums
ORDER BY 2;
ALTER INDEX PK_FillFactorTable ON dbo.FillFactorTable REBUILD;
ALTER INDEX IX_FillFactorTable_Value ON dbo.FillFactorTable REBUILD;
SELECT
OBJECT_SCHEMA_NAME(ios.object_id) + '.' + OBJECT_NAME(ios.object_id) as table_name
,i.name as index_name
,leaf_allocation_count
,nonleaf_allocation_count
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('dbo.FillFactorTable'),NULL, NULL) ios
INNER JOIN sys.indexes i ON i.object_id = ios.object_id AND i.index_id = ios.index_id;
SELECT
OBJECT_SCHEMA_NAME(ips.object_id) + '.' + OBJECT_NAME(ips.object_id) as table_name
,ips.avg_fragmentation_in_percent
,ips.fragment_count
,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.FillFactorTable'),NULL, NULL, 'LIMITED') ips
The results of the query are below. This shows that there haven’t been any page allocations since the index rebuild. And the rebuild pretty much removed all fragmentation from the index. Take note of the page count for the indexes, as we’ll see this increase in the later scripts.
With that baseline, run the second script listed below:
UPDATE dbo.FillFactorTable
SET Value = REPLICATE('X',200)
WHERE ID%5 =1
SELECT
OBJECT_SCHEMA_NAME(ios.object_id) + '.' + OBJECT_NAME(ios.object_id) as table_name
,i.name as index_name
,leaf_allocation_count
,nonleaf_allocation_count
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('dbo.FillFactorTable'),NULL, NULL) ios
INNER JOIN sys.indexes i ON i.object_id = ios.object_id AND i.index_id = ios.index_id;
SELECT
OBJECT_SCHEMA_NAME(ips.object_id) + '.' + OBJECT_NAME(ips.object_id) as table_name
,ips.avg_fragmentation_in_percent
,ips.fragment_count
,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.FillFactorTable'),NULL, NULL, 'LIMITED') ips
Here are the results from the second script.
As is shown, there is significant fragmentation on the first index. This fragmentation was caused by page splits. Also, both indexes have a number of pages that have been allocated to the index. Based on the previous post, these allocations are related to the page splits that occurred.
Drop Down to 80
One of the strategies that can be employed to prevent page splits is to reduce the fill factor on an index. Fill factor can be adjust on both clustered and non-clustered indexes. Let’s do that now with the table.
USE tempdb;
GO
IF OBJECT_ID('dbo.FillFactorTable') IS NOT NULL
DROP TABLE dbo.FillFactorTable;
CREATE TABLE dbo.FillFactorTable
(
ID int
,Value varchar(200)
,CreateDate datetime
,CONSTRAINT PK_FillFactorTable PRIMARY KEY (ID)
WITH FILLFACTOR = 80
);
CREATE INDEX IX_FillFactorTable_Value ON dbo.FillFactorTable (Value)
WITH FILLFACTOR = 80;
WITH
l0 AS (SELECT 0 AS C UNION ALL SELECT 0),
l1 AS (SELECT 0 AS C FROM L0 AS A CROSS JOIN L0 AS B),
l2 AS (SELECT 0 AS C FROM l1 AS A CROSS JOIN l1 AS B),
l3 AS (SELECT 0 AS C FROM l2 AS A CROSS JOIN l2 AS B),
l4 AS (SELECT 0 AS C FROM l3 AS A CROSS JOIN l3 AS B),
l5 AS (SELECT 0 AS C FROM l4 AS A CROSS JOIN l4 AS B),
nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM l5)
INSERT INTO dbo.FillFactorTable
SELECT TOP (10000)
n, REPLICATE('X',100), GETDATE()
FROM nums
ORDER BY 2;
ALTER INDEX PK_FillFactorTable ON dbo.FillFactorTable REBUILD;
ALTER INDEX IX_FillFactorTable_Value ON dbo.FillFactorTable REBUILD;
SELECT
OBJECT_SCHEMA_NAME(ios.object_id) + '.' + OBJECT_NAME(ios.object_id) as table_name
,i.name as index_name
,leaf_allocation_count
,nonleaf_allocation_count
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('dbo.FillFactorTable'),NULL, NULL) ios
INNER JOIN sys.indexes i ON i.object_id = ios.object_id AND i.index_id = ios.index_id;
SELECT
OBJECT_SCHEMA_NAME(ips.object_id) + '.' + OBJECT_NAME(ips.object_id) as table_name
,ips.avg_fragmentation_in_percent
,ips.fragment_count
,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.FillFactorTable'),NULL, NULL, 'LIMITED') ips
Once the script above executes, the results below will be displayed.
These are similar to the results for fill factor 100. The chief difference in the results is the number of pages allocated to each index. The clustered index increased from 157 to 193 pages and the non-clustered index increased from 141 to 195 pages. The number of pages increase because only 80% of the page was filled with data.
We’ve got extra space in the index now. Is it enough for the updates? Let’s run the second script in this pair to see what happens.
UPDATE dbo.FillFactorTable
SET Value = REPLICATE('X',200)
WHERE ID%5 =1
SELECT
OBJECT_SCHEMA_NAME(ios.object_id) + '.' + OBJECT_NAME(ios.object_id) as table_name
,i.name as index_name
,leaf_allocation_count
,nonleaf_allocation_count
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('dbo.FillFactorTable'),NULL, NULL) ios
INNER JOIN sys.indexes i ON i.object_id = ios.object_id AND i.index_id = ios.index_id;
SELECT
OBJECT_SCHEMA_NAME(ips.object_id) + '.' + OBJECT_NAME(ips.object_id) as table_name
,ips.avg_fragmentation_in_percent
,ips.fragment_count
,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.FillFactorTable'),NULL, NULL, 'LIMITED') ips
The results for this script are starkly different from the results with a fill factor of 100. In this case, the lower fill factor resulted in no allocations for the clustered index and significantly less for the non-clustered index. And as mentioned these allocations would have been due to page splits.
The other item to note, the page count for the clustered index did not increase. But not only did it not increase, it is also much lower that the post-UPDATE page count for fill factor 100. This important because one of the things that can be an issue with fill factor is the additional amount of space required to store the index.
Drop Down to 60
In this last pair of examples, we’ll set the fill factor to 60. As you may guess, this fill factor will also prevent page splits. But it will also use an excessive number of pages for each of the indexes.
USE tempdb;
GO
IF OBJECT_ID('dbo.FillFactorTable') IS NOT NULL
DROP TABLE dbo.FillFactorTable;
CREATE TABLE dbo.FillFactorTable
(
ID int
,Value varchar(200)
,CreateDate datetime
,CONSTRAINT PK_FillFactorTable PRIMARY KEY (ID)
WITH FILLFACTOR = 60
);
CREATE INDEX IX_FillFactorTable_Value ON dbo.FillFactorTable (Value)
WITH FILLFACTOR = 60;
WITH
l0 AS (SELECT 0 AS C UNION ALL SELECT 0),
l1 AS (SELECT 0 AS C FROM L0 AS A CROSS JOIN L0 AS B),
l2 AS (SELECT 0 AS C FROM l1 AS A CROSS JOIN l1 AS B),
l3 AS (SELECT 0 AS C FROM l2 AS A CROSS JOIN l2 AS B),
l4 AS (SELECT 0 AS C FROM l3 AS A CROSS JOIN l3 AS B),
l5 AS (SELECT 0 AS C FROM l4 AS A CROSS JOIN l4 AS B),
nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM l5)
INSERT INTO dbo.FillFactorTable
SELECT TOP (10000)
n, REPLICATE('X',100), GETDATE()
FROM nums
ORDER BY 2;
ALTER INDEX PK_FillFactorTable ON dbo.FillFactorTable REBUILD;
ALTER INDEX IX_FillFactorTable_Value ON dbo.FillFactorTable REBUILD;
SELECT
OBJECT_SCHEMA_NAME(ios.object_id) + '.' + OBJECT_NAME(ios.object_id) as table_name
,i.name as index_name
,leaf_allocation_count
,nonleaf_allocation_count
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('dbo.FillFactorTable'),NULL, NULL) ios
INNER JOIN sys.indexes i ON i.object_id = ios.object_id AND i.index_id = ios.index_id;
SELECT
OBJECT_SCHEMA_NAME(ips.object_id) + '.' + OBJECT_NAME(ips.object_id) as table_name
,ips.avg_fragmentation_in_percent
,ips.fragment_count
,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.FillFactorTable'),NULL, NULL, 'LIMITED') ips
After running the script above, the results below will display. These are similar to the previous executions of the these scripts. The exception this that the page counts for both indexes are substantially higher than before. In fact, the non-clustered index with a fill factor of 60 is large than it was post-UPDATE when the fill factor was 100 (233 vs. 195 pages).
We’ve seen the results before the anticipated page splits from the update. Now let’s run the update to see what happens.
UPDATE dbo.FillFactorTable
SET Value = REPLICATE('X',200)
WHERE ID%5 =1
SELECT
OBJECT_SCHEMA_NAME(ios.object_id) + '.' + OBJECT_NAME(ios.object_id) as table_name
,i.name as index_name
,leaf_allocation_count
,nonleaf_allocation_count
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('dbo.FillFactorTable'),NULL, NULL) ios
INNER JOIN sys.indexes i ON i.object_id = ios.object_id AND i.index_id = ios.index_id;
SELECT
OBJECT_SCHEMA_NAME(ips.object_id) + '.' + OBJECT_NAME(ips.object_id) as table_name
,ips.avg_fragmentation_in_percent
,ips.fragment_count
,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.FillFactorTable'),NULL, NULL, 'LIMITED') ips
With the script executed, the results should be:
In these script having fill factor at 60 continued to help prevent page splits. On the clustered index there were no page splits or leaf allocations. The non-clustered index had the same number of allocations as when the fill factor was 80.
With the fill factor this low, though, the value of using fill factor has been diminished. It is diminished because for the same data, the index is using up to 25% more tables. In this case the amount of space is not a huge deal. But consider an index that is over a GB in size or larger. This extra space is just sitting in the database and it’s not providing any value.
It is important to select a fill factor that isn’t too high, such as 100. And it is also important to select a fill factor that isn’t too low – as a fill factor of 60 is in this case.
Moar Information
Fill factor and page splits have a nicely tied relationship. One will affect the other and information in sys.dm_db_operational stats can be used to watch this activity. This post didn’t exactly expand on the information I’ve already provided on the leaf and non-lead allocation columns. It did, hopefully, walk-through a case where this information could be used.
One thing we haven’t looked at is the effect on performance due to the page splits and increased numbers of pages for the indexes. Both of these will have an effect on performance but much more time and space will be needed to go through that exercise.
For additional information on Fill Factor, I’d recommend reading Paul Randal’s (blog | twitter) latest article on SQL Mag. It covers more on what I covered here.
Hopefully these posts on sys.dm_db_operational stats have proven to be useful and informative. Please leave comments if there are other aspects of this DMV you’d like to see covered.
Related posts:
- Index Black Ops Part 5 – Page Splits
- Index Black Ops Part 2 – Page IO Latch, Page Latch
- Index Black Ops Part 4 – Index Overhead and Maintenance
