TheSQLGuru (5/12/2011)
tfifield (5/11/2011)
Ninja,You also might want to consider how data is entered for the table. If there are VARCHAR type columns that are initially empty and then get filled in later this can also cause page splits due to the expansion on the column widths.
Todd Fifield
That is a GREAT point Todd that sooo many people miss.
Kevin,
I'm glad I can contribute something here. I once had to tune customer table where the clustered index was an Identity column. No-one could figure out why it was always so fragmented. The application inserted a skeleton record and marketing people would later call and get the full information, which bloated the VARCHAR columns and caused page splits. Simply lowering the fill factor from 85 to around 45 fixed the problem. There was some unused space in the table but performance shot up like a rocket!
Todd Fifield