Success!

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.

20150309-JobSucceeded

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
indexes?

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.

20150309-DevIndexRunTime

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.

Advertisements

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