This one is even bigger than my production database. At 172,083 tables and 393,481 indexes it isn’t an order of magnitude bigger, only 5.5% bigger or so. The same query from yesterday with physstats.avg_fragmentation_in_percent >= 5 returned 711.
EXEC [IndexOptimize] @Databases = 'ProblemDB2', @PageCountLevel = 100, @LogToTable = 'Y'
So this one takes a while. An hour and a half later it finishes up. This is not a powerful machine. It’s a dev server and 711 indexes is a few to chew through. Oh, I mean 713. IndexOptimize found to more indexes to work on than my earlier query. That’s something to look into.
Going through the CommandLog a few thing catch my eye.
Of the 713 ALTER INDEX commands
- 77% finishing in 5 sec or less
- 95% fininshing in 20 sec or less
- 1 outlier at 1,206 seconds
- 708 using REORGANIZE
- Only 5 using REBUILD, all on 5 related tables.
I run my earlier query again and see we are down to 176. Let’s run IndexOptimize one more time just to see what happens.
Two and a half minutes later CommandLog shows 179 actions, all using REORGANIZE. My query shows 176 with 5% or greater. Only 0.045% of my indexes have some fragmentation? I can live with that.
It’s time to put this into a regular job and test about problem db #1, #2 and the rest. Except for that it’s Friday afternoon. I’m sure I’m not the only one who doesn’t want to put new code in on a Friday afternoon, even if it is just a dev box.