Time for problem db #2

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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s