Maintenance on a few…hundred thousand…indexes

Some run big data demos on laptops. Some technologists say big data is data sets that are so large or complex that traditional methods and practices are no longer adequate. My databases fall very short of big, but when I start looking at the number of objects I venture into well above average and maybe even bigish. I don’t have published metrics to back this up, but in my decade plus time with SQL Server I haven’t seen anything like what I deal with these days. I support a MS Dynamics Nav database with a mind boggling number of objects. Maybe my mind boggles a bit easier than others, but when I see 162,525 tables and 371,629 indexes my brain spins a bit. The copious number of objects comes from a setup where each company in the financial system has its own set of tables, nearly 1,600 tables per company. Each country we work in and each legal entity has its own company and more than 80 of those later (and growing…) BAM we’re in the hundreds of thousands of objects.

So what do you do with hundreds of thousands of indexes? DEFRAG THEM! Of course. Indexes are bound to be fragmented because data must be added and deleted and modified in those tables. Right? What’s the point of having 162 thousand tables if you aren’t constantly filling them with mission critical data. Time to throw a standard maintenance plan rebuild task at it. What could possibly go wrong with trying to rebuild 371 thousand indexes on a regular basis?

I don’t know, I’ve never tried that.

I skipped ahead and ran my own script against it which loaded the results of sys.dm_db_index_physical_stats into a temp table, declared a cursor (yikes!) and reorganized when avg_fragmentation_in_percent was between 10 and 30 and rebuilt when it was over 30. It ran OK, it was doing its job and it was running for around 45 minutes on a weekly basis. That was working and nobody was complaining, at least not about that part. Why mess with things?

Because. Reasons.

My first go around with Ola Hallengren’s scripts did not go so well, but that was a while ago and I failed to take notes. I’m sure something very pressing was going on that prevented me. Yes, very pressing. This guy doesn’t get praise and accolades out the wazoo (or whatever the Swedish word for wazoo is) if he isn’t doing amazingly awesome things. I must have not tried hard enough. MOAR OLA! I gave Ola’s IndexOptimize script another shot. I’ve written a few things about my implementation of Ola’s scripts, but the TL;DR is that they are working. In the beginning this script worked on 1,600 indexes, a few less than the 371 thousand available. Now it floats between 400 and 800, usually taking less than 10 minutes. It’s not perfect yet, there are times when the job run over an hour and I am ignoring an elephant in the room.

With IndexOptimize running in production I can look into trying new things in development. Minion Reindex by Midnight SQL was introduced as an easy to install, easy to run and very customizable index maintenance tool kit. set up is a snap and there are many ways to tweek and control how it runs. I wanted to see how this ran out of the box. Out of the .zip is more accurate, but doesn’t have the the same consumerist snap to it.

@IndexOption = 'All', -- Valid options: All, ONLINE, OFFLINE
@ReorgMode = 'REORG', -- Valid options: All, Reorg, Rebuild
@RunPrepped = 0, -- Valid options: 1, 0. If you've collected index frag stats ahead of time by running with @PrepOnly = 1, then you can use this option.
@PrepOnly = 0, -- Valid options: 1, 0. Allows you to take frag stats earlier in the day and save them for use at a later time.
@StmtOnly = 0, -- Print the reindex stmts w/o modifying the SP. This will NOT run any reindex stmts, only PRINT them.
@Include = NULL, -- Only do DBs listed here. Commas are used. ex: @Include = 'master, model, msdb'
@Exclude = NULL, -- Do all DBs except the ones listed here. Commas are used. ex: @Exclude = 'master, model, msdb'
@LogProgress = 0 -- Allows Live Insight through Log tables.

Minion has a few indexes to chew through. This is running on the dev box which has a slightly larger database than production.


sp_whoisactive is showing a run time of over 2 days, and 36 billion reads. Looking at the Status field of the Minion.IndexMaintLogDetails table at the same time shows me that it is almost complete, 383,484 of 386,105.


Minion has run a few times with varied success. Two runs completed and lasted nearly 2 and 1/2 days each, then a failure on the 3rd time. I haven’t dug in to figure out what Parameter 3 is, but apparently it was incorrect. I can appreciate the extra level of detail and control here, but I appreciate even more a job that finishes successfully and in less time.

What else do you with indexes besides maintenance? Make them better! How? With sp_BlitzIndex of course. It takes a while to run, but it gave me nearly 300 thousand easy to implement suggestions on how I can improve the indexes I have to deal with.

Finding Count
Feature-Phobic Indexes: No indexes use includes 1
Feature-Phobic Indexes: Potential filtered index (based on column name) 2,544
Index Hoarder: Addicted to nulls 1,272
Index Hoarder: Addicted to strings 12,088
Index Hoarder: Borderline: Wide indexes (7 or more columns) 37,948
Index Hoarder: Many NC indexes on a single table 6,996
Index Hoarder: More than 5 percent NC indexes are unused 1
Index Hoarder: Unused NC index 1,573
Index Hoarder: Wide clustered index (> 3 columns OR > 16 bytes) 129,348
Index Hoarder: Wide tables: 35+ cols or > 2000 non-LOB bytes 23,957
Multiple Index Personalities: Borderline duplicate keys 76,852
Self Loathing Indexes: Active heap 1
Workaholics: Scan-a-lots (index_usage_stats) 5
Workaholics: Top recent accesses (index_op_stats) 5
Total 292,591

Unfortunately this is a 3rd party database and I can’t make any schema changes. Maybe that’s not so unfortunate, I’m not sure I’d want to be held responsible for the creation of this monstrosity.


Thanks to Ed Leighton-Dick and others for getting this going.

9 thoughts on “Maintenance on a few…hundred thousand…indexes

  1. Kendra Little April 10, 2015 / 4:05 pm

    You know, I’ve been contemplating making a lighter weight version of blitzindex for a while (mentally titled the “where’s the beef?” release). And I think you just convinced me that it’s time. 🙂

    Keep on blogging, love your writing style.


  2. timothydpeters April 10, 2015 / 4:23 pm

    Thanks Kendra! Let me know if you want any testing. I’d love to help.


  3. MidnightDBA April 12, 2015 / 5:01 am

    Hey Tim,
    I’d love a chance to work with you to figure out why Minion is failing on you. I haven’t seen that error before. Though I find most of the time when people have errors, it’s usually a config issue.
    It also depends what version you’re on cause we’ve issued some bug fixes.
    Anyway, if you’re interested, email me and we’ll talk offline.
    I’ve got another customer who’s doing that many indexes and his are running smoothly.


  4. alinselicean April 14, 2015 / 6:58 am

    Hi Tim
    Nice post, love the style.

    Been through exactly same steps with Ola’s script. Initial runtime was around 20hrs (yes, we do have some really big tables). But I was able to drill down in the CommandLog table and get details on what was happening. My take on this was a bit different, but pretty much the same idea. Split the whole process in 3: update stats, reorgs and rebuilds. In addition, also isolate the largest tables with their own, dedicated jobs. As for the schedule, the REORGS/REBUILD jobs are starting 1 minute earlier than the UPDATE STATS and disables that, so we only have one maintenance job running each day. There are few other details, but this was my approach, high-level overview.

    And because we have multiple jobs for the same purpose, I also added the SPID ID and program name in the ExtendedInfo column so I can find out what job / SPID is updating each table, without having to go through each job individually.

    Thought of sharing my experience with Ola’s scripts (which are really great), maybe will help someone.



    • MidnightDBA April 14, 2015 / 11:59 am

      This is the problem I’ve always had with routines like Ola’s though. Any minor config change and you have to split it out into a separate job. That method doesn’t scale at all because if you’ve got a lot of DBs the jobs start to stack up. Then when you add more DBs you’ve got to find a place to put it. I had a client a couple yrs ago who had over 70 jobs just for reindex alone because they had a few thousand DBs on a single server and they had some specialized configs.

      That’s why I wrote Minion Reindex. Most of the config changes can be made with a simple query. That means that most of the time any specialized configs can be made without adding any new jobs, and you can change the configs for thousands of servers in a single query. And I made the logging much more extensive. Even Tim, who wrote this article, can’t deny that my logging is much more extensive and that the config is much easier to make mass changes to. Like let’s say you want to order the DBs or the tables. Easy. Or you want each table to have a separate fill factor… easy. You get the idea.
      But I urge you guys to try Minion. We’ve had over 2,000 downloads and we use it ourselves all over the place and we’ve never seen the behavior Tim is reporting. And I’m working with him in the background to see if we can discover the nature of his issue.
      Sorry to come off like a commercial guys, but I really believe in Minion and I wrote it because I honestly think it’s a better way of doing things.


      • alinselicean April 14, 2015 / 12:07 pm

        I’ll give it a try. The only problem is that we developed a lot around Ola’s process: diff backups are cascaded once the last of the maintenance job is finished, etc… Thanks.


Leave a Reply

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

You are commenting using your 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