PHYData DBA (3/26/2015)
To anyone reading this thread. I know we went Off Topic there for a long period.It seemed like an important subject and one I had data to share with the community.
I will attempt to steer this discussion back to optimum settings for Index Fill Factor.
In the Books Online I found this statement.
https://technet.microsoft.com/en-us/library/ms177459(v=sql.105).aspx
If existing rows will be updated with data that lengthens the size of the rows, use a fill factor of less than 100. The extra bytes on each page will help to minimize page splits caused by extra length in the rows.
Now the database I am working with is 90% varchar and nvarchar values.
98% of those values do not have an entry yet for the Max Length.
So this database has the ability to triple the row size of every table (and all indexes) with one very very long winded entry from the UI.
It is my understanding from the information above that keeping the Fill Factor at 10 (90%) is not a bad idea for data tables like this.
What are your experiences?
PS: I want to mention that our vendor that wants us to change to Fill Factor of 0 or 100 has no information on why he thinks this will be good. Just says it will make the indexes and data tables smaller.
I agree with him.
I also tested that the first time the row size for a table gets increased we will see the app lock up for 10 minutes while 9 gigabytes of pages get split.
Like I said in my long winded write-up, an 80% Fill Factor is much better than page splits, which creates two pages each that are half empty which wastes 100% (total) for a given page that's been split and that can be quite substantial when it comes to the Clustered Index. I'll also remind you that if the rows of a table average over 4000 bytes, then no Fill Factor will help the Clustered Index, but you also won't get page splits there, either.
The bottom line is the same as all else in SQL Server. Changing the FILL FACTOR is not a panacea nor a bane to performance. "It Depends" on the situation and each index, especially Clustered Indexes and Covering Indexes should be examined carefully before making a change to FILL FACTOR.
To answer your question, I've seen changing the FILL FACTOR cause quite a relief to Insert/Update intensive tables and I've also seen it provide nothing but wasted space. The same holds true with SELECTs except I've also seen it hurt SELECTs quite a bit when there is a lot of wasted space. It always requires an educated and informed decision on larger tables.
--Jeff Moden
Change is inevitable... Change for the better is not.