Ola's maintenance solution taking too long on VLDB's
- ffarouqi - Tuesday, December 19, 2017 10:10 AM
We have some TB scale tables, and we also use Ola Hallengren's index maintenance jobs. You didn't mention partitioning, which is essential for when operating at this scale. Read up on the PartitionLevel option. I don't partition every table and index, only the largest, but when it comes to incremental maintenance operations, it really shines.
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Maintain partitioned indexes on the partition level. If this parameter is set to Y, the fragmentation level and page count is checked for each partition. The appropriate index maintenance (reorganize or rebuild) is then performed for each partition.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
ALTER INDEX { index_name | ALL } ON <object>
{
REBUILD {
[ PARTITION = ALL ] [ WITH ( <rebuild_index_option> [ ,...n ] ) ]
| [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> ) [ ,...n ] ]
}
| DISABLE
| REORGANIZE [ PARTITION = partition_number ] [ WITH ( <reorganize_option> ) ]
| SET ( <set_index_option> [ ,...n ] )
| RESUME [WITH (<resumable_index_options>,[âŚn])]
| PAUSE
| ABORT
}
[ ; ]"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
I would start by only doing rebuilds - especially if you're on Enterprise Edition and that MAXDOP = 8 is helping you more than MAXDOP = 1 would. I agree with the other posters; reorganize is a waste. It has one benefit over rebuild - if you stop it partway through, some of its work remains.
An easy thing is that you can also try tuning the MAXDOP - is 8 too high? I'd guess that MAXDOP 3 or 4 would deliver nearly all of the performance at a much lower CPU cost. It's certainly at the upper limit of what I'd do. If you have dedicated, separated disks - or have databases on 100% flash storage - you can experiment with running two lower maxdop index maintenance jobs in parallel, and see if the aggregate result is superior. If you're on shared spinning disks, expect it to get worse instead.
Do make sure you're doing statistics maintenance, as Jeff said.
More importantly, while I don't agree with the "turn off index maint" part of Jeff's statement, I 100% agree with FILLFACTOR maintenance and care, to the point that years ago I started changing the DEFAULT fillfactor to less than 100%, on the premise that it's an exceptional case where 100 is ideal for most indexes across the board, not a normal case. Leave a little room for new/larger (VARCHAR) rows on each page, and you're likely a lot happier.
Monitor which indexes are getting rebuilt EVERY time; consider those for dropping fillfactor - they probably have rows added or enlarged "in the middle" regularly.
If you can, I also split my maintenance by size; tiny things get rebuilt and their statistics updated much more often than big ones that take time, since it's nearly free (a few minutes during a short nightly maintenance/slowness OK time) if you have some time nightly where performance degradation or brief blocking is ok. If you don't have that time, obviously don't do that!
- Nadrek - Saturday, January 6, 2018 2:52 PM
Unfortunately, the window is slightly tighter for me and there is not much I can do. I'll have to keep it within that time frame. We have more than 50+ databases and it would be difficult to baby sit each and every table and look for indexes to leave room for fill factor. My only question that I asked surfaced around what options should I chose in order to run the index maintenance job successfully without much issues in that window that I have. I would at least say that there are a few databases on which it generally spends a lot of time doing index rebuilds/reorgs and update stats with full scan. I can remove these databases off of the maintenance but then how would I handle these databases separately. I still have to run maintenance on them. Is there a script or a maintenance solution that you'll use which can collect the stats way ahead of the maintenance schedule and in case if it runs during that window and doesn't finish during that tighter time frame it can collect those metrics in a table and work its way out by defragging where it left so it won't have to do everything all over again.
- ffarouqi - Tuesday, January 9, 2018 10:58 AM
I have a weekly 4-hour window for index maintenance on a 1.8TB database. Prior to my adventure to roll my own scripts, the full index job implemented by the vendor took 8 to 9 hours. They simply chose to rebuild everything, even archive tables that no one will ever query against. Here is what I did.
Create 3 tables:
1. base table: This table keeps track of every table and index, page count, current fragmentation level. It is also provisioned to hold statistics like start and end times of reindex (reorg too but I gave up on that), previous page count, previous fragmentation level (prior to current reindex job). This table is updated weekly during job estimate, to capture new page counts as well as newly added tables and indexes.
2. job table: This table keeps track of reindex job scheduling and execution. For instance, it will contain target database name, beginning and ending jobstep id's, job start and end times, estimated duration and actual duration, and total number of indexes that will be indexed during the next run.
3. job detail table: For each job, a detailed step is generated to for every index that needs to be rebuilt. It keeps track of page count, fragmentation level, estimate duration, start and end times, actual duration, command used (originally I had rebuild and reorg command types).Create 2 jobs.
Estimate job: Every Friday at noon, an estimate job is run. This job checks fragmentation level of every single index. Based on fragmentation level, it populates the base table, generates a new job and create an entry for every index to be rebuilt in job detail table. If the index was previously rebuilt, the previous duration would be retrieved from the base table. This piece of info is used as the time estimate.Index rebuild job: On Friday night, during maintenance window, the commands from job detail table are executed. As they are executed, start and end times and actual duration is recorded in both base table and job detail table. Of course, job table is also updated so at any time I know exactly which index is being rebuilt, if I care to check.
This process will overtime populate the base table with actual rebuild durations. So the weekly estimate will gradually become more accurate. At this moment the actually seems to be within 30-min from my estimate.
If you or anyone is interested to try I can script out everything to share. I've love to hear any suggestions on the design and SQL scripts.
- ffarouqi - Tuesday, January 9, 2018 10:58 AM
If you don't have the time to come up with correct FILL FACTORs, then at least stop doing index maintenance (but DO continue to rebuild stats when needed) on the indexes that have a 0/100% FILL FACTOR that aren't based on an ever ascending key (typically an IDENTITY, SEQUENCE, or date/time) because it's a never ending, totally non-productive battle (much like doing a "shrink file" every night) that is actually causing performance problems in the form of "bad" page splits during the day.
--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) - RandomStream - Tuesday, January 9, 2018 11:35 AM
This looks promising. Could you please share the script in case if you don't mind. I believe this is what I am after. Hopefully, this would solve all my problems. In case if you don't want to share it in the forum I can provide you my email address to send the details to me separately. Again, thank you so much.
- ffarouqi - Tuesday, January 9, 2018 12:43 PM
I do not mind sharing it here but first let's get it working for you as we may have to go back and forth and do some clean-up. Once it is working for you I can document it and share here. Let me see if I can PM you from here.
-
1) I don't know why anyone hasn't asked the obvious question yet: What is the server you are running all this on? Please provide details, especially on CPU, RAM and IO PERFORMANCE capabilities. Also, what exact edition and build of SQL Server?
2) What schedule are you running defrag under? Are there overlapping jobs (including other stuff such as data loads, extracts, backups, checkdb, etc)?
3) Have you reviewed various tuning metrics such as IO stalls and done a wait stats analysis to find and fix bottlenecks?3) If you are asking questions like this and are responsible for managing that much data I must REALLY REALLY recommend that you hire a professional to a) give your entire system a performance review and b) help you understand your maintenance needs and get all of that set up correctly. I am absolutely confident that this will pay HUGE dividends.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service - TheSQLGuru - Wednesday, January 10, 2018 9:55 AM
BWAAA-HAAA!!! đ If they're doing index maintenance based on % of Fragmentation alone, then it doesn't really matter how good or bad their machine is, which is why I haven't asked. đ
--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) - Jeff Moden - Wednesday, January 10, 2018 4:17 PM
I should point out that you are being QUITE silly with your FILLFACTOR nonsense!! No one bothers with that - EVER. :crazy: And yet it is SO VERY DAMN IMPORTANT that EACH AND EVERY index has the FILLFACTOR (actually ALL of the CREATE INDEX options) INTELLIGENTLY set for ITS data and access patterns!!! Well, it's important if you want an optimal system anyway.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service - TheSQLGuru - Wednesday, January 10, 2018 8:16 PM
Heh... I love it when people do index maintenance of 0/100% Fill Factor indexes at 5%. It reminds me of shrinking a database file every day. So much fun in so much time. đ Then really fun part is that the daily page splits are just starting to slow down/level off and then they start the whole mess over that night by doing the reorg or rebuild. In other words, the index maintenance causes massive bad page splits that last the WHOLE DAY and this is just ONE index!
Here are the first 30 days (I'm writing a presentation and a series of articles on all this) of a non-ever-increasing index with different Fill Factors. The Red saw tooth pattern is the 0/100% fill factor. The lines represent the number of pages the index contains at any given point in time. If the lines go up, page splits happened to make the page counts go up. The Light Blue Line is when the index is based on an ever-increasing column and is an indication of only "good" page splits. The Green line is if you do no index maintenance at all. The Orange Line is ReOrgs... totally useless, total waste of time.
Like I said, the Green line is the line where no defragmentation was done and it formed a "Natural Fill Factor" of 68%. There are almost no "bad" page splits associated with it, either, because of the "Natural Fill Factor" that it formed.
And this was based on a wide clustered index using almost the worst thing possible as the index key... a random GUID. đ
The chart is based on inserting 1,000 items (random GUIDs except for the Light Blue line, which was presorted GUIDs) every simulated hour for 10 hours per day with nightly maintenance possible as indicated in the legend of the chart.
--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) -
Does anyone here besides me implement partitioning on indexes just for taking advantage of incremental reorgs and re-indexing? It depends on the access patterns and chosen partitioning key, but at least in my case only the latest couple of partitions are run hot with inserts and updates, and the older partitions are relatively static.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
- Eric M Russell - Thursday, January 11, 2018 7:23 AM
I do the same as you. They're audit tables, which qualifies them as "temporal WORM" tables. When a month ends, that's the only time I rebuild the indexes for that month and I do it in a strange fashion to pack all of the indexes at a 100% fill factor with virtually no freespace trapped in the partition so that I can make it a Read_Only File Group for purposes of not backing up older partitions every night. I only backup the current (active) month and the next month (which is empty until it becomes current).
As for tables that have active rows in every partition all the time, the only reason why I'd do such a thing might be to make piece-meal restores possible. Partitioning provides no particular advantages for code performance over a monolithic table and I've taken to simply not doing general index maintenance (I do rebuild stats on a very regular basis, though) over the last 2 years and, especially on large monolithic tables, I've taken to using an untraditional method to keep from blowing out the MDF file with unwanted/unnecessary freespace during some rather surgical rebuilds (especially if it's a large clustered index). I've found that ReOrgs are mostly useless, blow out the log file, and take way too long so I stick with index rebuilds using the ONLINE option. (of course, available only in the Expensive Edition).
I'll also state that most of the Clustered Indexes we have never get fragmented because the key is based on a narrow, unique, immutable, ever-increasing value. After two years of no general index maintenance, we have found a couple of clustered indexes that suffered physical fragmentation (page fullness) beyond what is expected due to updates of variable length columns and we've handled those using the more surgical method of defragging those, especially if they have ever increasing keys.
Disclaimer... most of our tables are less than 500GB. We have one (on a telephone system and it's partitioned) that's over a Terra-Byte, about a 2 dozen between 250GB and 500GB and the rest fall into the less (frequently much less) than 100GB area. Even our area code table comes into the "only" 4GB range. So, we're not dealing with true monster size tables here. We do have something less than 2000 tables even on our "big" databases so nothing real crazy there, either. Our main business server weighs in with only about 3.2 TB of data across all the databases.
--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) - Eric M Russell - Thursday, January 11, 2018 7:23 AM
I advise that to clients on Enterprise Edition of SQL Server (which is very few).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply