SQL Server Indexes : The forgotten Fill-Factor
While we all know the fill factor index option when creating or rebuilding an index, few know about the huge impact a fill factor has on the performance of your machine. Most DBA's treat the fill-factor as a static server setting and "forget about it".
“What number should I set the fill-factor to?” is a very challenging question that most DBA’s should bother about. High fill-factors have high data densities, while low fill-factors can cope better with nonsequential IO. The challenge is to find a statistical model that will minimize fragmentation, optimize insert performance while reducing disk usage overhead and all this without stressing the maintenance window.
By using a different approach for specific use cases this session will give you the tools to find the most optimal fill-factor for your tables.