Ever since we released Minion Reindex, our free index maintenance solution, people have asked us the same question time and again: “How is this different from Ola’s solution?”

Ola Hallengren’s SQL Server Maintenance Solution – specifically, the IndexOptimize piece – has been a popular routine for some time now. Like Minion Reindex, Hallengren’s IndexOptimize is free, easily downloadable, and easy to install and run.

So, why did we write an entirely new solution from scratch? It’s a reasonable question. We have a LOT of answers, and all of them start with “We wanted…”  We wanted something more, and we knew that a LOT of people would want the same things.

The heart of what we wanted…

We wanted quite a few features and options, but most of them center around a handful of core tenants:

  • Vastly improved logging and insight
  • Ease of management, especially through reducing the number of jobs (or job steps) required
  • Fine-grained control, in the form of database- and table-level configurations and exclusions
  • Massive scalability

See the “Philosophy of Minion Reindex” discussion below for more.

Feature comparison

Major Features
We want to… Minion Reindex Index Optimize Notes
View operations while they’re running  ● With Minion’s Live Insight, you can see specifics of the progress of index maintenance operations while they’re happening.  IndexOptimize has almost no insight into the status.
Easy mass deployment and alteration  ● Minion focuses on mass deployment and configuration.  You can easily alter the settings for any number of databases or tables on any number of servers without touching a single job.  Minion Reindex comes with a Powershell script to help get you there.
Minimal job configuration  ● Minion is specifically designed to minimize administration: you almost never have to alter or create a job to configure your environment.  IndexOptimize relies entirely on job configuration. In many cases, this necessitates dozens of maintenance jobs (or steps).  Minion Reindex does the same thing with a bare handful of jobs.
Pre-gather fragmentation stats  ● Maintenance windows can be tight and you don’t want to spend tons of time gathering fragmentation stats.  Minion lets you gather those stats earlier in the day, and use them during your maintenance window.  This maximizes your efforts during your window.  IndexOptimize does not have this feature.
Run pre or post code before or after the database is processed  ● Minion allows you to run any code you like before and/or after the database is processed.  This is a powerful feature that several users are already taking advantage of.
Run pre or post code before or after each table is processed  ● If you need to run something to prepare a table before it’s processed, Minion Reindex accommodates it.  A great example is setting ALLOW_PAGE_LOCKS = ON before (and then turning it off after the index), so that your reorg doesn’t fail.
 Order, Exclude, and Include
We want to… Minion Reindex Index Optimize Notes
Reindex databases in the order you need, without altering jobs  ● Minion allows you to reindex the databases in any order you like, without any extra jobs or job steps.  IndexOptimize does not natively allow ordering; a workaround is to create new jobs and manage multiple schedules, or add additional steps and manage parameters in each.
Reindex tables in the order you need  ● You can set the order of index operations at the table level, to maximize your maintenance window, with Minion. This is not available in IndexOptimize.
Exclude databases without altering jobs  ● Minion allows you to exclude databases without touching the job. IndexOptimize requires separate jobs (or steps) in order to manage database inclusions and exclusions.
Exclude tables easily, without altering jobs  ● Minion allows you to exclude individual tables from index maintenance, without touching the jobs.IndexOptimize uses string matching within job parameters to include and exclude indexes; this does not scale past the needs of very small shops (with very few tables).
Exclude databases based on regular expressions  ● Minion allows you to define as many regular expressions as you like to exclude databases.  This is handy when you have transitory archives or other types of databases that come and go.  And, you don’t have to touch any jobs to enable this feature.
Exclude all tables in a schema In Minion Reindex, you can manage this through table exclusions, by excluding all tables in that schema. You don’t have to alter any jobs, and you can easily set up a precode update statement to maintain the exclusion list.In IndexOptimize, you must edit parameters in the job(s), a manual process which doesn’t scale.  If you have exceptions, this will be difficult to manage.
Include specific databases  ●  ● In both solutions, you can provide database names and wildcards to include specific databases.
Include specific tables  ● IndexOptimize allows you to reindex specific tables by name, via wildcard matching in a job parameter.Minion Reindex has a stronger emphasis on exclusions, assuming that most databases and tables require index maintenance. Howerver, you could achieve a similar effect in Minion Reindex with the use of database precode that sets the Exclude column for non-included columns.
Include specific indexes by name  ● IndexOptimize allows you to reindex specific indexes (or tables, or schemas) by name, via wildcard matching in the Indexes parameter.Minion Reindex does not currently provide this feature.
 Logging
We want to… Minion Reindex Index Optimize Notes
Monitor daily fragmentation levels  ● Minion allows you to save raw fragmentation stats so you can graph your daily fragmentation.  This is great for estimating when the big tables will hit their thresholds again.
Collect post-processing fragmentation  ● Minion allows you to see how effective your index maintenance has been, by taking the fragmentation stats right after the index is processed.  And of course, this is configurable down to the table level without changing the job.
Log results to table  ● While both solutions log to a table, IndexOptimize only gives very basic information that’s difficult to access.  See the “Log results to table” discussion below for more.
Automatic log management  ● Minion allows you to specify when the log entries get deleted.  And of course, you can configure your log retention down to the table level.  IndexOptimize creates yet another job (or job step) and all databases on the server are held to the same threshold.
 Configuration
We want to… Minion Reindex Index Optimize Notes
Configure thresholds and settings for individual databases, without altering jobs  ● Minion can have a completely separate set of criteria for each database, without altering or adding any jobs.  IndexOptimize requires you to set up a new job, or a new job step, each time you need to configure different settings for a database.
Configure thresholds and settings for individual tables, without altering jobs  ● Minion allows each table to have its own threshold settings.  Every aspect of the routine is configurable at the table level.  While it is possible to reproduce this behavior with IndexOptimize, it simply does not scale. Anything more than a very small, simple set of customizations quickly turns into a huge number of jobs or job steps.
Focus on reorganizing, rebuild, or both  ●  ● Both solutions allow you to perform only reorgs, only reindexes, or both.
Focus on online, offline, or both  ●  ● Both solutions allow you to maintain only indexes with online operations, only those with offline, or both.
Provide all ALTER INDEX options  ● Both solutions support basic index maintenance options such as Sort in TempDB, Fill Factor, Pad Index, and MaxDOP.But, only Minion Reindex offers the full set of ALTER INDEX options available; IndexOptimize neglects a great many reindex options.
Configure reindex options at multiple levels  ● Minion Reindex allows you to configure ALTER INDEX options at the default level, the database level, or the table level without adding or even touching the jobs.While you can configure reindex options for databases and tables with IndexOptimize, it requires everything to be done in different jobs or steps, and is extremely laborious.
Page count level  ●  ● IndexOptimize provides a paramater that lets you skip indexes based off of the number of pages.In Minion Reindex, you can achieve this same effect dynamically, by setting precode to run before each index maintenance run.Note: This is not usually necessary, as small indexes process so quickly.  One Minion Reindex run tested against a database with 2,000 small indexes only added a few seconds to the runtime.
 Additional Features
We want to… Minion Reindex Index Optimize Notes
LOBCompaction  ● This feature is turned on by default in SQL Server;  Minion doesn’t specifically allow you to turn this option off.IndexOptimize specifically provides for turning LOBCompaction on or off.
Reindex by partition  ● Minion Reindex does not yet offer partition level reindexing; we want to take an extra release to get it right.IndexOptimize does reindex by partition, though we feel that this feature is not well executed (e.g., there is no partition level configuration).
Set a time limit Minion Reindex does not yet offer a time limit; we want to take an extra release to get it right. What’s more, we believe that database and table ordering is a better option for most shops.See the “Time Limit” discussion below for more. IndexOptimize lets you set a duration after which no commands are executed; this is not actually a hard time limit.
Delay  ●  ● IndexOptimize allows you to add in a delay between index commands.In Minion Reindex, you can achieve a similar effect (at the table level) by setting table precode or postcode to run before (or after) each set of reindex statements.See the “Delay” discussion below for more.
Reindex system tables  ● Minion Reindex doesn’t currently reindex system tables.  Watch future releases.IndexOptimize does.
Update statistics on reorganize  ● Minion Reindex lets you choose whether you want to update stats when you reorganize.  This is configurable down to the table level.IndexOptimize has an “all or nothing approach”; updating stats is either on or off at the database level, for all objects. You can get around this to a degree, by creating jobs (or job steps) with commands to maintain individual tables; but, this quickly becomes unmanageable.
Update statistics independently of index maintenance Minion Reindex does not update statistics separately at this time; stats are updated on reindex.IndexOptimize lets you update stats – index statistics, column statistics, both, or neither – for your entire database, independently of index maintenance.
Stats sampling When Minion runs update stats for you, it allows you to specify exactly the parameters you can add, and you can specify them at the table level.While IndexOptimize does more with stats than Minion reindex, it only provides two options for stats, and only at the database level.
Generate statements only  ●  ● Both solutions allow you to generate index maintenance statements without running them.
Support Availability Groups  ●  ● Both solutions will perform index maintenance on  the primary replica of AlwaysOn Availability Groups.

 

Yes  ●
Insufficient, or via workaround
No

 

Philosophy of Minion Reindex

Minion Reindex achieves its four core tenants – logging, management, control, and scalability – with a handful of design points. First, almost all configurations are done in tables, so it’s extremely easy to configure your setup exactly the way you need. The table-based logging means that you can mine information about your maintenance runs in multiple ways, and report off of that data the way you’re used to, with queries and SSRS reports.  And with the option of pre- and post-code, you can customize your routine extensively.

Log Results to Table

Minion provides extensive logging to both help you troubleshoot, predict, report, and plan your index maintenance.  And while IndexOptimize does log to a table, the amount of logging is perfunctory in comparison.  Minion Reindex provides every single aspect of the process in the log tables, and it’s easy to query.  The only information stored in IndexOptimize’s log past date and time is in a column called ExtendedInfo and it only holds the fragmentation level.  This is an XML column so you have to learn how to parse XML just to sort your log data by fragmentation level.  And there are no queries provided to help you.  And what if your job has errors?  IndexOptimize creates text files for errors.  Minion puts errors in the log where you can monitor them easily from SCOM or any other tool you use.  Or just create a monitor job to alert you when there are errors.  And there are no text files to manage.

Time limit

The issue is around intent.  What this feature intends to do is to keep you from going over your maintenance window by not allowing any further commands to be run after a certain time.  However, large tables in large databases throw this feature out the window.  For example, you have a time limit set for 2hrs.  You process tables up to that point, and you’ve only got 5mins left before you hit the 2hr mark.  So since you’re still under the time limit, your next table begins processing.  The problem is that this last table has 1.5billion rows and takes 1.5hrs to process.  I don’t think this is what you intended.  And if that table is really important then you’re in trouble.  That’s why we don’t think this is a significant feature.  With Minion we’ve taken a different approach.  Instead of limiting the time, we let you order the tables in the order you need so that your most important tables get processed first.  This really nullifies the time limit because the bulk of that 2hrs isn’t spent on insignificant tables.  With Minion, your important tables get done first, and then the rest can finish up as they need to because they’re probably smaller and less significant and the maintenance will have much less impact on your system.  Which approach sounds like it follows the actual intent of the time limit?

Delay

We rarely find the delay feature to be useful, and it doesn’t scale at all.  We’ve tested Minion Reindex against a database with over 350,000 tables.  Assuming you’re using IndexOptimize, simply adding a 1 seond delay between index commands (assuming only one index per table) will add over 97 hours to the maintenance job in delays alone.  That’s 4 days.  And what if you only needed it for a couple tables?

Get Minion Reindex at http://www.MidnightSQL.com/Minion. Check out our extensive documentation sign up for the newsletter, or enter a feature request while you’re there.

For a PDF version of this document, click here: MinionReindexVsIndexOptimize.pdf