CODEWITHSUNDEEP
sql-serverindexing
ren
ren

Reputation: 3993

Fill factor for constantly growing table

I have two indexes: one is clustered on self-incrementing primary key and one unique non-clustered on nvarchar(50) field. The table is expected to grow constantly until it reaches 2-3 million rows. After 250000 rows were inserted I could see 30% fragmentation of primary key index and 99% of non-clustered one. How should I deal with fragmentation of the second index? Should I change fill factor (which is now default value of 0) and if so which value would be reasonable?

Upvotes: 1

Views: 160

Answers (1)

Brian
Brian

Reputation: 3713

This case is exactly what fill factor was made for, preventing file fragementation as tables expand. I took a DBA class some years ago and I remember them recommending 10% as a "good starting point", but given the numbers you've provided, maybe 30-40% would be a starting point.

But while avoiding page splits is a good thing, the downside of fill factor is that if you set it at 50%, that means SQL will have to read twice as many sectors to retrieve your data.

Here's a good explanation:

Good luck.

Upvotes: 1

Related Questions