In this post I want to cover some thoughts about SQL Server index fragmentation recommendations I have had. Because recently I provided some feedback about it.
My feedback was to do with the current recommendations from Microsoft about when to either reorganize or rebuild an index. Because I had talked about it online a couple of times recently.
You can read the current SQL Server Documentation page in detail here. At this moment in time the current guideline is to rebuild the index if it is more than thirty percent fragmented.
However, like they say in the paragraph below the guidelines they are only a rough guideline and you should test yourself.
In reality, the existing guideline figures have been around for some time now and could do with a fresh review.
I know a lot of people have been suggesting these figures should be changed for some time now. However, the question is what do we propose to change the recommendations to?
For instance, do we ask Microsoft to provide more examples of where the index fragmentation differs?
Because I know from experience these figures can vary for all kinds of reasons. Especially when dealing with large indexes, which I discussed in a previous post here.
In addition, do we ask them to add details of other things to check first if investigating performance issues? For example, updating the statistics instead first.
Interest
Another thing that did cross my mind when I was thinking about what feedback to give was who actually would want it changed?
Because a lot of people simply put in a popular index maintenance solution and leave the defaults as they are. In addition, a lot of people are branching out into other areas as well these days.
My feedback
After some thought, I suggested that maybe Microsoft provides suggestions based on different index/table sizes instead. You can read my feedback in detail here.
Final word
I hope you enjoyed reading about my thoughts about SQL Server index fragmentation recommendations. In addition, I hope that it has got you thinking about your own views about these recommendations.
Do you have an alternative proposal, or are you simply not bothered if these guidelines are changed? Let me know your thoughts below with a comment.
Actually, they removed to old 5/30 recommendation, from the article at the link you provided, on Apr 20, 2021 and its been in a state of flux since then.
Unfortunately, they still have the same code example under example “D” in the following article on sys_dm_db_index_physical_stats. Here’s the link for that.
https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql#d-using-sysdm_db_index_physical_stats-in-a-script-to-rebuild-or-reorganize-indexes
My personal experiments have taught me that we’ve been seriously misled for more than 2 decades and the fault is not entirely (although significantly) the fault of Microsoft. It’s also the fault of the hundreds of band-riding “experts” that readily propagate the same, ill conceived, 5/30 or similar recommendations.
In the ‘tube at the following link, I destroy the myth of Random GUID fragmentation and it turns out that myth can firmly be blamed on the supposed 5/30 “Best Practices” method of doing Index Maintenance, which I also shred. Considering your good post above, you’ll have more than a mild interest in what I say.
https://www.youtube.com/watch?v=rvZwMNJxqVo
If you’re listening with a headset, be aware that they added 3 advertisements at the 15:00, 30:15, and 45:25. The advertisements are important to help support this event but I wanted to let you know that they’re sudden and they’re loud! They WILL lift your headset! I also have an “errata” post on that ‘tube offering. I has to get up about 4 hours before my normal wake up time because I was presenting to folks in Europe and I wasn’t firing on all 8 cylinders yet and so misspoke a couple of times.
There’s also a bit of an outtake after the Q’n’A that drives the point home in spades.
And, no… it’s not just about Random GUIDs. I packed a shedload of info into that hour and 21 minutes.