Adjusting FillFactor by Referencing Split IO/Sec and Page Splits/Sec

As you may have noticed I've been posting a lot of questions lately related to tuning memory, disk, etc... We have a large project nearing its end and now we have to identify and eliminate some possible hardware bottlenecks. Code-related bottlenecks is another story.
In determining an optimal FILLFACTOR I have read that SPLIT IO/Sec and Page Splits/Sec should be examined to see if the values are high. My question is what is considered a high value for these counters?
I appreciate your input as always.
Dave

Fillfactor is depends on the activity on the table and it is not common to every table....so it all depends...
General rule...I can says...
Low insert/update you can keep it at 100% fillfactor, for high it could be between 60-75% and medium it could be 80-90%
If you want to make server side change I will go with 80-90% depends on your db activity...

Hi Dave,
As Muhammed points out - you really need to know
- where your writes are
- where your reads and what kind of reads (scans or seeks)
BUT also which particular procedures and/or queries are prioritized. Even though a table is subject to mainly reads, it might be some INSERT procedure that is more critical (from execution time point of view). You can monitor sys.dm_db_index_usage_stats to get this information.
You also need to establish how often you will be able to do index maintenance
If a table has only, or almost only, INSERTs, I would look at the possibility of putting the Clustered index on an ever-incrementing column (such as IDENTITY, orderdate etc.). In that way you can keep a high fillfactor, your table will never be fragmented and all new rows will go in nicely at the end.
As stated by Muhammed, around 90% sounds reasonable for read intensive tables but if fragmentation is an issue, it also depends on how often you will have a window where you can REBUILD or REORG your indexes.
Page Splits/sec is unfortunately server-wide but there is actually "leaf_allocation_count" and "nonleaf_allocation_count" in sys.dm_db_index_operational_stats which, according to BoL, will corresponds to page splits. However, I'm not sure I believe that because there is no separate column to account for just a new page in the leaf-level (the level of pages where all your data columns can be found, non-leaf level being where the index entries are). E.g.. if you had the clustered index on an Identity it would just add a new page at the end - not split an existing page. I never played around with these so I cannot say for sure what the number in these colums represent.
My approach would be to
- identify the most intensively used tables and the access patterns
- investigate whether it's feasible to set the clustered index on an everincrementing column
If not
- try with 90% for read
- monitor how long it takes for fragmentation to occur
- try with 70-80% for writes where there are critical INSERT procedures (again, it also depends on how often you can REBUILD/REORG)
- monitor the page splits and also keep an eye on the read IO for those tables
As you know there is very a often a "it depends" when it comes to SQL Server but with FILLFACTOR it is a really big "it depends". In bold.
HTH
/Elisabeth