Is goes faster when you do less work

It turns out that when you ignore your largest table, the index job goes much faster. Wow, who would have imagined that.


JOB RUN: 'IndexOptimize' was run on 3/11/2015 at 6:00:00 PM
DURATION: 0 hours, 11 minutes, 57 seconds
STATUS: Succeeded
MESSAGES: The job succeeded. The Job was invoked by Schedule 42 (Nightly). The last step to run was step 3 (Optimize System).

Omitting the largest table resulted in the fastest run time of the IndexOptimze job yet and no deadlocks. There were 763 indexes worked on and 91% finished in a second or less. Only 15 of them lasted 5 or more seconds and topped out at 10 seconds. It’s all well and good that I have a fast running and successful job, but it is at the expense of ignoring the elephant in the room. The table I have excluded has 18 indexes. As of my last run of sys.dm_db_index_physical_stats, 7 of them are just fragmented enough for IndexOptimize to pick them up. These 7 indexes have a total of 854,812 pages and are just on the edge of the default for IndexOptimize to pickup, ranging between 5.1% and 6.9%. I’ve setup a quick job to log these stats nightly so I can revisit them to see how this fragmentation changes.

20150312-IndexStats

Advertisements

One thought on “Is goes faster when you do less work

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