Back in October 2014 Midnight SQL released v1.0 of Minion Reindex, a free, open source index maintenance solution. I’m all for making my day job easier so I once I heard about this I wanted to get it into a development environment asap to see what it can do. Unfortunately other things have gotten in the way so I’m only getting around to reviewing it now.
I’m going to go through the deployment, main objects and an initial test of the solution but first off, let’s address the massive elephant in the room that is, Ola Hallengren’s maintenance solution. Ola’s scripts are tried and tested, I’ve been running them in my production environment for god knows how long so it’s going to take something pretty special to make me change. Midnight SQL are aware of this and have detailed the differences between the two solutions here.
Going through the list, the features of Minion Reindex that caught my eye were:-
- Minimal job configuration – reduced amount of job/job steps needed
- Exclude databases/tables without altering jobs
- Configure database/table thresholds and settings without altering jobs
- Monitoring/Collecting initial and post fragmentation
Ola’s solution does require a fair amount of configuration in the job steps. It also logs all information into one table, dbo.CommandLog which stores some information as XML. That’s fine to be honest but I’m looking to see if this solution can make my life simpler. Can it reduce the amount of time I spend making adjustments to index maintenance (i.e. – the jobs) on my servers? Will it give me more information than I have now? Will that information be easier to retrieve (i.e. – not having to parse XML)?
Deployment
Deployment is a cinche, just download the .zip file, extract and then run the MinionReindexInstall.sql script in the database you wish to deploy to. One thing I don’t like is the ascii art at the top of the script, it looks a bit tacky for my taste but each to their own.
Another thing to mention is that the script will enable xp_cmdshell, not something I’m particularly keen on because of security concerns surrounding it (see here https://msdn.microsoft.com/en-us/library/ms175046.aspx) so I’d rather have it turned off unless it is absolutely necessary. I’m only testing on one instance of SQL Server 2012 Development Edition and I am not using the centralised logging feature (which is why the script enables xp_cmdshell) so I removed the code that enabled xp_cmdshell from the deployment script.
Objects Created
The script creates its own schema, a scalar function, three stored procs and nine tables. I’m not going to go through what each of them does as they are all detailed in the documentation that comes in the .zip file, but here are the main ones:-
Tables
IndexMaintLog – Index operations breakdown by database
IndexMaintLogDetails – Index operations breakdown by individual database
IndexSettingsDB – Maintenance settings at database level
IndexSettingsTable – Maintenance settings at index level
Stored Procedures
IndexMaintMaster – Main procedure which analyses databases and then calls IndexMaintDB
IndexMaintDB – Performs the actual index maintenance in the databases
SQL Server Agent Jobs
MinionReindexDBs-All-All – Performs the heavy work i.e. – rebuilding indexes
MinionReindexDBs-All-REORG – Performs light weight maintenance (reorganising)
Fairly straightforward, right?
Initial Run
So the table that’ll start you off is IndexSettingsDB. Here you’ll add in rows for each database in your SQL instance OR just leave the default row. There’s loads of settings here, allowing you to finely tune your index maintenance for each database without having to touch the Agent jobs. Initially I just inserted a row for each database (using the default settings) and gave the MinionReindexDBs Agent job a whirl.
Whilst the jog is running, have a look at the IndexMaintLog and IndexMaintLogDetails tables. These will show you what operations the job has performed, what it is currently doing and, in the case of IndexMaintLogDetails, what is queued up.
The level of detail in these tables is fantastic. The IndexMaintLog table will give you a good overview of what has been done in each database, the execution time, the number of tables processed and not only the number of indexes processed but the number of indexes reorganised vs rebuilt.
Moving on to the IndexMaintLogDetails, we’re now getting down to the individual index stats. So we’ve got execution time of both reindex and stats update, initial fragmentation, post fragmentation (if you’ve included that option in IndexSettingsDB) and the index usage stats (seeks,scans, lookups etc.). I’ve always separated out my collection of stats from my maintenance tasks but I’m definitely going to switch on the post frag collection and see how it performs.
These two tables are absolutely screaming out for a SSRS report to be slapped over the top of them. And they’d be pretty easy to write, only two tables to track trends in your databases which would allow you to see changes in fragmentation patterns over time.
Further Testing
The next things for me will be, setting up table level exclusions in certain databases and setting the order of databases and tables to be reindexed. Once that’s all setup I’ll get both Agent jobs on a schedule, start fragmenting my test databases (I’ll shrink them) and then analysing the stats.
Conclusion
I like this solution, it’s easy to deploy, configure and run. I’m going to let this run in my dev environments for a while to see how it performs with a view to deploying to production.
I’d definitely recommend that DBAs should be downloading this, installing in a development environment and playing around.
What’s also encouraging about this solution is that Midnight SQL list on their website features that will be included at a later date. There are some features that I’d like to see, system table maintenance and the ability to rebuild individual partitions would probably be at the top of the list, but it’s good to know that there will be future releases (and with that, hopefully support).
Minion Reindex can be downloaded from here:- http://www.midnightsql.com/minion/
