Opening the original article.
Opening original article…
You’ll be redirected to the original source shortly.
Everybody wants to know about index fragmentation
It is an inescapable vortex of malaise and confusion. Like that swamp in The Neverending Story that killed the horse. Sorry if you haven’t seen that movie. The horse wasn’t that cool, anyway.
Neither is index fragmentation, but it’s not worth losing sleep over. Or a horse.
I see a lot of people messing with the fill factor of their indexes. Sometimes you gotta. If you use GUIDs for a clustering key, for example. If you don’t lower fill factor from 100, you’re going to spend a lot of time splitting pages when you insert records. GUIDs are hard to run out of, but they’re even harder to put in order.
Setting fill factor under 100 tells SQL to leave free space on index pages at the leaf level for new records to be add to. If you don’t, and a record needs to be added to a page, it will do about a 50/50 split to two other pages.
When does it hurt?
Like most things, not at first. To prove it, let’s rebuild an index at different fill factors, and insert some fragmentation information into a table. It’s pretty easy. Create a table, rebuild the index, insert record to table. I could have done this in a loop, but I’m kind of lazy today.
Transact-SQL
1234567891011121314151617181920212223242526272829303132333435363738394041 | CREATE TABLE dbo.[FillFactor] ( TableName NVARCHAR(128) NULL, IndexName sysname NULL, index_type_desc NVARCHAR(60) NULL, avg_fragmentation_in_percent FLOAT NULL, page_count BIGINT NULL, fill_factor TINYINT NOT NULL ) ON [PRIMARY];GO INSERT dbo.[FillFactor] ( TableName, IndexName, index_type_desc, avg_fragmentation_in_percent, page_count, fill_factor )SELECT OBJECT_NAME(ddips.object_id) AS TableName, i.name AS IndexName, ddips.index_type_desc, ddips.avg_fragmentation_in_percent, ddips.page_count, i.fill_factorFROM sys.dm_db_index_physical_stats(DB_ID(N'StackOverflow'), OBJECT_ID('dbo.Votes'), NULL, NULL, 'LIMITED') AS ddipsJOIN sys.tables AS t ON t.object_id = ddips.object_idJOIN sys.indexes AS i ON i.object_id = ddips.object_id AND i.index_id = ddips.index_id; ALTER INDEX PK_Votes ON dbo.Votes REBUILD WITH ( FILLFACTOR = 100 );ALTER INDEX PK_Votes ON dbo.Votes REBUILD WITH ( FILLFACTOR = 80 );ALTER INDEX PK_Votes ON dbo.Votes REBUILD WITH ( FILLFACTOR = 60 );ALTER INDEX PK_Votes ON dbo.Votes REBUILD WITH ( FILLFACTOR = 40 );ALTER INDEX PK_Votes ON dbo.Votes REBUILD WITH ( FILLFACTOR = 20 ); SELECT ff.TableName, ff.IndexName, ff.index_type_desc, ff.avg_fragmentation_in_percent, ff.page_count, ff.fill_factorFROM dbo.[FillFactor] AS ffORDER BY ff.fill_factor DESC; |
Put on your thinking cap. Fragmentation percent doesn’t budge. Granted, we rebuilt the index, so that’s expected. But look at page counts. Every time we reduce fill factor, page count gets higher. Why does that matter? Each page is 8kb. The more pages are in your index, the more you’re reading from disk into memory. The lower your fill factor, the more blank space you’re reading from disk into memory. You could be wasting a lot of unnecessary space both on disk and in memory by lowering fill factor.
Space Age Love Song is probably the best Flock of Seagulls song, just so you know.
Let’s do math!
Because everyone loves math. Let’s take page count, multiply it by 8, and then divide it by 1024 twice to get the size of each index in GB.
Transact-SQL
123456789 | SELECT ff.TableName, ff.IndexName, ff.index_type_desc, ff.avg_fragmentation_in_percent, ff.page_count, ff.fill_factor, ( ff.page_count * 8. ) / 1024. / 1024. AS SizeGBFROM dbo.[FillFactor] AS ffORDER BY ff.fill_factor DESC; |
Even reducing this to 80 takes up about an extra 600MB. That can really add up. Granted, disk and memory are cheap, but they’re not infinite. Especially if you’re on Standard Edition.
Ack! Numbers!
It’s in everything
It’s not just queries that reading extra pages can slow down. DBCC CHECKDB, backups, and index and statistics maintenance all have to deal with all those pages. Lowering fill factor without good reason puts you in the same boat as index fragmentation does, except regular maintenance won’t “fix” the problem.