August 6, 2018 at 4:05 am
Dear SQL Server Central forum members,
I have recently stumbled across a custom index fragmentation reporting tool and did a little write-up in my blog. I've made some statements about what kinds of index fragmentation are dangerous and can potentially have negative impact on the performance, and I would like to get them peer-reviewed.
Here is what I wrote:
Simply speaking, index data can be:
placed within each (or the only) page in an ordered way, without much blank space
spread across hundreds of pages with each page only containing, say, 30% of data and 70% of free space. In this scenario SQL server will still have to read through all the unused space within the page, causing unnecessary use of CPU cycles and memory
and
Based on 0 execution time it appears that these indexes are so insignificantly small that there probably is no need to mark them as 'red' within the index monitoring script. The fragmentation that is likely to have any sort of performance impact is only going to occur in much larger indexes.
And here is the whole article, for context: http://www.mediaisourlife.com/blog/studying-custom-index-fragmentation-reports/
Are these statements valid? I don't want to mislead my colleagues and would appreciate your feedback.
August 6, 2018 at 6:21 am
I haven't read the whole article. The core of both statements is correct. Fragmentation can lead to extra reads on disk. The issue is, defragmentation leads to refragmentation. To really evaluate if defragmentation on a rowstore index is worth it, you need to also get a count of page splits and the waits entailed there to compare it to general I/O waits caused by extra reads. That will let you know if fragmentation is hurting you.
Measuring just execution time to determine index size doesn't take into account blocking and waits. Not sure of the context of that statement, but it might be a poor measure, depending.
"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
August 6, 2018 at 6:39 am
Grant Fritchey - Monday, August 6, 2018 6:21 AMThe issue is, defragmentation leads to refragmentation. To really evaluate if defragmentation on a rowstore index is worth it, you need to also get a count of page splits and the waits entailed there to compare it to general I/O waits caused by extra reads. That will let you know if fragmentation is hurting you.
Absolutely spot on, Grant!
With the months long study/testing I've been doing on page splits and fragmentation, I've found that defragmentation can be like a bad drug habit on certain "kinds" of indexes (not to be mistaken for "types" of indexes like CI/NCI). The more you do it, the more you need to do it. The currently accepted "standards" for defragmentation for those types of indexes actually guarantee rampant page splits and the need for defragmentation.
I do have a fix for those "kinds" of indexes, though. The hard part is identifying that you actually have one of those "kinds" of indexes. There are other "kinds" of indexes that I've identified or refconfirmed and I'm working on the methods to auto-magically identify those and what to do about them.
The other part is that most people only test for logical fragmentation and ignore the physical fragmentation.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply