I thought this was a great blog post and it got me to read, even though it’s a topic I know about, have written about, and didn’t think the post would reveal anything amazing: Finding Fragmentation Of An Index And Fixing It. It’s no-nonsense and it lets me know right away that this author intends to help me.
But I’m somewhat amazed how many fragmentation questions, posts, articles, and more that I still see on a regular basis. Shouldn’t this be a low level feature of SQL Server that just works? In 2008, or I guess SQL 11 now, should there be any reason for a DBA to monitor this and write custom code to ensure that it’s fixed on a regular basis? Should this not be something built into the system?
I dream about the day of right clicking a table and choosing “defragment.” This wonderful built in utility would then return my table to it’s initial fill factor and clean up all the allocations of extents to ensure that they were contiguous. It would have options I could pull up that would set the minimum and maximum levels of fragmentation I accept, based on server defaults of course, and it would ensure that my tables were kept within those limits. Sure it would be a background process, it would consume CPU cycles and require a good amount of disk space, but those things are relatively cheap.
Especially when compared with the cost of time for a DBA to manage this process. Even a few hours a year is too much time wasted by DBAs on a task like this.
It’s not sexy, but there are a lot of DBAs out there that would really appreciate it. As much as I know large sales dominate the market and this isn’t something that reps at Microsoft can point to, it’s the little guys, those DBAs in lots of 1 and 2 person shops, 10 and 20 instance companies, that make the recommendation to upgrade. And if Microsoft spent some time cleaning up little features, making things work better, I bet there would be a lot of recommendations to change versions.
And, Microsoft, if you deliver small things that work well, along with the big, sexy features that flake or that we don’t understand, you might be surprised how many of us would be looking to upgrade before Service Pack 1.