Start a new topic

Reindex Table Size Threshold

Hello All,

Does Minionware ReIndex have a minimum table size or page numbers threshold similar to that of Ola Hallengren's Index Optimize @MinNumberOfPages Parameter? I have searched the documentation and IndexSettings Tables but had no luck.


Thank You

1 Comment

The answer is y/n.  In v.1 we chose to provide that as part of our pre/post code process.  So it's still quite easy to do, it's just not an actual setting.  Here are the steps to making this happen:

1. Run the routine with @PrepOnly = 1.  This gets all the frag info and puts it into the TableFrag table I believe.  

2. Create an SP to delete the data from the TableFrag table based on your criteria. ** See below for more details.

3. Call the above SP in the batch precode col of the SettingsServer table and call the routine for that row with @RunPrepped = 1.


** If you want to delete tables/indexes based on number of pages, etc you have but to build that into your SP.  So you can set LogIndexPhysicalStats = 1 and that'll save the raw DMV data to a table.  You can then join any of that data to the TableFrag table and delete based on anything.  You can also get the row count for all the tables and delete based on that.  Or join it with index usage and delete the ones that don't get used very often.


The reason we initially chose to do it this way is because it's much more flexible.  You can not only delete based on any criteria you like but you can also order the tables/indexes using any criteria.  This gives you an incredible amount of flexibility.


I've attached some of the ones we've written.  Test them out first before relying on them in prod.  The one you're probably interested in is the Filter script.  Even if you don't use them, it'll give you a good jumping off point.


I'll also add that I've made these and more for v.2 and they've been promoted to settings so you don't have to add them to precode.  And you'l still be able to use you own if you like.


Ok, let me know if you need anymore help.

zip
(9.74 KB)
Login or Signup to post a comment