• Yes, that's correct, and hopefully you can schedule a re-build of the indexes before the page splitting starts to occur.

    The other downside is that less-full indexes also increase disk I/O. If you have an index that's 20% full, you still have to read in the entire index page, even though 80% of it is empty. So compared with an optimal index, where no page splits are occuring but every index page is 100% full (unlikely scenario unless you're not inserting any data into your database, but...), you'll end up having to perform 5 times as much disk I/O in order to read the index, as the index pointers / data will be spread over 5 times as many pages.

    I think this is one of the reasons Microsoft recommend that you leave FILLFACTOR settings to SQL Server - you can cripple a system's performance by creating lots of half empty indexes which increase disk I/O. Also, your backups are going to be bigger too, as there's lots of *almost* empty pages in the index which have to be backed up.

    For the most part, I'd look at leaving FILLFACTOR alone, with one exception - "read-only" MIS/reporting databases which are only going to be restored from an OLTP backup, and are never going to have data added to them. In that case, fill your indexes 100%, as it minimises the space they use on disk, which in turn minimises disk I/O when queries read the index, and you never need to worry about page splits because no data is added, so they never happen.


    Jon