Maintenance Plan - index pages

[Joiner]

Can anyone tell me the "best practice" for optimizing index pages? Our indexes were created with the default fill factor of zero as recommended in BOL. I am now creating a maintenance plan and I'm wondering if it is better to reorganize pages with the original amount of free space or change the free space per page percentage to 10% (default wizard option). Can anyone give me a rule of thumb for this?
Thank you in advance for any assistance.

 

[foxdev]

The answer, of course, is: "it depends".

It depends on the nature of your indexes. For an index on a "date/time added" DateTime column or Identity column, where in both cases new rows (and index entries) are added to the end of the index, you can work with a tighter "free space" value.

It also depends on whether query performance or transaction (insert/update) performance are more important. Query performance priority suggests a tighter "free space" value (95+%, for example), requiring fewer disk reads. -----------------
Robert Bradley
use coupon code "TEKTIPS" for 15% off at:

http://www.lowcostprints.com

http://www.foxdev.com