December 28, 2022 at 1:05 am
BWAAA-HAAA-HAAA. Posting on this thread must cause disturbances in the "force". I looked again right after I posted and I finally found one.
https://gertjans.home.xs4all.nl/sql/fragmentation-hurts-performance.html
--Jeff Moden
Change is inevitable... Change for the better is not.
December 28, 2022 at 1:34 am
And, then again, NOT! It turns out that he was testing fragmentation on spinning rust. Not logical fragmentation of a index. [headdesk] 🙁
--Jeff Moden
Change is inevitable... Change for the better is not.
December 28, 2022 at 2:03 am
BWAAA-HAAA-HAAA. Posting on this thread must cause disturbances in the "force". I looked again right after I posted and I finally found one.
https://gertjans.home.xs4all.nl/sql/fragmentation-hurts-performance.html
He also was only testing a table scan.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 28, 2022 at 5:34 am
Jeff Moden wrote:BWAAA-HAAA-HAAA. Posting on this thread must cause disturbances in the "force". I looked again right after I posted and I finally found one.
https://gertjans.home.xs4all.nl/sql/fragmentation-hurts-performance.html
He also was only testing a table scan.
Yeah... and no actual data access. He was using the SELECT as a Pseudo-Cursor to dump GETDATE() for a variable. And, it was back in the days when spinning rust was pretty much all that was available.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 28, 2022 at 5:20 pm
Michael L John wrote:Jeff Moden wrote:BWAAA-HAAA-HAAA. Posting on this thread must cause disturbances in the "force". I looked again right after I posted and I finally found one.
https://gertjans.home.xs4all.nl/sql/fragmentation-hurts-performance.html
He also was only testing a table scan.
Yeah... and no actually data access. He was using the SELECT as a Pseudo-Cursor to dump GETDATE() for a variable. And, it was back in the days when spinning rust was pretty much all that was available.
He did test against an SSD - and it showed the same level of issues vs spinning rust for the tests that he performed. Not sure how you can test the performance impact of many processes loading extents that are not needed, because most of the data in the extent cannot be used to satisfy the query.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 28, 2022 at 6:06 pm
Jeff -
Wouldn’t a valid test be to clone disks with the index and file fragmentations - both internal and external fragmentation - and then work backwards? There still are variables, but this might give a good benchmark.
On a hard disk, there is a short stroking technique to optimize access. Likewise there is a trimming technique for ssd that also could be explored. Maybe go back to the old days of Doom and try a RamDisk……
Even what I found recently from Microsoft seemed to recommend defragmentation, although kind of punted on any defined gains. Likely depending on hardware and data base sizes you hit a wall. And there are a lot of variables that can factor in, both internal to sql server and external.
December 28, 2022 at 8:24 pm
Thanks for the response, Greg. I appreciate it.
I wasn't asking if anyone knew of an article that proves the "benefits" of doing Index Maintenance to figure out a way to do it myself. I've got that very well down pat.
I won't bore you with my long winded rant on the subject but, after having read perhaps a 100 "expert" articles on the subject and having viewed dozen's of 'tubes on the subject, I'm truly amazed by how so many people (including a younger me) have been snowed by hear-say for more than 2 decades on the subject and continue to be snowed. Even Microsoft falls into that category with their documentation on index maintenance and the related, poorly designed tools. There have also been a few isolated individuals that rail against index maintenance but, they too, have failed to produce any demonstrative proof.
Like I said, I've done a shedload of testing (for more than half a decade now after I got bit by "Best Practice Index Maintenance" big time on 18 Jan 2016) so I'm not looking for test methods. My question was to find if anyone has found such demonstrative proof either way and, save for the single article that I provided a link to (which actually proved the opposite of what it seems they intended), I was beginning to wonder if I simply needed a lesson on how to search for such a thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 28, 2022 at 8:54 pm
When I stopped re-indexing 6 years ago at my old company, I took a "leap of faith". My logic was that things could not get worse.
I did perform performance testing of some procs and queries side by side. Some were the same, some were better, and some were worse. The overall effect was a wash.
The benefits were from the following items:
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 28, 2022 at 9:25 pm
Thanks for the "faith" and thank you for the incredible feedback.. Our buddy, Ed, did the same thing with similar results after I told him what I'd found, especially concerning the "morning after" syndrome of Index Maintenance.
Testimonials are always a good thing. The trouble with that is it won't convince the masses that have been "convinced" by years of MS and "experts" saying otherwise and that's why I was looking for MS or one of those "experts" that had demonstrable proof... and they don't... either way.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 28, 2022 at 9:51 pm
Jeff -
My example was more of a way in which I would test and prove either way and measure results. But with all the variables results would be an indication. I have no doubt you have done a lot of testing, and probably had some mixed results.
Searching for something specific can be like finding a unicorn. Here is my unicorn sighting.
I recently did a search through the videos to figure out why my wife’s key would occasionally not go all the way to the off position in her Impala. Almost all the experts detailed a complete replacements of the shifter assembly. Then I found the ‘Expert’. Remove a couple pieces to get access, then push a pin over just a bit so it once again triggered the micro switch. Applied to her year and newer. Cost $0, Time 15 minutes. Everyone else pointed to a $600 + solution.
I’ve started playing a fair amount of Pickleball in retirement. Just keeping track of the score and who’s server can be demanding at times. And I find most times if someone can get consistent, they can be competitive even if they aren’t terribly athletic. At the Y I play at, we get as young as teenagers, up to several in their 80’s. I’ve seen young so called Advanced players get humbled at times by a pair of 65+ players. A game I would never bet a dime on without seeing some play.
December 28, 2022 at 10:28 pm
Thanks, Greg. I have to admit I took it wrong way a bit and so was just making sure. And I love both of your examples.
As a bit of a sidebar, I've never played Pickle-Ball before. Interesting history there as are the rules, especially the "wrist high" and "double bounce" serving rules. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
December 29, 2022 at 3:44 am
Jeff -
I always describe Pickleball as kind of like a mix of tennis and ping pong. My bonus is that my wife plays too. She’s not the most athletic, but does fairly well. And it is an enjoyable way to get in an hour or two of exercise - nobody cares a ton about how good you are, but more about seeing you make some progress.
Absolutely no offense taken to the tone of your initial reply. I always have welcomed your back and forth discussions, something rare lately. Fragmentation and it’s effects on performance are hard to put into black and white numbers due to lots of variables. Sometimes it helps, sometimes not - something that drives some crazy until they know the root cause.
December 29, 2022 at 1:30 pm
There's no question in my mind that I've lost my mind 😀 and I'm not sure if I've asked it here before or not but I'll ask it again.
Other than the following article, does anyone have a link to a decent article with some demonstrable proof in the form of code that either kind (logical or physical/page density) actually makes a substantial difference in performance? Ironically, Jonathon proved there may be an advantage to fragmented indexes.
https://sqlperformance.com/2017/12/sql-indexes/impact-fragmentation-plans
Yep... I get that the Physical/PageDensity type of fragmentation has an effect on memory usage and can lower the amount of time some things spend in memory but I'm looking for actual run time differences between identical data in tables where 1 is 0% logically fragmented and the other is 99% fragmented even for cold-cache loads.
In case you can't tell, I've been watching 'tubes and reading articles on the subject of fragmentation and they all say that "fragmentation affects performance" but I've not found a one, other than the article I cited above, that proves that de-fragmentation substantially improves run times.
Another bone I'm getting ready to pick is all these people that say "Reorganize takes less time than a Rebuild", "Reorganize takes fewer resources than a Rebuild", "Reorganize is easier on the log file than Rebuild (I've proven that's totally incorrect more than once)" but NONE of them have proven it.
Any links that actually prove how de-fragmentation makes a substantial difference in performance (save that of total "Index Inversion" due to Shrink File) or proves any of the other claims would be greatly appreciated because I'm not finding any.
I show an example in the query tuning book where defragmentation improves performance because of a scan, fewer pages read. I also explain all the reasons why, this probably doesn't matter most of the time, AND, probably doesn't help, most of the time. However, there is one example.
As to reorganize, Brad MacGeehee had an article back in 2006 or 2007 that showed how useless reorganizing indexes is. You spend, just shy of almost exactly the same amount of resources, and get crap out the other end. He detailed it all in the article. Google-fu may help.
"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 29, 2022 at 1:30 pm
There's no question in my mind that I've lost my mind 😀 and I'm not sure if I've asked it here before or not but I'll ask it again.
Other than the following article, does anyone have a link to a decent article with some demonstrable proof in the form of code that either kind (logical or physical/page density) actually makes a substantial difference in performance? Ironically, Jonathon proved there may be an advantage to fragmented indexes.
https://sqlperformance.com/2017/12/sql-indexes/impact-fragmentation-plans
Yep... I get that the Physical/PageDensity type of fragmentation has an effect on memory usage and can lower the amount of time some things spend in memory but I'm looking for actual run time differences between identical data in tables where 1 is 0% logically fragmented and the other is 99% fragmented even for cold-cache loads.
In case you can't tell, I've been watching 'tubes and reading articles on the subject of fragmentation and they all say that "fragmentation affects performance" but I've not found a one, other than the article I cited above, that proves that de-fragmentation substantially improves run times.
Another bone I'm getting ready to pick is all these people that say "Reorganize takes less time than a Rebuild", "Reorganize takes fewer resources than a Rebuild", "Reorganize is easier on the log file than Rebuild (I've proven that's totally incorrect more than once)" but NONE of them have proven it.
Any links that actually prove how de-fragmentation makes a substantial difference in performance (save that of total "Index Inversion" due to Shrink File) or proves any of the other claims would be greatly appreciated because I'm not finding any.
I show an example in the query tuning book where defragmentation improves performance because of a scan, fewer pages read. I also explain all the reasons why, this probably doesn't matter most of the time, AND, probably doesn't help, most of the time. However, there is one example.
As to reorganize, Brad MacGeehee had an article back in 2006 or 2007 that showed how useless reorganizing indexes is. You spend, just shy of almost exactly the same amount of resources, and get crap out the other end. He detailed it all in the article. Google-fu may help.
"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 29, 2022 at 1:31 pm
There's no question in my mind that I've lost my mind 😀 and I'm not sure if I've asked it here before or not but I'll ask it again.
Other than the following article, does anyone have a link to a decent article with some demonstrable proof in the form of code that either kind (logical or physical/page density) actually makes a substantial difference in performance? Ironically, Jonathon proved there may be an advantage to fragmented indexes.
https://sqlperformance.com/2017/12/sql-indexes/impact-fragmentation-plans
Yep... I get that the Physical/PageDensity type of fragmentation has an effect on memory usage and can lower the amount of time some things spend in memory but I'm looking for actual run time differences between identical data in tables where 1 is 0% logically fragmented and the other is 99% fragmented even for cold-cache loads.
In case you can't tell, I've been watching 'tubes and reading articles on the subject of fragmentation and they all say that "fragmentation affects performance" but I've not found a one, other than the article I cited above, that proves that de-fragmentation substantially improves run times.
Another bone I'm getting ready to pick is all these people that say "Reorganize takes less time than a Rebuild", "Reorganize takes fewer resources than a Rebuild", "Reorganize is easier on the log file than Rebuild (I've proven that's totally incorrect more than once)" but NONE of them have proven it.
Any links that actually prove how de-fragmentation makes a substantial difference in performance (save that of total "Index Inversion" due to Shrink File) or proves any of the other claims would be greatly appreciated because I'm not finding any.
I show an example in the query tuning book where defragmentation improves performance because of a scan, fewer pages read. I also explain all the reasons why, this probably doesn't matter most of the time, AND, probably doesn't help, most of the time. However, there is one example.
As to reorganize, Brad MacGeehee had an article back in 2006 or 2007 that showed how useless reorganizing indexes is. You spend, just shy of almost exactly the same amount of resources, and get crap out the other end. He detailed it all in the article. Google-fu may help.
"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
Viewing 15 posts - 66,091 through 66,105 (of 66,738 total)
You must be logged in to reply to this topic. Login to reply