A while back I saw a wonderful article on BrentOzar.com by Kendra Little. In the comments, Aaron Morelli mentioned that he had a script that was proprietary that manages fill factor for his tables. I'm sure his is better built, but this is a work in progress that I plan to expand on. This is the first thing that I've created that I'm releasing to more than the 5 people I work with... Please be gentle ^.^;
I am working on a project that will clear up some fragmentation issues over time. Right now this script is in beta 0.001 ½. The idea is that it adjusts your fill factor down by 5% after you've had 5 days within a 7 day window that show over 30% fragmentation and 1000 pages. This can be modified of course. Again, this is still in the beginning stages and needs a lot of love and care!
I will be working on cleaning up the code while making it a bit more user friendly. Any suggestions and feedback are welcome.
Things I plan to add in the future:
1) Make this a stored procedure.
2) Allow for the minimum fill factor to be modified.
3) Allow for this information to be E-Mailed instead of automatically adjusted for those less trusting.
4) More Dynamic to adjust for various features(i.e. partition based work, online operations)
5) Partition level rebuilds and control (2014 is supposed to allow partition based rebuilds online... YAY!)
6) Flowers and Penguins... (because who doesn't love Penguins!?!?!)
This is a two part code release. Part one will change all indexes to 100 fill factor (back to default). Part two will be the automatic fill factor adjustments.
Please please please don't run this in production. It's no where near official release. It's barely clean enough to be up here. ^.^
Auto_Adjust_FillFactor is here.
Set_Fragmentation_To_100 is here.
Auto Adjust Fragmentation by BIll Barnes is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
Set All Fragmentation To 100 by BIll Barnes is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
Post a Comment
All opinions welcome! Please comment with any changes thoughts or discussion points.