Featured Article(s)
DTS Conversion to SSIS (Part 2 of 4)
Understanding the differences between DTS and SSIS. Also, information on running packages.
Quick, Initial Feedback on Index (de)Fragmentation Practices
On this Friday before the US-holiday weekend, I wanted to pass along just a thought or two on defragmentation. I do have one question first though. Several people that have written in have talked about maintenance plans that help with the index fragmentation and defragmentation. I’ve had a surprising number of you talk about shrinking the database as part of this plan. Surprising because this is not a good idea – it runs counter to the defragmentation work. So, my question is simple: Why? Why are you shrinking the database? Is there a benefit you’re getting or was it just that it was part of the maintenance plan, or…? I mean it as a sincere question. What does shrinking the database gain in your work with SQL Server?
Please send me a note here and let me know.
That said, here’s what Rogger had to say about defragmentation – "We are hosting a large number (100’s) of databases ranging from about 500 Mb to 4 Gb each. The applications are fairly transaction intensive. To that end, we run weekly maintenance plans that UpdateUsage, CheckDB, Reindex, UpdateStats, and finally ShrinkDatabase. This will run about 16-20 hrs. If anything fails, it’s usually the index rebuild. That requires identifying which one failed and manually identifying and fixing the issue(s)
And yes, we specifically rebuild rather than reindex."
Ken wrote to say "Automation on a weekly basis with the FillFactor recomputed for optimal size based on last 6 weeks growth statistics and fragmentation seen prior to defragmentation.
Of course, you know that FillFactor=0 is the optimal for a cluster Index on Identity(1,1), but is the worst fill factor for an index on a uniqueidentifier. The default value is an artifact from the early days when Identity(1,1) was the norm…"
Have a great weekend – we’ll be back on the air on Tuesday.