It worked! On Friday night for 51 minutes and 39 seconds IndexOptimize chewed through 1,618 indexes on a production server. That plus no indications of problems (quiet cell phone and email) make me a very happy DBA.
The CommandLog shows the bulk of the ALTER INDEX commands used REORGANZE (1,124 of 1,168) and went pretty quickly. 51% finished is less than a second and 95% finished in 3 or less seconds. There were a few outliers with run times around a minute or two, plus one long one. A REORGANIZATION on an index of a table with 1,127,615 pages, 15,400,680 rows and 7% fragmentation took almost 7 minutes.
I’m pleased enough with these results that I’m setting this up as a nightly job on this production server. The work load on
this server has two busy spans, 11 PM to 6 AM and then during Pacific business hours. I’ve set the job to run during one of the lowest periods of activity, 6 PM Pacific.
Running my index analysis query returns 757 indexes. What will tonight’s run do? What will be my baseline of fragmented
Looking back at the run times on the dev server, now through 5 days of daily IndexOptimze, the run times are all over the
place. It’s a small sample and past performance might not be an indication of future returns, but it is interested to look at
none the less.
This needs to run for a while longer before I’m ready to make any statements about job time. That and I want an excuse to graph some more data. I do enjoy graphs.