• Nice script.

    For performance purposes, have you considered dynamically specifying the FILLFACTOR on your index rebuilds to help with page splits and IO performance?

    Not sure what your database default is for FILLFACTOR, but your script is going to apply the default setting to all your index rebuilds when this is not specified. Since you are already checking for the percent fragmentation in your script, you could specify a lower FILLFACTOR for more fragmented non-clustered indexes and possibly even a zero FILLFACTOR on clustered indexes that currently have a near zero percent fragmentation since they are obviously designed to add all new rows to the end of the table.

    This approach allows your clustered indexes to have a higher page density (improving overall IO performance) and allows your non-clustered indexes to handle some number of new records before a page split is needed, but not too much to waste a bunch of space on the drives and in memory. Having too low of a page density can also adversely affect performance by requiring the buffer pool to perform more IOs to get the same amount of data off the disk or out of memory.

    The FILLFACTOR should be set dynamically as the insert and update patterns on your tables will most likely vary from table to table - figuring out that algorithm is the tricky part. I guess you could even create a table of all your indexes where you specify your best FILLFACTOR based on your trials and refer to that table during your index rebuilds and only use the default on all new indexes that have not been added to your table yet.

    -Eric

    -Eric