June 26, 2019 at 3:46 am
Are there benefits to be had from defragging clustered indexes that reside on SSDs? Or is it true that since SSD's data is addressed via electronic switching on the SSD address bus there is no performance penalty due to fragmentation?
June 26, 2019 at 12:27 pm
Oh boy, this is a fun topic.
So, SSDs. VERY fast. However, I/O is I/O. No matter how fast your disks, more I/O means slower performance. Comparing reading 8 pages on an SSD to spinning rust, the SSD wins by huge amounts. Toss in things like Pure Storage where you have arrays of SSDs and that gets better by leaps and bounds. However, let's compare SSD to SSD. Reading 8 pages is faster than reading 16 is faster than reading 32 is faster than reading 64. I/O is I/O.
I say all this because I want to be really clear. SSDs are not magic. They are great, but not magic. You can put too much strain on your I/O on an SSD, same as with spinning rust. It just takes more work on your part.
So, that brings us to a completely separate discussion. Is fragmentation a performance enhancer AND, to what degree is defragging an index helping performance when compared to the costs of defragging that index as well as dealing with the subsequent refragmentation, page splits, and the associated costs of all that. According to the work I've done and the detailed analysis that Jeff Moden has done (and I trust Jeff more than myself), defragging indexes is not worth the performance hit associated with that process and the subsequent pain of page splits. You're better off finding a good fill factor and leaving your indexes alone in most cases. The only exception to this is columnstore indexes, which have different benefits from defragging (movement of data out of the delta store). Primarily fragmentation only seriously hurts scans anyway. If you're mostly seeks on your queries, fragmentation is largely pain free.
So, my opinion, don't defrag. Not because of the SSD though. If fragmentation hurts your spinning rust (and you'd need to put a lot of math into proving it does), then it's going to hurt your SSDs, just a lot less.
"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
June 27, 2019 at 11:59 am
look after your stats is probably the best effort
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply