In my previous post here, I’ve discussed how we can detect fragmentation in SQL Server databases indexes using dynamic management view function sys.dm_db_index_physical_stats. In this post, I’m sharing my stored procedure which I’ve created a while ago to rebuild fragmented indexes based on the fragmentation level. This stored procedure is based on sys.dm_db_index_physical_stats and resides in master database. This procedure automatically rebuilds indexes if the fragmentation level is above 30% and reorganises indexes if fragmentation is less than 30%.

Code for the procedure is as follow:

Mark this procedure as system object by executing the following command so that this procedure can be accessed from any database:

In the next version, I will add a logging feature so that you can see what indexes were defragmented, see how bad the fragmentation was, and perhaps do some trending on the data.

Let me know, if you find a bug or…