June 16, 2022 at 5:23 pm
Is there any measurement(query) that can be used to check for correct value used. I have a table that has about 9 million records with 2 additional indexes other table the PK. One of the additional indexes has a fill factor of 0 actualfillfactor of 98 currentfragmentation of 2.0 fragment_count 2600 and avgfrag of 46.
I found this query to help with diagnose numbers from table.
Thanks.
SELECT
tbl.name TableName
, idx.name IndexName, idx.fill_factor
, CAST(Fragmentation.avg_page_space_used_in_percent AS DECIMAL(4,1)) ActualFillFactor
, CAST(Fragmentation.avg_fragmentation_in_percent AS DECIMAL(4,1)) CurrentFragmentation
, Fragmentation.fragment_count
, CAST(Fragmentation.avg_fragment_size_in_pages AS DECIMAL(8,1)) AvgFragmentSize
FROM
sys.tables tbl
INNER JOIN
sys.indexes idx ON tbl.object_id = idx.object_id
CROSS APPLY
sys.dm_db_index_physical_stats(DB_ID(), tbl.object_id, idx.index_id, 0, 'SAMPLED') Fragmentation
WHERE
tbl.name LIKE 'design%' and idx.name like 'ix_design_%'
June 16, 2022 at 7:57 pm
There is currently (though I'm working on it), no code that will make the "best" assignment because things like logical fragmentation are averages at a given time and provide no information at to what the index does over time.
It may also be that your index is what people refer to as "permanently fragmenting". An example of one form of the problem can be found in indexes with an "ever-increasing key". People can do a bazillion INSERTs into such a table and have super low (<1%) fragmentation caused only by the formation of non-contiguous pages that occur naturally and without any page splits.
But, if you turn around and then UPDATE any variable-with column to something larger that what they were at the time of INSERT (which I refer to as "ExpAnsive Updates"), then you end up with MASSIVE page splits and the resulting fragmentation. The reason why these are considered to be "permanently fragmenting" is because, no matter how low you set the Fill Factor to, the newly inserted rows ALWAYS go into the table as if the Fill Factor were set to 100% because INSERTs pay no attention to the Fill Factor (there is one place where they will follow the Fill Factor but this isn't that place). I refer to these type of indexes as a "Type 97" because that's what I set the Fill Factor to recover the wasted space and provide me with a reminder that I have something "2" do to fix them. (The "7" in the Fill Factor is like a footless 2".
A similar type of index is what I call a "Sequential Silo" index and I identify these types of indexes as a "Type 98", where the "8" is like the sign for infinity stood up". These indexes have a leading column (say, like a state or zip code) with a secondary column that's ever-increasing, such as a date or a customer ID (which is usually ever-increasing.
Such an index will fragment like mad and very quickly go over 98% logical fragmentation but they'll never have a low page density. That's because they never actually do a page split. The fragmentation is caused by new pages being created out of order but no pages splits are present... very much like the "Type 97" index above but at multiple "hot spots" throughout the index instead of just one.
Again, lowering the Fill Factor won't do a thing here except waste index space.
And the "Best Practices" for Index Maintenance that the whole world has been using for more than two decades? Yeah... they almost completely incorrect. Even the author of that "starting point recommendation" has basically said so. It turns out that the biggest culprit is the use of REORGANIZE... it doesn't work the way most people think it does even though it's stated correctly in the MS documentation. People just don't realize the ramifications there and REORGANIZE can lead to perpetual fragmentation and page splits and can actually cause the page splits to seriously increase once it's done on a given index just as surely as if you rebuilt the index to 100% on a Random GUID keyed index. It removes all the free space from the index and does nothing to clear out the area above the Fill Factor. It's also NOT the "gentle on resources/log file kitty" that everyone makes it out to be.
And, worse yet, if your index is used in a mostly OLTP fashion (one row at a time), logical fragmentation makes absolutely NO difference in performance because each SELECT will only read one row from one page at a time. Decreased logical fragmentation only helps for index scans or a seek follow my a range scan.
Unless you want to spend several weeks figuring out the "personality" of each index, your best bet is to simply stop doing index maintenance based on logical fragmentation and ONLY REBUILD indexes if they drop below something like 92% or 82% or 72% depending on how often you want the REBUILDs to occur.
If you can't do a REBUILD, then wait until you can actually take the time to figure out what a REORGANIZE is actually going to do to you... it usually has a devastatingly negative effect.
For an introduction to all that I'm talking about, take the time to watch the following 90 minute video. I use Random GUIDs to clearly demonstrate what I'm talking about and then show people how they can be made fragment free for MONTHS at a time and it's NOT just because of a lower Fill Factor. I also cover the very common "Type 97" index problem, as well.
https://www.youtube.com/watch?v=rvZwMNJxqVo
--Jeff Moden
Change is inevitable... Change for the better is not.
June 16, 2022 at 11:36 pm
Thanks Jeff,,
I have been using the formula:
if ($Average_Fragmentation -gt 40.00)
{
Write-Host -ForegroundColor Red "$Index_Name is more than 40% fragmented and will be rebuilt.";
$index.Rebuild();
Write-Host -ForegroundColor Green "$Index_Name has been rebuilt.";
}
elseif($Average_Fragmentation -ge 10.00 -and $Average_Fragmentation -le 40.00)
{
Write-Host -ForegroundColor Red "$Index_Name is between 10-40% fragmented and will be reorganized.";
$index.Reorganize();
Write-Host -ForegroundColor Green "$Index_Name has been reorganized.";
}
else
Just wondering if I'm seeing READ performance issues against this table when original built and Indexes added no FillFactor applied.
This table gets millions of READS thru out a particular process.
Thanks.
June 17, 2022 at 1:53 am
Thanks Jeff,,
I have been using the formula:
if ($Average_Fragmentation -gt 40.00) { Write-Host -ForegroundColor Red "$Index_Name is more than 40% fragmented and will be rebuilt."; $index.Rebuild(); Write-Host -ForegroundColor Green "$Index_Name has been rebuilt."; } elseif($Average_Fragmentation -ge 10.00 -and $Average_Fragmentation -le 40.00) { Write-Host -ForegroundColor Red "$Index_Name is between 10-40% fragmented and will be reorganized."; $index.Reorganize(); Write-Host -ForegroundColor Green "$Index_Name has been reorganized."; } else
Just wondering if I'm seeing READ performance issues against this table when original built and Indexes added no FillFactor applied. This table gets millions of READS thru out a particular process.
Thanks.
That's not SQL. With so many similar looking languages, I don't know what it actually is but I'll never understand why people feel they need to do such things outside of SQL itself. 😉
As to your wondering, you're using just a slight variation of the current "Best Practices" and, IMHO, is just as incorrect as those are. If you don't know why and index is fragmenting, you probably should read what I previously wrote and start to figure out why. As for the use of REORGANIZE, all I can say is that you REALLY need to watch the 'tube I provided a link for. 😉
Certainly, I would not use such a "catch-all", generic attempt at a panacea solution for maintaining even simple rowstore indeses.
Yeah... I know... I don't mean to sound harsh or livid about any of this but I AM remembering the absolute hell I went through on 18 Jan 2016 because of such things and I'm trying to spare you and other from the misery that hasn't yet bitten you so hard but is biting you in the form of excessive log file usage, massive unnecessary page splits, and the blocking every "morning after" that occurs. I actually went for almost 4 years after that without doing any index maintenance and, except for some wasted space, caused no ill effects performance wise. In fact, performance slowly got better over the first 3 months and stayed there. I cringe even when I rebuild an index to recover disk space on a "permanently fragmenting" index.
I also tell you that, if you're going to do index maintenance, then why are you waiting for it to "quiet down" after the fragmentation has created free-space in the index and then maintain it when improper maintenance, such as what you're doing ,will just increase page splits, blocking, fragmentation, disk usage, log usage, etc.
Now, go watch the 'tube and get a copy of sp_IndexDNA™, and start seeing what's actually happening to your indexes. You'll be as shocked and amazed as I was. :0
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2022 at 10:13 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply