Maintaining SQL Server indexes may have just become quite a bit easier. If you're hitting issues with your current maintenance routines, or you have seriously large or complex systems, you might want to check out Minion Reindex from Sean McCown and the Midnight DBA team.

What we have here is a set of complex, highly customizable scripts that are going to take on the task of rebuilding and reorganizing your indexes. Sounds simple because it is, as long as someone else is doing all the work for you. The trick is to make it easy and that's what Minion is all about. Let's check it out.

Installing Minion Reindex

On the Minion Reindex Home Page, you'll find the download link. There are currently no requirements attached to downloading the scripts. They're not tracking who has downloaded it, but there is an End User License Agreement (EULA) that is probably worth a read. Download the .ZIP file, open or extract it, and in there you'll find two files, one that's the Minion Reindex install script for a single server, and the other a PowerShell script for doing large scale installations. They really do make this easy.

Single or multi-server installation

To install Minion on a single server, simply open the install script inside SSMS, read through the comments, and run it. The script will enable xp_cmdshell, so make sure you have your security settings correct, as noted in the documentation. A side note: very cool ASCII art awaits you at the start of the script, but personally, I think it's a bit too much.

If you want to install Minion Reindex on a bunch of servers, all at once, you can use the MinionMassInstall.ps1 script. It's just a simple loop operation using the pipe in PowerShell to take a list of servers that you supply and a database to which to install Minion. I just used master, which is the default, but you can specify a custom management database. By default, you enter the list of server by editing an array, but you could modify this if you wanted to load the servers from a file, or in some other way.

What's installed?

Minion Reindex creates a number of objects in your system. Firstly, it creates two SQL Agent jobs (covered in the next section), which will run the index maintenance tasks on a schedule; a major focus of Minion Reindex is ease of use and automation. Yes, you can modify these jobs, or even create your own, with the procedures that get installed on your system.

Secondly, Minion Reindex creates in the master database three new stored procedures, as follows:

• Minion.IndexMaintMaster – the "control" procedure, which determines which indexes to rebuild or reorganize, and in what order
• Minion.IndexMaintDB – called by IndexMaintMaster to perform the actual re-indexing operations
• Minion.HELP – displays help on any Minion objects

These are not encrypted and you can open them up and read through them to see exactly what is being done and how it's being done.

Finally, Minion Reindex adds to master a set of tables that it uses to help it do its job of maintaining your indexes. Again, these are fully accessible, so you can read them for yourself. We'll come back to these later.

The SQL Agent Jobs

Once installation is complete, you have index maintenance in place. Go worry about something else, like your backups. However, let's assume you want to do something other than just let Minion run. What can it do for you?

The default installation creates two jobs in SQL Agent, as follows:

• MinionReindexDBs-All-All
  • rebuild or reorganize all the indexes on all your tables in all your databases based on the fragmentation criteria (there are defaults in place that you can adjust)
  • runs once a week on Saturday
• MinionReindexDBs-All-REORG
  • reorganize all your indexes across all your databases based on fragmentation criteria
  • runs every day at 2:00AM

You can modify these jobs just as you would any other job on your system. Don't like 2:00AM? Change it to 3:00AM. Minion Reindex puts strong emphasis on simplicity and flexibility.

The Reindex Scripts

The same flexibility applies to the re-indexing scripts; they come with defaults so you can run them "as is", but we you can also customize their behavior.

The documentation recommends strongly to do all maintenance by calling Minion.IndexMaintmaster (rather than calling Minion.IndexMaintDB directly), and Listing 1 shows the default settings for the Minion.IndexMaintmaster stored procedure.

CREATE PROCEDURE [Minion].[IndexMaintMaster]
@IndexOption = 'All', -- Valid options: All, ONLINE, OFFLINE
@ReorgMode = 'All',   -- 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 = 1      -- Allows Live Insight through Log tables.
AS
…

Listing 1

Let's step through what's possible, just from here.

• @IndexOption: The valid entries are ALL, ONLINE, OFFLINE. You can decide which of the indexes within your system you want to include. If you keep offline indexes in place for some reason, it might be a good idea to exclude them. You could just modify this script to make it ONLINE only.
• @ReorgMode: This lets you determine which indexes are going to be rebuilt based on the Reorg threshold, another setting we'll discuss later. You can make it so that it automatically just does them all. You can make it so it only does reorgs for tables that pass the threshold
• @RunPrepped: You can have Minion gather metrics on your indexes, specifically on the fragmentation statistics using the next parameter. Once you have statistics gathered, you can have your Minion script use those stats. It's a way to take more direct control over how you use your maintenance windows. You can break apart the gathering of statistics from the rebuild process.
• @PrepOnly: Runs the statistics gathering if it's enabled and stores them for later use. It's a way to break down the processing so you're not doing everything all at once. One more level of flexibility built into this code.
• @StmtOnly: Maybe you just want to generate the scripts for rebuilding your indexes. Maybe you just want to run a test to see what would get rebuilt if you ran the script now. Here's how you do it.
• @Include: One great way to control the process is to run Minion only against the databases that you want. All you have to do is supply a comma delimited list within a string. But, remember, if you add databases to your server that you do want included, you'll need to add them to this list. That's why, if you only need to exclude a few databases, rather than listing everything, you use the next parameter.
• @Exclude: Instead of including databases, you can pick and choose databases to exclude.
• @LogProgress: If you're doing a manual run of some kind, you probably don't need to log what happened. But if you're running this regularly on your systems, you're going to want to know which tables and indexes on those databases were updated, and which did not. Here's where you turn that on for any one run for live progress tracking.

Controlling the Reindex Settings

As you can tell, there's a lot of flexibility built into Minion, but it doesn't end there. You can control re-indexing criteria, such as the rebuild and re-org thresholds, specify fill factors and more, at both the database and individual table level, using two of the following two tables, which are created when you install Minion:

• Minion.IndexSettingsDB
  • Contains a 'default' row, MinionDefault, which specifies the settings for all databases on the server that don't have an individual row specifying custom settings
  • If you wish to specify custom settings for a particular database, simply insert a new row for that database with the required settings
• Minion.IndexSettingsTable
  • Allows you to insert rows, each one defining a specific table in a database that requires different behavior

There are a huge number of options here and we'll discuss only a few of the highlights – see the documentation for full details.

Using Minion.IndexSettingsDB, you can create 'groups' of databases and determine the order in which each of the groups is processed, putting higher priority databases first by entering them into the table. You can individually set the percentage threshold of fragmentation at which any given index on a database is to rebuilt or reorganized. You can also set things like FILL FACTOR, PAD_INDEX, ONLINE, MAX DOP, SortInTempDB, compression, and more. You can also turn off statistics updates, which will normally run by default, in case you're maintaining statistics in some other fashion.

One really cool feature is that you can set up pre- and post- scripts to run for your databases or tables. It's amazing the level of flexibility built into this set of scripts.

You also can control most of the same settings right at the table level, using Minion.IndexSettingsTable. This will allow you to exclude some tables or just modify the behavior of some tables within your databases, right down to being able to set specific scripts to run for individual tables within a database.

Checking the Log

I'm a huge believer in doing your work based on knowledge. One of the most important places where you need knowledge is in troubleshooting. I've been running the Minion scripts for months now with no issues, but things do go wrong occasionally. That's where getting a look at the logs is going to make a huge difference.

Figure 1 shows some of the output from Minion.IndexMaintLog table, on my test machine.

Figure 1

You can see the important stuff, when it was run, whether or not it completed successfully, the settings that defined the run and how many objects it processed. If you have pre/post scripts, you'll also be able to see when they started and completed. You can also see the finish time of the whole process and the runtime in seconds, as well as any warnings.

This gives you a great overview of what has been run, but the Minion.IndexMaintLogDetails table provides even more detail, as shown in Figure 2.

Figure 2

You can see when each of the indexes that was rebuilt or reorganized by Minion were touched. It also provides information about the index itself and lets you know the table, the name of the index, the type of index and more. You can look up the rebuild or reorg command used on this index, should there be a problem. You can see how long it took, the start and end times, and a whole slew of other statistics about the indexes in question. Further, this is entered live as the system runs, so you can use this table to monitor Minion as it's running, in order to understand the current status of your rebuild processes.

There are settings at the default, database, and index level that let you set what gets logged and how long it's retained.

Summary

I'm really impressed with the work and thought that has gone into these scripts. Using all the default options, Minion offers a simple to use, easy to maintain, utterly painless mechanism for maintaining your indexes. Alternatively, you can set up a very complex, highly sophisticated, customized set of specific maintenance routines. You can do either or both of these things using a single set of scripts.

Even my one very minor criticism of the tool is sort of a complement: it almost provides you with too many options. The easy set up and instant gratification are going to work really well for the accidental/incidental/reluctant data pro. The incredibly well thought out degree of flexibility and power are going to work well for the sophisticated data pro. However, it's possible that the person that's moving between these two stages could be a little overwhelmed. However, the Midnight SQL team has put together great documentation, a webinar, and whole series of how-to lessons that should offset any of that difficulty.

Ultimately, Minion Reindex is very easy to use and very flexible, and is a seriously powerful addition to anyone's SQL Server toolbox.