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.
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.
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.
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.