Support Center

Order tables by row count

Last Updated: Oct 23, 2015 06:27AM CDT
We get asked sometimes how you can make MR sort tables and indexes by page count or how to process tables and indexes that are over a certain row count or page count.  While v.1 doesn't do this natively, it's not that hard to get what you need.
There are different ways you can tackle this problem.
1.       The easiest is to enter rows into SettingsTable for these tables and raise their thresholds so they don’t get processed every day.  In fact you can raise them high enough that they won’t ever get processed.  A simple query to find their row counts and inserting them into that table will do the trick.  Now, if you want to make that more dynamic you can create a step in the job that runs before the reindex that does this inserting and deleting to keep that table up to date so if any of those tables grow then they’ll start being processed.
2.       The previous one goes off of table row count.  But if you really want a clean run then you’ll want to process it at the index level.  This too is fairly straight forward, it just takes a small amount of coding.  Again you’re going to use a prestep, only this time you’ll use 2 I think.  So the first prestep you will run with @PrepOnly.  This will put all of the indexes to be processed into the IndexTableFrag table.  However, you’ll also want to set LogIndexPhysicalStats = 1 in the Settings table.  That holds all of the current index frag details from the dmv in a static table so you can look at them.  Now that you’ve got that data in the table, and the indexes that are going to be processed in another table, you can use this next step to delete from IndexTableFrag the indexes that don’t have enough pages in the IndexPhysicalStats table.  A simple join on DB and table and indexID should do the trick.  If you’re going to run several jobs at once don’t forget to limit it to the latest ExecutionDateTime.  Also, the IndexPhysicalStats table doesn’t clean up after itself so once you’re finished deleting your unwanted rows, you should delete the rows.  But keep the rows in IndexTableFrag cause the reindex step will need it and MR will clean it up for you.  Now that you’ve got both your steps down, the 3rd step is the one that’s already there… the one that runs the reindex itself.  This time you’ll just need to run it with @RunPrepped = 1 and @PrepOnly = 0.  Now you’re set.
This combines a couple techniques and was intended to give you a method for doing whatever you need to do.  With other reindex solutions, it’s not possible to perform fine-grained operations on your indexes.  Here, without any extra jobs you can do your indexing based off of anything you want.  It can be page count, or row count or anything else you can query.  Once you’ve got that list of indexes in IndexTableFrag, whatever else you do with that data is up to you.  You can manipulate it using any criteria you like.  I’ve got someone else doing this exact same solution and they’re doing it based off of whether they’ve got enough space to perform the reindex.  They do some calculation and delete indexes out of the list based off of that.  Crazy if you ask me, but they were very happy they didn’t have to modify my code to make it happen.  Just add a couple presteps and they were done.
So I hope this gives you what you need.  You’ll not only find it flexible, but you can start coming up with different solutions that aren’t available with any other product.  And it’s the PrepOnly and IndexPhysicalStats features that make that possible.  By saving that data for you, we’ve given you a powerful way to solve even your most complex problems.
Hell, PrepOnly wasn’t even created for this in the beginning.  At first I just wanted to give you way to have the index frag stats calculated before your maint window so you could spend the entire time reindexing instead of running the stats… then as I began testing I quickly saw that it was very useful for stuff like this.  Then the rest was history.
Anyway, I hope you see how flexible this is and how easy it would be to use the same solution again and again on different servers, and just change the criteria as you need it.
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
Invalid characters found