Some run big data demos on laptops. Some technologists say big data is data sets that are so large or complex that traditional methods and practices are no longer adequate. My databases fall very short of big, but when I start looking at the number of objects I venture into well above average and maybe even bigish. I don’t have published metrics to back this up, but in my decade plus time with SQL Server I haven’t seen anything like what I deal with these days. I support a MS Dynamics Nav database with a mind boggling number of objects. Maybe my mind boggles a bit easier than others, but when I see 162,525 tables and 371,629 indexes my brain spins a bit. The copious number of objects comes from a setup where each company in the financial system has its own set of tables, nearly 1,600 tables per company. Each country we work in and each legal entity has its own company and more than 80 of those later (and growing…) BAM we’re in the hundreds of thousands of objects.
So what do you do with hundreds of thousands of indexes? DEFRAG THEM! Of course. Indexes are bound to be fragmented because data must be added and deleted and modified in those tables. Right? What’s the point of having 162 thousand tables if you aren’t constantly filling them with mission critical data. Time to throw a standard maintenance plan rebuild task at it. What could possibly go wrong with trying to rebuild 371 thousand indexes on a regular basis?
I don’t know, I’ve never tried that.
I skipped ahead and ran my own script against it which loaded the results of sys.dm_db_index_physical_stats into a temp table, declared a cursor (yikes!) and reorganized when avg_fragmentation_in_percent was between 10 and 30 and rebuilt when it was over 30. It ran OK, it was doing its job and it was running for around 45 minutes on a weekly basis. That was working and nobody was complaining, at least not about that part. Why mess with things?
My first go around with Ola Hallengren’s scripts did not go so well, but that was a while ago and I failed to take notes. I’m sure something very pressing was going on that prevented me. Yes, very pressing. This guy doesn’t get praise and accolades out the wazoo (or whatever the Swedish word for wazoo is) if he isn’t doing amazingly awesome things. I must have not tried hard enough. MOAR OLA! I gave Ola’s IndexOptimize script another shot. I’ve written a few things about my implementation of Ola’s scripts, but the TL;DR is that they are working. In the beginning this script worked on 1,600 indexes, a few less than the 371 thousand available. Now it floats between 400 and 800, usually taking less than 10 minutes. It’s not perfect yet, there are times when the job run over an hour and I am ignoring an elephant in the room.
With IndexOptimize running in production I can look into trying new things in development. Minion Reindex by Midnight SQL was introduced as an easy to install, easy to run and very customizable index maintenance tool kit. set up is a snap and there are many ways to tweek and control how it runs. I wanted to see how this ran out of the box. Out of the .zip is more accurate, but doesn’t have the the same consumerist snap to it.
@IndexOption = 'All', -- Valid options: All, ONLINE, OFFLINE
@ReorgMode = 'REORG', -- 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 = 0 -- Allows Live Insight through Log tables.
Minion has a few indexes to chew through. This is running on the dev box which has a slightly larger database than production.
sp_whoisactive is showing a run time of over 2 days, and 36 billion reads. Looking at the Status field of the Minion.IndexMaintLogDetails table at the same time shows me that it is almost complete, 383,484 of 386,105.
Minion has run a few times with varied success. Two runs completed and lasted nearly 2 and 1/2 days each, then a failure on the 3rd time. I haven’t dug in to figure out what Parameter 3 is, but apparently it was incorrect. I can appreciate the extra level of detail and control here, but I appreciate even more a job that finishes successfully and in less time.
What else do you with indexes besides maintenance? Make them better! How? With sp_BlitzIndex of course. It takes a while to run, but it gave me nearly 300 thousand easy to implement suggestions on how I can improve the indexes I have to deal with.
|Feature-Phobic Indexes: No indexes use includes||1|
|Feature-Phobic Indexes: Potential filtered index (based on column name)||2,544|
|Index Hoarder: Addicted to nulls||1,272|
|Index Hoarder: Addicted to strings||12,088|
|Index Hoarder: Borderline: Wide indexes (7 or more columns)||37,948|
|Index Hoarder: Many NC indexes on a single table||6,996|
|Index Hoarder: More than 5 percent NC indexes are unused||1|
|Index Hoarder: Unused NC index||1,573|
|Index Hoarder: Wide clustered index (> 3 columns OR > 16 bytes)||129,348|
|Index Hoarder: Wide tables: 35+ cols or > 2000 non-LOB bytes||23,957|
|Multiple Index Personalities: Borderline duplicate keys||76,852|
|Self Loathing Indexes: Active heap||1|
|Workaholics: Scan-a-lots (index_usage_stats)||5|
|Workaholics: Top recent accesses (index_op_stats)||5|
Unfortunately this is a 3rd party database and I can’t make any schema changes. Maybe that’s not so unfortunate, I’m not sure I’d want to be held responsible for the creation of this monstrosity.
Thanks to Ed Leighton-Dick and others for getting this going.