MS SQL Index rebuild for less page count and high fragmentation

Question

Hello Team,

We have an arguments saying Index rebuild not required if page count <1000 in MS SQL server.
We have experience performance issue if fragmentation is high and page count <1000. As soon we rebuild index performance resume normal (high loaded system).

Reference to below article and discussion, rebuild only if page count >=1000
https://www.sqlskills.com/blogs/paul/where-do-the-books-online-index-fragmentation-thresholds-come-from/

Reference to our experience can we proceed rebuild index excluding page count and maintain fragmentation < 20%.
Any script to rebuild index excluding page count?

Answer 1

Hi,

We have experience performance issue if fragmentation is high and page count <1000. As soon we rebuild index performance resume normal (high loaded system).

Did you notice the most important sentence in the article saying that "These numbers are made up." ?

Reference to our experience can we proceed rebuild index excluding page count and maintain fragmentation < 20%.

No one can argue something which you test in practical :-)

As the article mentioned these numbers can vary for you, and they’re only meant as a good starting point to work from.

The answer is yes. If this fits best for your case then it fits best for your case - the question you should ask your self is why this is your case and maybe a change in the system design can bring you better solution, but as long as your tests shows that this action improves the performance, then the answer is yes

One more point to think about: You should take into consideration all actions in the database and not only specific query or task when you think about performance. In many cases (like rebuild index) one action which can improve performance for specific task, can lead to issues (like waits and locks) in other tasks. You should always think about balance between maintenance and live work with the data.

Answer 2

There's a pretty good chance that what other folks have said is true... Rebuilding indexes is a really expensive way to rebuild stats.

Index rebuilds cause stats to rebuild. Try just rebuilding stats, instead. You'll likely be surprised at how will it works.