December 19, 2017 at 10:10 am
All,
I am looking into optimizing Ola's maintenance solution by tweaking around some parameters that is part of the stored proc. However, it is becoming really difficult to have the run time reduced in order to defrag the indexes across multiple VLDB's. Is there a way I can cut down the run time and still complete the maintenance routine across all our databases (we have around more than 50 databases and most of them are like in the 300 to 700 GB range with some of them even being a TB in size). Also, kindly, let me know if there is a better way of doing it or a better solution out there. Our maintenance window starts at 8p on Friday and extends up until Sunday midnight.
EXECUTE dbo.IndexOptimize
@databases = 'USER_DATABASES',
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 50,
@MaxDOP = 8,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@SortInTempdb = 'N',
@StatisticsSample = 100
December 19, 2017 at 10:48 am
ffarouqi - Tuesday, December 19, 2017 10:10 AMAll,
I am looking into optimizing Ola's maintenance solution by tweaking around some parameters that is part of the stored proc. However, it is becoming really difficult to have the run time reduced in order to defrag the indexes across multiple VLDB's. Is there a way I can cut down the run time and still complete the maintenance routine across all our databases (we have around more than 50 databases and most of them are like in the 300 to 700 GB range with some of them even being a TB in size). Also, kindly, let me know if there is a better way of doing it or a better solution out there. Our maintenance window starts at 8p on Friday and extends up until Sunday midnight.
EXECUTE dbo.IndexOptimize
@databases = 'USER_DATABASES',
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 50,
@MaxDOP = 8,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@SortInTempdb = 'N',
@StatisticsSample = 100
You may want to check the following article for some ways to address index maintenance with VLDBs:
SQLU VLDB Week – Index Maintenance
Sue
December 19, 2017 at 11:04 am
Sue_H - Tuesday, December 19, 2017 10:48 AMffarouqi - Tuesday, December 19, 2017 10:10 AMAll,
I am looking into optimizing Ola's maintenance solution by tweaking around some parameters that is part of the stored proc. However, it is becoming really difficult to have the run time reduced in order to defrag the indexes across multiple VLDB's. Is there a way I can cut down the run time and still complete the maintenance routine across all our databases (we have around more than 50 databases and most of them are like in the 300 to 700 GB range with some of them even being a TB in size). Also, kindly, let me know if there is a better way of doing it or a better solution out there. Our maintenance window starts at 8p on Friday and extends up until Sunday midnight.
EXECUTE dbo.IndexOptimize
@databases = 'USER_DATABASES',
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 50,
@MaxDOP = 8,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@SortInTempdb = 'N',
@StatisticsSample = 100You may want to check the following article for some ways to address index maintenance with VLDBs:
SQLU VLDB Week – Index MaintenanceSue
I already looked into it but it doesn't offer me the flexibility to run across multiple databases apart from that I don't think it would be same as Ola's solution.
December 19, 2017 at 11:24 am
ffarouqi - Tuesday, December 19, 2017 11:04 AMSue_H - Tuesday, December 19, 2017 10:48 AMYou may want to check the following article for some ways to address index maintenance with VLDBs:
SQLU VLDB Week – Index MaintenanceSue
I already looked into it but it doesn't offer me the flexibility to run across multiple databases apart from that I don't think it would be same as Ola's solution.
There isn't anything in that post that would do anything other than check fragmentation levels - the rest is all explanations and some suggested alternatives.There is no solution up on that site. There are others who have had problems with those scripts with VLDBs but they all wrote their own solutions or modified OLAs so those will not work. If you only want to use Ola's scripts and have no other deviations, you should refer to his site. The contact pages lists ways to find and posts questions on Stack Exchange and has a link to his FAQs:
Ola Contact
Sue
December 20, 2017 at 5:27 am
I'd suggest you take a look at the Minion tools. They have a great deal more flexibility and management than Ola's scripts. I'm not knocking Ola's scripts, they serve a purpose, and serve it well, but can be less optimal in some situations like what you're dealing with now.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 20, 2017 at 9:35 am
ffarouqi - Tuesday, December 19, 2017 10:10 AMIs there a way I can cut down the run time and still complete the maintenance routine across all our databases (we have around more than 50 databases and most of them are like in the 300 to 700 GB range with some of them even being a TB in size). Also, kindly, let me know if there is a better way of doing it or a better solution out there.
Yes... stop doing index maintenance until you've determined that, at the index level, that such maintenance is actually providing a benefit. That also means establishing what the best FILL FACTOR should be for each index which, ironically, is a form of fragmentation itself.
Almost everyone rebuilds indexes based on "logical" fragmentation, which has zero effect on singleton lookups from the front-end and, with the way SANs operate and the fact that so many people are hitting the disks/memory in a time-share fashion and the fact that very few, if any, tables with any decent weight to them will actually have all of their pages nicely lined up as fully contiguous adjacent pages on the disk or in memory. I'll also state that by the time logical fragmentation goes above 5%, page splits (the "bad" kind) are already happening.
My short recommendation is to stop looking at logical fragmentation and start looking at percent of page fullness, instead. If the page fullness is well below that expected (your FILL FACTOR determination is fairly key here), then defrag the index back to the FILL FACTOR. If the page fullness is over 90% and it's not a static index, consider doing an index REBUILD because a REORGANIZE (according to Books Online and some testing I've been doing) won't actually take the page fullness back DOWN to the desired FILL FACTOR.
MUCH more important are statistics. You'll get more bang for the buck if you spend more time updating statistics than defragging indexes that probably don't need it because they've settled in on a "Natural Fill Factor". Yes, there will be some indexes that need defragging because of code that deletes rows or updates variable width columns to be a larger size but those will all become fairly apparent when you check for the average percent of page fullness.
And, yeah... I practice what I preach here... I've not rebuilt any indexes as a part of any regular maintenance on my production boxes in nearly two years. Performance actually got measurably better in the first 3 months and hasn't degraded since. I've recently defragged (using REBUILD) only one table (CI) and all but one of the NCIs on it that gets hit with a ton of deletes just to recover some space both on disk and in memory..
--Jeff Moden
Change is inevitable... Change for the better is not.
December 20, 2017 at 9:49 am
Ah... almost forgot the most important part...
If you have indexes that have a FILL FACTOR of 0 or 100 and the average percent of page fullness decreases and you're rebuilding or reorganizing those indexes, then you're actually CAUSING performance issues because you'll leave those indexes no choice. The pages will all be as full as possible after you index maintenance and they WILL cause "bad" page splits. If you do nothing else and you insist on defragging indexes, find those types of indexes and establish what the FILL FACTOR should be before you defrag them again.
Of course, my recommendation is to just stop the wholesale maintenance/defragging indexes based on logical fragmentation altogether and get "more surgical" in your efforts. Spend the time on more effectively rebuilding statistics, instead.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 4, 2018 at 4:41 pm
I experimented with Ola's idea form quite some time and eventually rolled my own solution in which I keep track of every index (page count, fragmentation level, time required to reorg and to rebuild). I can I've found that on most of my tables with over 2000 pages, reorg takes 1.5 to 3 times longer than rebuild. For this reason I'm going to 'rebuild' only.
January 4, 2018 at 4:51 pm
RandomStream - Thursday, January 4, 2018 4:41 PMI experimented with Ola's idea form quite some time and eventually rolled my own solution in which I keep track of every index (page count, fragmentation level, time required to reorg and to rebuild). I can I've found that on most of my tables with over 2000 pages, reorg takes 1.5 to 3 times longer than rebuild. For this reason I'm going to 'rebuild' only.
I'll say it again... unless you've done a FILL FACTOR analysis, especially on the larger indexes, any form of rebuild or reorg may actually be causing performance problems as soon as the maintenance is complete.
Also, if you're waiting until >5% fragmentation appears on indexes that do get fragmented (and you've set the correct FILL FACTOR), you're waiting too long because the damage to performance in the form of page splits has already begun and allowed to occur for way too long, especially on larger tables.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 4, 2018 at 5:11 pm
Jeff Moden - Thursday, January 4, 2018 4:50 PMRandomStream - Thursday, January 4, 2018 4:41 PMI experimented with Ola's idea form quite some time and eventually rolled my own solution in which I keep track of every index (page count, fragmentation level, time required to reorg and to rebuild). I can I've found that on most of my tables with over 2000 pages, reorg takes 1.5 to 3 times longer than rebuild. For this reason I'm going to 'rebuild' only.I'll say it again... unless you've done a FILL FACTOR analysis, especially on the larger indexes, any form of rebuild or reorg may actually be causing performance problems as soon as the maintenance is complete.
Also, if you're waiting until >5% fragmentation appears on indexes that do get fragmented (and you've set the correct FILL FACTOR), you're waiting too long because the damage to performance in the form of page splits has already begun and allowed to occur for way too long, especially on larger tables.
Thanks, Jeff. I'll do a quick check now.
January 4, 2018 at 5:37 pm
RandomStream - Thursday, January 4, 2018 5:11 PMJeff Moden - Thursday, January 4, 2018 4:50 PMRandomStream - Thursday, January 4, 2018 4:41 PMI experimented with Ola's idea form quite some time and eventually rolled my own solution in which I keep track of every index (page count, fragmentation level, time required to reorg and to rebuild). I can I've found that on most of my tables with over 2000 pages, reorg takes 1.5 to 3 times longer than rebuild. For this reason I'm going to 'rebuild' only.I'll say it again... unless you've done a FILL FACTOR analysis, especially on the larger indexes, any form of rebuild or reorg may actually be causing performance problems as soon as the maintenance is complete.
Also, if you're waiting until >5% fragmentation appears on indexes that do get fragmented (and you've set the correct FILL FACTOR), you're waiting too long because the damage to performance in the form of page splits has already begun and allowed to occur for way too long, especially on larger tables.
Thanks, Jeff. I'll do a quick check now.
The first thing to check for would be any indexes that have a 0 or 100 FILL FACTOR that do get fragmented. It's horribly counter productive to rebuild those because they spend the entire next day splitting again. If you don't change the FILL FACTOR on such indexes, then (with only a couple of exceptions) you'll get a much bigger bang for the buck in just letting them fragment to form their own "Natural Fill Factor".
--Jeff Moden
Change is inevitable... Change for the better is not.
January 5, 2018 at 6:03 am
RandomStream - Thursday, January 4, 2018 4:41 PMI experimented with Ola's idea form quite some time and eventually rolled my own solution in which I keep track of every index (page count, fragmentation level, time required to reorg and to rebuild). I can I've found that on most of my tables with over 2000 pages, reorg takes 1.5 to 3 times longer than rebuild. For this reason I'm going to 'rebuild' only.
Brad McGehee did a series of tests a while back that showed that Reorg was largely a waste of time. It cost cycles but didn't deliver enough to make it worth bothering. You can probably track those down if you're interested.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 5, 2018 at 8:50 am
Grant Fritchey - Friday, January 5, 2018 6:03 AMRandomStream - Thursday, January 4, 2018 4:41 PMI experimented with Ola's idea form quite some time and eventually rolled my own solution in which I keep track of every index (page count, fragmentation level, time required to reorg and to rebuild). I can I've found that on most of my tables with over 2000 pages, reorg takes 1.5 to 3 times longer than rebuild. For this reason I'm going to 'rebuild' only.Brad McGehee did a series of tests a while back that showed that Reorg was largely a waste of time. It cost cycles but didn't deliver enough to make it worth bothering. You can probably track those down if you're interested.
One of the problems with Reorg, which is completely documented in BOL but worded in a way so as to not be obvious (and my testing proved it to me) is that if the pages are filled larger than the given FILL FACTOR for the index (and that actually happens a whole lot with indexes), Reorg will NOT make free space on the pages like Rebuild will. If the page fullness is less than the given FILL FACTOR, Reorg will fill the pages to approximately that point.
I agree that Reorg is nearly a complete waste of time even if all you have is the Standard Edition. I'll also state again that my now 2 year long test of NOT rebuilding or reorganizing indexes as a general practice, especially if you make the mistake of using % of fragmentation as an indication as to when to do any kind of defrag, continues to demonstrate (to me) that index maintenance is pretty much a waste of time unless you have carefully designed fill factors for each index AND you do the defrags prior to the page splits occurring that cause the % of fragmentation. The best way to check for things is to check the average page fullness. When it get's close to being full, it's time to defrag if you're going to defrag. With only a couple of exceptions, the natural fill factor formed by not reindexing has worked wonders for me over the last 2 years.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 5, 2018 at 10:07 am
Jeff Moden - Friday, January 5, 2018 8:50 AMGrant Fritchey - Friday, January 5, 2018 6:03 AMRandomStream - Thursday, January 4, 2018 4:41 PMI experimented with Ola's idea form quite some time and eventually rolled my own solution in which I keep track of every index (page count, fragmentation level, time required to reorg and to rebuild). I can I've found that on most of my tables with over 2000 pages, reorg takes 1.5 to 3 times longer than rebuild. For this reason I'm going to 'rebuild' only.Brad McGehee did a series of tests a while back that showed that Reorg was largely a waste of time. It cost cycles but didn't deliver enough to make it worth bothering. You can probably track those down if you're interested.
One of the problems with Reorg, which is completely documented in BOL but worded in a way so as to not be obvious (and my testing proved it to me) is that if the pages are filled larger than the given FILL FACTOR for the index (and that actually happens a whole lot with indexes), Reorg will NOT make free space on the pages like Rebuild will. If the page fullness is less than the given FILL FACTOR, Reorg will fill the pages to approximately that point.
I agree that Reorg is nearly a complete waste of time even if all you have is the Standard Edition. I'll also state again that my now 2 year long test of NOT rebuilding or reorganizing indexes as a general practice, especially if you make the mistake of using % of fragmentation as an indication as to when to do any kind of defrag, continues to demonstrate (to me) that index maintenance is pretty much a waste of time unless you have carefully designed fill factors for each index AND you do the defrags prior to the page splits occurring that cause the % of fragmentation. The best way to check for things is to check the average page fullness. When it get's close to being full, it's time to defrag if you're going to defrag. With only a couple of exceptions, the natural fill factor formed by not reindexing has worked wonders for me over the last 2 years.
That's really interesting to hear. I'm not surprised by it. I suspect it won't work for everyone though. So many people have really poor queries, lots of scans. Defrag helps, a little, there. Still, this is great information to have.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 5, 2018 at 3:11 pm
Grant Fritchey - Friday, January 5, 2018 10:07 AMJeff Moden - Friday, January 5, 2018 8:50 AMGrant Fritchey - Friday, January 5, 2018 6:03 AMRandomStream - Thursday, January 4, 2018 4:41 PMI experimented with Ola's idea form quite some time and eventually rolled my own solution in which I keep track of every index (page count, fragmentation level, time required to reorg and to rebuild). I can I've found that on most of my tables with over 2000 pages, reorg takes 1.5 to 3 times longer than rebuild. For this reason I'm going to 'rebuild' only.Brad McGehee did a series of tests a while back that showed that Reorg was largely a waste of time. It cost cycles but didn't deliver enough to make it worth bothering. You can probably track those down if you're interested.
One of the problems with Reorg, which is completely documented in BOL but worded in a way so as to not be obvious (and my testing proved it to me) is that if the pages are filled larger than the given FILL FACTOR for the index (and that actually happens a whole lot with indexes), Reorg will NOT make free space on the pages like Rebuild will. If the page fullness is less than the given FILL FACTOR, Reorg will fill the pages to approximately that point.
I agree that Reorg is nearly a complete waste of time even if all you have is the Standard Edition. I'll also state again that my now 2 year long test of NOT rebuilding or reorganizing indexes as a general practice, especially if you make the mistake of using % of fragmentation as an indication as to when to do any kind of defrag, continues to demonstrate (to me) that index maintenance is pretty much a waste of time unless you have carefully designed fill factors for each index AND you do the defrags prior to the page splits occurring that cause the % of fragmentation. The best way to check for things is to check the average page fullness. When it get's close to being full, it's time to defrag if you're going to defrag. With only a couple of exceptions, the natural fill factor formed by not reindexing has worked wonders for me over the last 2 years.
That's really interesting to hear. I'm not surprised by it. I suspect it won't work for everyone though. So many people have really poor queries, lots of scans. Defrag helps, a little, there. Still, this is great information to have.
You'd be really surprised. I've found that people that are using defrag incorrectly (many definitions, some of which I included above) is actually making a lot of crap code that does a lot of scans look worse because of the really nasty bad page splits (which result in ~50% page fullness) on the data they read the most, which is usually the latest data that just split the dickens out of the latest current data.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply