Index Fill Factor

Question

Post reply

Index Fill Factor

SQLAssAS

SSCertifiable

Points: 7246
More actions
July 29, 2013 at 6:25 am

#280724

We have a database is isnt performing as it should. A while back I had a database which grew quite rapidly and I was advised to change the index fill factor from the default 0 to 80. For mainly static databases 0 is fine but should be altered for databases with constant deletes and inserts.
Is this advise correct? Will it help with performance? And are there any adverse effects which could occur as a result of changing it to 80?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

performance typically has a lot more to do with

1. the sarg-ability of the queries hitting the database,

2. the accuracy of the statistics on the data and

3. the indexes in place to support the queries themselves.

fill factor would not, by itself, cause a slowing down of the data; if you are inserting and updating a lot of data, statistics would be one of the first things i would knee-jerk update before anything else.

the fill factor is going to affect the # of pages the data is store din, but not really affect the performance.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Changing the fill factor in the way you suggest is likely to have a positive effect on write performance (because there will be fewer page splits) but a negative effect on read performance (because sparsely populated pages mean more of them have to be read in). What size those effects are (and whether they'll even be noticeable) will depend on the kind of indexes you have, your disk subsystem, data usage patterns and so on.

John

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 3

Lowell - Completely agree that the index fill factor wont be the only thing to address, I am just looking at all possibilities at the moment and wasn't aware how this worked properly. The application is currently on the same server and SQL is set to use all the memory which obviously won't be helping things either! Aswell as the index's being fragmented and stats not been updated for months. (It is an inherited server).

John Mitchell-245523 (7/29/2013)
Changing the fill factor in the way you suggest is likely to have a positive effect on write performance (because there will be fewer page splits) but a negative effect on read performance (because sparsely populated pages mean more of them have to be read in). What size those effects are (and whether they'll even be noticeable) will depend on the kind of indexes you have, your disk subsystem, data usage patterns and so on.
John

Thanks for that John very simple way to look at it. The database is currently 240GB and grows by 1GB per week. I suppose it is hard to advise is the fill factor should be changed without knowing the reads but what guidelines do you follow for your databases?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

I know it's not what you're going to want to hear, but every environment, every workload is different. I'm afraid you're going to need to make the change (preferably in a test environment) and compare performance before and after.

John

ScottPletcher SSC Guru Points: 100947 More actions · Answer 5

FILLFACTOR can have a major impact on performance, depending on the table and the data usage patterns.

However, a FILLFACTOR of "80" is not necessarily good or bad in and of itself. As so often, it depends on the particular table and index.

You need to set the FILLFACTOR high enough to reduce fragmentation sufficiently in between rebuilds, but not so high that excess data space is essentially wasted.

Indexes (incl. a clus index, which is the table) that are naturally ascending -- such as IDENTITY or GETDATE() key -- typically work best with a very high fillfactor, 98+ (the main concern here is variable-length columns that increase significantly in size after INSERT).

The key tuning with FILLFACTOR is on indexes that do not have a naturally ascending key, such a GUIDs, names, etc.. For those tables, you have to take a best initial guess, implement it, then review the results and refine the FILLFACTOR.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 6

It could be the correct action to change the fillfactor to 70 or 80 for a specific table, if you have conducted careful analysis that your biggest problem is page split in that table.

Setting the fillfactor for all tables across the board in the database as a randomly selected action is more likely to have adverse effects. If nothing else, it may reduce your cache utilisation as you fill the cache with unused space.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]