November 5, 2021 at 9:11 am
Hello,
Are there any recommendations to change the defaults on Ola Hallengren's index maintenance script?
It's been a while. Left out the index_reorganize and might consider timelimit, locktimeout and SortInTempdb
What do you think of it?
@databases nvarchar(max)='USER_DATABASES'
@FragmentationLow nvarchar(max) = NULL, /* don't defrag low fragmented */
@FragmentationMedium nvarchar(max) = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', /* left out index_reorganize because of https://www.sqlservercentral.com/forums/topic/rebuilding-and-reorganizing-indexes/page/2/?bbp-ajax=true */
@FragmentationHigh nvarchar(max) = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 int = 30, /* threshold medium fragmented */
@FragmentationLevel2 int = 50, /* threshold highly fragmented */
@PageCountLevel int = 1000, /* minimum size of table */
@SortInTempdb nvarchar(max) = 'N', /* Should I change this to Y ? */
@MaxDOP int = NULL, /* default maxdop instance*/
@FillFactor int = NULL, /* use existing index fillfactor*/
@PadIndex nvarchar(max) = NULL, /* fill to near capacity */
@LOBCompaction nvarchar(max) = 'Y', /* Should I change it to N ?, we don't use a lot of lob's */
@UpdateStatistics nvarchar(max) = 'ALL', /* ALL just to be sure?*/
@OnlyModifiedStatistics nvarchar(max) = 'N', /* Probably should change this to Y, no sense of recalculating stats if nothing has changed */
@StatisticsSample int = NULL, /* default sql server sample */
@StatisticsResample nvarchar(max) = 'N', /* not sure what updating stats with most recent sample does*/
@PartitionLevel nvarchar(max) = 'Y', /* we don't have partitions, leave it as Y */
@MSShippedObjects nvarchar(max) = 'N', /* no intervening with MS objects*/
@Indexes nvarchar(max) = NULL, /* leave it at the default ALL_INDEXES */
@TimeLimit int = NULL, /* Might consider this to halt when working hours is reached*/
@Delay int = NULL, /* no need for delays yet */
@LockTimeout int = NULL, /* Better set this so it runs within maintenance schedule in combination with timelimit? */
@LogToTable nvarchar(max) = 'Y', /* trace commands to dbo.CommandLog */
@Execute nvarchar(max) = 'Y' /* run the indexoptimize instead of printing commands */
November 5, 2021 at 1:46 pm
Yes. Do not use REORGANIZE. It doesn't work the way you think it does. It's so bad on indexes that fragment that it's actually the cause of the myth of Random GUID Fragmentation. You can see the proof of that at the following presentation/'tube. It turns out that properly maintained Random GUID indexes (especially the Clustered Ones) can go for MONTHS at a time with less than 1% fragmentation just by avoiding the use of REORGANIZE because of the way REORGANIZE works and doesn't work.
https://www.youtube.com/watch?v=qfQtY17bPQ4
The bottom line is that it's better to do no index maintenance than it is to do it wrong and, if you're using REORGANIZE, you're probably doing it wrong. Spend most of your precious maintenance time doing proper FULL SCAN Statistics Maintenance and doing DBCC CHECKDB. Those are much more worthwhile.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2021 at 1:48 pm
Ah... sorry. Looking back at the original post, I see that REORGANIZE was left out. That's ok... it's still worth mentioning for other people that may read this thread.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2021 at 3:16 pm
@databases nvarchar(max)='USER_DATABASES'
@FragmentationLow nvarchar(max) = NULL, /* don't defrag low fragmented */
@FragmentationMedium nvarchar(max) = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', /* left out index_reorganize because of https://www.sqlservercentral.com/forums/topic/rebuilding-and-reorganizing-indexes/page/2/?bbp-ajax=true */
@FragmentationHigh nvarchar(max) = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 int = 30, /* threshold medium fragmented */
@FragmentationLevel2 int = 50, /* threshold highly fragmented */@PageCountLevel int = 1000, /* minimum size of table */
@SortInTempdb nvarchar(max) = 'N', /* Should I change this to Y ? */
@MaxDOP int = NULL, /* default maxdop instance*/
@FillFactor int = NULL, /* use existing index fillfactor*/
@PadIndex nvarchar(max) = NULL, /* fill to near capacity */
@LOBCompaction nvarchar(max) = 'Y', /* Should I change it to N ?, we don't use a lot of lob's */
@UpdateStatistics nvarchar(max) = 'ALL', /* ALL just to be sure?*/
@OnlyModifiedStatistics nvarchar(max) = 'N', /* Probably should change this to Y, no sense of recalculating stats if nothing has changed */
@StatisticsSample int = NULL, /* default sql server sample */
@StatisticsResample nvarchar(max) = 'N', /* not sure what updating stats with most recent sample does*/
@PartitionLevel nvarchar(max) = 'Y', /* we don't have partitions, leave it as Y */
@MSShippedObjects nvarchar(max) = 'N', /* no intervening with MS objects*/
@Indexes nvarchar(max) = NULL, /* leave it at the default ALL_INDEXES */
@TimeLimit int = NULL, /* Might consider this to halt when working hours is reached*/
@Delay int = NULL, /* no need for delays yet */
@LockTimeout int = NULL, /* Better set this so it runs within maintenance schedule in combination with timelimit? */
@LogToTable nvarchar(max) = 'Y', /* trace commands to dbo.CommandLog */
@Execute nvarchar(max) = 'Y' /* run the indexoptimize instead of printing commands */
First of all, if you have indexes that use Random GUIDs, handle them separately. They absolutely need different settings than any other index type. GUID indexes that are fragmenting that are less than 2 years old need to be rebuilt with a FILL FACTOR of 71 or 81 (the "1" is to allow you to easily identify them by Fill Factor alone) and they need to be rebuilt as soon as logical fragmentation goes over just 1%. Your settings above don't cover GUIDs. You can ignore what I just said if you don't have any GUIDs that are fragmenting (static tables will not fragment even if they have GUIDs because, usually, no data is being added or updated).
Ola's good code is, indeed, a "gold standard". There are several problems with it though and they all boil down to just one thing
The way people use it is usually "out to lunch".
For example, you have the following threshold settings...
@FragmentationLow nvarchar(max) = NULL, /* don't defrag low fragmented */
@FragmentationMedium nvarchar(max) = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', /* left out index_reorganize because of https://www.sqlservercentral.com/forums/topic/rebuilding-and-reorganizing-indexes/page/2/?bbp-ajax=true */
@FragmentationHigh nvarchar(max) = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 int = 30, /* threshold medium fragmented */
@FragmentationLevel2 int = 50, /* threshold highly fragmented */@PageCountLevel int = 1000, /* minimum size of table */
There are several different reasons why indexes fragment but the biggest reason in inserts to somewhere other than the logical end of an index or expansive updates anywhere in the index. Let's talk just about those two reasons for now because we'd otherwise end up writing a book instead of a post on a forum.
Do you have any indexes that are fragmenting that are still using the default Fill Factor of "0" which is, in all other respects, the same as using a Fill Factor of 100? WHAT do you think happens if you rebuild those?
The answer is absolute and massive fragmentation on the morning after because you've removed all free space from the index. Your index maintenance is a total waste and, in fact, makes things worse on such indexes. You need to determine a Fill Factor to help stop the fragmentation. Buuuutttttt... don't jump on that just yet because doing that might not help at all and might just waste a whole lot of disk space and memory buffer space.
So, let's say you lower the Fill Factor a bit and the index still fragments. What do most people do? They lower the Fill Factor again. The index still fragments and they lower it again. Wash, rinse, repeat. They get down to 70 or even 60% and the damned thing still fragments as fiercely as it always did. What's the problem?
If the index is based on an ever-increasing key, the problem is that folks are doing inserts, which all go into the logical end of the index and they go in a 100% because, except for one place where inserts will follow the Fill Factor which we won't cover now, inserts don't follow the Fill Factor. They fill the "current" page as full as possible. When it's full, the system does a "good" page split to create a new page at the logical end of the index and starts filling that. The pages are just as full as if you rebuilt them at 100%. There's no room for rows to grow.
Then you do something to those new rows long before any index maintenance occurs. You do an UPDATE for one reason or another. Frequently, this update changes a NULL variable width column to something that's not null. The row has to get larger but there's no room on the page and BOOM! You get a "BAD" page split, which is the cause of most logical and physical (page density) fragmentation. This is known as an "ExpAnsive" Update and lowering the Fill Factor will NOT fix these on such a "hot spot" update where the pages have all been filled to capacity by inserts. The only thing you can do is to prevent the fragmentation by identifying what is causing the row to expand and fixing it by either adding a default to the offending column(s) to prevent expansion during updates OR move the column out of the (especially) Clustered Index.
The other problem is waiting for 30% logical fragmentation. That's only worthwhile if you have ever-increasing indexes that have "hot spot" updates like I mentioned above. Of course, you've not identified those indexes and, even if you have, the "generic use" of Ola's good code (as you've posted) isn't going to pay attention to those findings.
The places where you've assigned a Fill Factor that does help prevent/delay fragmentation usually need the same care that works to prevent fragmentation in GUIDs because the index is probably close to being evenly distributed just like Random GUIDs. Those will probably need to be rebuilt as soon as they go over just 1% logical fragmentation. If you wait until 30% on those, then you've already had the worst page splits happen. If you've not assigned the correct Fill Factor, then you run into the problem I spoke of before... Massive "Morning After" Fragmentation and Blocking.
On top of it all, if the table is used for OLTP (basically, one row at a time), then Logical Fragmentation doesn't actually matter AT ALL!. Every single row query is going to start at the root page of the B-Tree, traverse the intermediate levels, and finally reach the correct page at the leaf level. There's no RANGE SCAN involved and Logical Fragmentation ONLY affects Range Scans.
What all that means (and we've only covered a part of it) is that you're probably measuring the wrong thing to determine if fragmentation needs to be fixed.
If you're doing index maintenance on things that have the default Fill Factor, that's about as effective as drinking to celebrate sobriety. You're just creating a hot-bed source of massive fragmentation.
If you lower the Fill Factor to prevent fragmentation and it doesn't, then you're wasting space and you need to determine the cause of the fragmentation and make it so it doesn't fragment any more (fix "ExpAnsive" updates).
If you lower the Fill Factor and it works in delaying fragmentation, then you need to rebuild at 1% instead of waiting for massive page splits that will cause the fragmentation.
If you don't have the time for any of that, then concentrate on properly rebuilding statistics more often and concentrate on indexes that have a low page density (which Ola's code does NOT measure). Eventually, you WILL need to make some time to figure out what your indexes are actually doing so that you can either make it so they don't fragment anymore or you maintain them properly instead of using recommendations like waiting until after you have 30, 50, or even 70% logical fragmentation. Doing this "generic/panacea" form of index maintenance, whatever form it takes, is killing you and is most likely making both page splits and fragmentation worse than it would be if you did... nothing at all.
To wit, it's better to do no index maintenance than it is to do it wrong. If you're basing your index maintenance on logical fragmentation alone, you're doing it wrong.
After a morning of massive blocking the morning after index maintenance on Monday, the 18th of January 2016, I went 4 years not doing any index maintenance on my big production box and performance actually got better over the first 3 months and then stayed there because I wasn't doing so many page splits. It was an interesting experiment and I learned a lot from it through constant monitoring but I don't recommend that. If you're not going to go through all that I'm still going through to make a better way, then use low page density to drive what you're going to do index maintenance on and, even then, be careful not to cause massive page splits that are more than if you did nothing. You need to recover the wasted space (a page density of 70% does NOT waste 30%... it actually wastes (100/70-1)*100 or 42.857% of your memory and disk space.
And, yeah... there are indexes that are permanently fragmented such as the "Hot Spot" updates I spoke of (if you can't prevent the fragmentation) and a thing that I call "Sequential Silos", which almost instantly fragment and cannot be fixed. It's best to rebuild those only when page density has gotten low enough to be a concern.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2021 at 4:29 pm
Thanks Jeff Moden for the extensive reasoning. Bookmarked this post.
Most of our non-guid indexes are fillfactor 100. If I understand correctly I'll better stop the index maintenance for now and focus on updating statistics and concentrate on indexes that have a low page density
November 5, 2021 at 4:47 pm
regarding reorganize - there is an exception to this - if you have columnstore indexes that are frequently subject to deletions or that are subject on inserts in small batches then running reorg is an option to clear up some of the deleted records (which are kept on the lob until either a rebuild or a reorg)
in some cases rebuilding a columnstore index is not the best option if the space taken is significant due to the log file size required - reorg with on this case does not take as much space and may be preferable
see https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver15 - REORGANIZE a columnstore index
November 5, 2021 at 4:53 pm
That's the gist of it. If you have indexes where logical fragmentation actually does matter for large range scans, you might want to include those but, again, that's going to take some analysis of the individual indexes. Generic rules that people apply to all indexes in the index maintenance are actually working against them. They even (finally... Paul Randal should be overjoyed based on his article at https://www.sqlskills.com/blogs/paul/where-do-the-books-online-index-fragmentation-thresholds-come-from/ ) removed the old 5/30 "Best Practice" recommendation back on 20 April 2021. What they replaced it with is probably a bit complex for most folks because they don't provide a coded method of discovery and analysis of each index.
That latter part is what I have been working on.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2021 at 5:52 pm
regarding reorganize - there is an exception to this - if you have columnstore indexes that are frequently subject to deletions or that are subject on inserts in small batches then running reorg is an option to clear up some of the deleted records (which are kept on the lob until either a rebuild or a reorg)
in some cases rebuilding a columnstore index is not the best option if the space taken is significant due to the log file size required - reorg with on this case does not take as much space and may be preferable
see https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver15 - REORGANIZE a columnstore index
I absolutely agree. What I've been talking about is only for RowStore indexes. ColumnStore indexes are totally different and, as you say, REORGANIZE is a key to properly maintaining those.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply