Grant Fritchey Reviews Minion Reindex
-
Comments posted to this topic are about the item Grant Fritchey Reviews Minion Reindex
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Nice write-up, nice product. I haven't done a deep dive on the code but I have some questions. Maybe Sean can incorporate some of these if they don't already exist.
1. Does it handle Partitioned Tables and Indexes without having to manually identify such things?
2. Is there anywhere that you can see "why" an index was rebuilt? (As in what the frag level was).
3. When in the ONLINE mode, does it automatically exclude (do OFFLINE) indexes with LOB involvement prior to 2014 or do you have to make an entry in the control table for that?
4. When in the OFFLINE mode, does it take advantage of disabling an index before rebuilding it to keep space usage down? If it does, does it automatically detect foreign keys so it doesn't disable the index which would kill the FKs?
5. When it's doing a rebuild, does it take advantage of the BULK LOGGED Recovery Model for minimally logged index rebuild operations?
6. What's the SLA for support if something goes haywire? Does such support exist?
7. Does it do the optimization of rebuilding clustered indexes before it starts on the non-clustered indexes?
8. Since is does rebuilds, does it also automatically do the optimization of not rebuilding stats for indexes it just rebuilt?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
2 & 7. Yes.
The rest, I don't know. I'd have to go look it up. I'm sure Sean will be haunting this discussion though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Any thoughts on how these stack up with the scripts from Ola Hallengren that some of us know?
-
benjamin.seidle (1/27/2015)
Any thoughts on how these stack up with the scripts from Ola Hallengren that some of us know?That's always one of the first questions, so we put together an answer for that here: http://www.midnightsql.com/how-does-minion-reindex-differ-from-ola-hallengrens-indexoptimize/[/url]
-
Hey Jeff, just a few answers (bold) while Sean's otherwise occupied:
1. Does it handle Partitioned Tables and Indexes without having to manually identify such things?
Currently it doesn't do anything special for partitions, but we're going to blow that out of the water in v.2 later this year.
2. Is there anywhere that you can see "why" an index was rebuilt? (As in what the frag level was).
Yes, we have extensive logging. The Minion.IndexMaintLogDetails specifically has FragLevel and PostFragLevel columns.
3. When in the ONLINE mode, does it automatically exclude (do OFFLINE) indexes with LOB involvement prior to 2014 or do you have to make an entry in the control table for that?
Online mode only does those that can be done online.
...
5. When it's doing a rebuild, does it take advantage of the BULK LOGGED Recovery Model for minimally logged index rebuild operations?
Minion gives you the option to switch to BULK or SIMPLE recovery before index maintenance, but we don't do it by default. That seems just a little presumptuous! To enable this for a database, set the RecoveryModel column in Minion.IndexSettingsDB. (This is covered in our online and in-product help.)
6. What's the SLA for support if something goes haywire? Does such support exist?
We don't have official support - it's a free product - but we wrote this to be a force for good in the world, so we pay attention to reports of "haywire" activity. Also bugs and irritations. Email, tweet, or submit a bug report on MidnightSQL.com/Minion. Or all three.
7. Does it do the optimization of rebuilding clustered indexes before it starts on the non-clustered indexes?
Not that I'm aware of, but we'll have to get Sean to answer authoritatively on this.
8. Since is does rebuilds, does it also automatically do the optimization of not rebuilding stats for indexes it just rebuilt?
Each maintenance operation (a single run of Minion Reindex, which may cover many fragmented indexes) is based off of a list of indexes that meet the criteria for rebuild or reorg. An index isn't hit twice in a single maint op, because Minion already checked it off of the to-do list, so to speak.
[/quote]
Keep em coming!
-
I've got a few mins so I'll add to what Jen posted.
6. What's the SLA for support if something goes haywire? Does such support exist?
On top of what Jen said, we've always been very responsive to issues with the product and we are dedicated to continue to do so. So if you have issues or Qs, email us through our support email on the site and we'll do our best to work through any issues you have. I won't promise an outcome, of course, but we have yet to fail to meet someone's needs... and we've had some pretty obscure requests.
7. Does it do the optimization of rebuilding clustered indexes before it starts on the non-clustered indexes?
Starting in SQL2K5 the non-clustered indexes changed in how they relate to the clustered. Therefore, it's no longer necessary to make sure that the clustered index gets done first to avoid the NCs getting rebuilt automatically, and then again when you call them specifically. So we don't really go out of our way to force the cluster to be rebuilt first (that I can remember off the top of my head). So if you're holding onto the idea that it'll cause the NCs to be rebuilt, let that go cause it doesn't exist anymore.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Sorry about the SLA question. I know it's for free and didn't check things out to see if there was a paid support option. It's one of those questions that I always ask as a matter of rote.
On question 7, the one about rebuilding CIs before NCIs, I agree that it hasn't been a "problem" since 2k5 and I apologize because I didn't explain the "optimization" I was talking about. If a database is going to grow, there's a pretty good chance that it will grow during the rebuild of the largest clustered index. The optimization is that if you rebuild the largest index first, none of the other indexes will cause any growth at all. It doesn't matter so much for the MDF especially when instant file initialization is enabled but it will always matter if the rebuild is fully logged. Again, apologies for not being more clear on that.
On question 1, you wrote
1. Does it handle Partitioned Tables and Indexes without having to manually identify such things?
Currently it doesn't do anything special for partitions, but we're going to blow that out of the water in v.2 later this year.
Sorry for not doing a dive on the code yet but does that actually mean that it doesn't handle partitioned tables or that it does but doesn't do anything "special" (can't imagine what "special" would mean in such a case)?
3. When in the ONLINE mode, does it automatically exclude (do OFFLINE) indexes with LOB involvement prior to 2014 or do you have to make an entry in the control table for that?
Online mode only does those that can be done online.
Just for absolute clarity, that means that if you specifically select the ONLINE mode and the tables have LOBs in them, that the clustered indexes won't be rebuilt even if they need it or???
BTW... I think you guys doing this is awesome. I don't ask questions about things that I'm not interested in. Thanks for your continued participation in the community of SQL Server.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Not a problem Jeff, I don't mind these kinds of deep Qs about the product. I welcome them in fact.
So let's get started.
"Sorry for not doing a dive on the code yet but does that actually mean that it doesn't handle partitioned tables or that it does but doesn't do anything "special" (can't imagine what "special" would mean in such a case)?"
I'll answer the question you asked and then I'll answer the Q I think you meant to ask.
Yes you can reindex partitioned tables. I don't see why not.
No, it doesn't currently reindex individual partitions. There are some cool things we can do in this space and like I said, v.2 will cover them. I wanted to get this out in the field first and get people using it and then go for the deep stuff like that. Don't worry though, partition support will be excellent.
"Just for absolute clarity, that means that if you specifically select the ONLINE mode and the tables have LOBs in them, that the clustered indexes won't be rebuilt even if they need it or???"
I labored over this feature for quite some time as to how I should handle it. I *think* what I landed on is that whenever you have ONLINE set, and the index will be forced into OFFLINE for whatever reason, I don't process it. My thinking was that I didn't want to have you think you were doing ONLINE reindexes and then get a table locked up on you when you promised your users that wouldn't happen. Like I said, I think that's what I landed on. Let me look at the code tonight and see and I'll give you a definitive answer.
Thanks for all your interest. We love this product and we're very proud of the work we've done here. It's only v.1 but we've already got so much functionality. And we built it to make it easy for you to write custom solutions yourself (hopefully) as easily as possible and w/o having to create any new jobs. We've got quite a few DBAs doing some pretty cool things with the product.
Anyway, I'm going to finish this by saying if you like this, wait till you see Minion Backup. It's coming soon.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Actually, I just looked at the code and here's what I found:
1. If the edition of sql doesn't support online reindexing, MR will set the Online option to OFFLINE regardless of what you put in the Settings tables. The reasoning is that you might have thought you had Ent and you actually had Std and I didn't want your reindexes to fail. You maight have done a mass install and configured it with all ONLINE. So if that's the case, MR will change it to offline for you on your Std boxes. It won't change it in the Settings tables, it'll just change it at runtime.
2. If the routine is called with @IndexOption = 'OFFLINE', then all of the indexes that can be done ONLINE will be dropped from the process and only the OFFLINE indexes will be processed. And the exact opposite is true for @IndexOption = 'ONLINE'. The reasoning here is to give you a way to do all of your online reindexes during the week for example, and then have a special run on the wknd or maybe every couple wks for your offline reindexes.
So yeah, the reasoning again is that if you specify an online IndexOption, I don't want to throw in an offline reindex on you because that's not what you wanted. So the IndexOption means to say ONLY do the reindexes that can be done with that setting.
There's one more scenario. What if you have @IndexOption = 'All' or NULL? What happens if you have a table set to ONLINE but the current index has a LOB and can't be done online? Well, in that case, since the IndexOption is set to All, then MR will change the option to OFFLINE and continue. And we do that because since you don't have an expectation that none of your reindexes will do any table locking, you expect that to happen, so we reindex the table offline since itwas your clear intent to reindex all tables no matter what.
Ok, I hope this helps. I like to not only tell how the feature works, but also the reasoning behind it because it helps you understand what we were thinking and if there's a hole in the feature, you'll know what it was intended to do. And who knows, maybe it'll give you an idea on a way to make it better.
So anyone else? Keep the Qs coming and I'll do my best to answer them promptly.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Good stuff, Sean & Jen. Thanks for taking the time to hammer that out for us.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Grant, Thanks for bringing this to our attention.
Sean and Jen, I'm impressed by your responsiveness.
-
benjamin.seidle (1/27/2015)
Any thoughts on how these stack up with the scripts from Ola Hallengren that some of us know?I think they stack up quite well. In fact, there's some degree of flexibility and ease of use that Ola's scripts don't have. Personally, I could recommend either set. I don't want to get in the middle of Mommy & Daddy fighting. But individuals will find strengths and weaknesses either way. My principal concern is that people use something to maintain their indexes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
marcia.j.wilson (1/27/2015)
Grant, Thanks for bringing this to our attention.Sean and Jen, I'm impressed by your responsiveness.
You're welcome.
I'm unimpressed by their responsiveness because I expected nothing less. 😛
Nah, I figured they would take care of this after I got the review finished. Glad to see you guys jumping on it with both feet.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Thanks for the review. Looking forward to trying it out.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply