July 24, 2021 at 8:29 pm
With reference to the following MS article...
That article was where the old "Best Method" recommendation of Reorg between 5 and 30% and Rebuild at > 30% fragmentation used to live. You can verify that by doing an "edit" on the page and looking for where they made the change on lines 73 thru 74 of the 20 April 2021 commit in the history of the page.
What they now recommend is (to make a much longer story shorter), is doing a "Query Store A/B Comparison" ( https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-store-usage-scenarios?view=sql-server-ver15#ab-testing ) of before and after index maintenance to see if the index maintenance actually did any good.
I can see doing that manually for like maybe 4 of the largest indexes. What do you do when you have hundreds or even thousand of indexes? I see some interesting DMVs associated with Query Store and I might simply be missing it but I see no reasonable examples of how to quantitatively evaluate all indexes for whether or not the last index maintenance actually helped or hurt any given index as the new documentation suggests we do manually.
Note that I DO see a lot of code examples but I'm not actually seeing anything about how to automatically capture information about whether or not rebuilding an index was a good thing or not, And, certainly, I see nothing about how to determine if a Fill Factor is doing any good for preventing fragmentation.
Am I simply missing something?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 25, 2021 at 2:16 pm
Maybe I am reading a different article - but what you linked to is actually worse. This article *recommends* REORGANIZE as the primary method to be used and then links to code that uses the same logic that they removed from the article.
I don't see that this article links to use Query Store A/B to see whether or not an index reorg/rebuild improves performance. I may have missed it - and I guess you could set it up to do that - but I would not use query store in that manner.
I guess this means we are back to looking at each table/index and determining the best strategy for index maintenance for each one - based on the usage patterns and the structure of that index. This is going to require DBA's to review their systems and determine how they are being used - modify the clustered index - set an appropriate fill factor - and schedule the index maintenance.
For some, you can extend the schedule possibly for months or even years - others you will need to rebuild daily.
Or...you can continue using the code they link to in the article or Ola's utilities or Maintenance Plans, which all utilize the checks for fragmentation/page_density to determine when to rebuild vs reorganize.
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
July 25, 2021 at 3:58 pm
Thanks for the response, Jeffrey. If you go to the first article I provided a link for and then search for "A/B", that 3 character link will take you to the second link I cited, which is the Query Store A/B testing I spoke of.
I also see what you're talking about with the recommendation that REORGANIZE is the preferred thing... which is totally incorrect in all but a couple of special cases (and, yeah, I prove that in my presentations on the subject).
You DID come to the same conclusion as I...
I guess this means we are back to looking at each table/index and determining the best strategy for index maintenance for each one - based on the usage patterns and the structure of that index. This is going to require DBA's to review their systems and determine how they are being used - modify the clustered index - set an appropriate fill factor - and schedule the index maintenance.
I actually have code that does most of that. I'm still tweaking it and so haven't released it, yet.
As for using Ola's Code... his code is nothing short of remarkable but, if people are using it to run the old 5/30 supposed "Best Practices" that were never meant to be a "Best Practice" and are actually a worst practice, then they have some serious problems. Again, with only a couple of exceptions, using REORGANIZE in regular index maintenance is a form of "Death by Defragmentation" and based on the amount of Log File that REORGANIZE actually does use compared to Rebuilds, I'm not likely to use it except for one of those exceptions because nothing else will compact LOBs short dropping the table and rebuilding it from the data.
So, the good part is they've removed the 5/30 junk from the article. That bad part is that they've provided nothing reasonable for the bulk handling of index maintenance and they've perpetuated the myth of REORGANIZE somehow being friendly when it's actually one of the worst things you can do to most indexes.
That means the 5/30 thing will still prevail because newbies will ask for what to do and, since the whole freakin' world has been making the same mistake for more than 2 decades, they'll continue to recommend that same poor practice.
Looks like I need to redouble my efforts on getting a Stairway on the subject out.
Again... thanks for the reply, Jeffrey.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 26, 2021 at 2:04 am
Don't forget: Microsoft is a Cloud Service provider. Quite a big one. With no plans of retreating.
Increased load on in-house servers makes the argument for a company to move to the Cloud only stronger.
Increased load on Azure servers only increases their revenue, as they charge per usage units.
It's just their business, nothing personal.
I can totally understand that if anyone on MS payroll one day publishes an article how to do things more effectively, in order to decrease the load on the servers and remove unnecessary overheads - he or she must be fired on the spot, because such suggestions would negatively affect the company's bottom line.
So, if you're after some increased efficiency and reduced overall load on SQL servers the last people you should be listening to are Microsoft employees.
_____________
Code for TallyGenerator
July 26, 2021 at 12:45 pm
So, if you're after some increased efficiency and reduced overall load on SQL servers the last people you should be listening to are Microsoft employees.
That's a pretty tough accusation. And, being friends with Bob Ward, I'm going to say this, it's not at all accurate. Here's just one example, and yes, I can find hundreds more, of Microsoft employees, Bob & Hanna, showing how to improve performance in Azure. Bob doesn't give out bad information. He just doesn't. Bob, Hanna, Joe Sack, Buck Woody, Pam Lahoud, James Roland-Jones, Jes Borland, the list goes on and on, Microsoft people who teach the best ways to maximize performance on their systems. Heck, a lot of what many of us teach was passed on by these people. Are there corporate hacks out there? Without a doubt. However, I would not paint with a broad brush like that for all the Microsoft employees. It's just wrong.
"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
July 26, 2021 at 3:36 pm
Thanks for the response, Jeffrey. If you go to the first article I provided a link for and then search for "A/B", that 3 character link will take you to the second link I cited, which is the Query Store A/B testing I spoke of.
I see that now - thanks. This just reinforces the idea that you need to inspect each table/index individually. No way you could setup query store to monitor all index maintenance - across the board.
As for using Ola's Code... his code is nothing short of remarkable but, if people are using it to run the old 5/30 supposed "Best Practices" that were never meant to be a "Best Practice" and are actually a worst practice, then they have some serious problems. Again, with only a couple of exceptions, using REORGANIZE in regular index maintenance is a form of "Death by Defragmentation" and based on the amount of Log File that REORGANIZE actually does use compared to Rebuilds, I'm not likely to use it except for one of those exceptions because nothing else will compact LOBs short dropping the table and rebuilding it from the data.
Ola's stuff works on the 5/30 methodology for index maintenance. The maintenance plan rebuild task uses the 5/30 methodology - the code the article links to (Adaptive Index Defrag) uses the same 5/30 logic. Removing the statement from the article doesn't help much if the utilities they are recommending use that same methodology.
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
July 26, 2021 at 3:50 pm
Ola's stuff works on the 5/30 methodology for index maintenance. The maintenance plan rebuild task uses the 5/30 methodology - the code the article links to (Adaptive Index Defrag) uses the same 5/30 logic. Removing the statement from the article doesn't help much if the utilities they are recommending use that same methodology.
Those are the defaults in Ola's scripts. You can change them pretty easily.
"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
July 26, 2021 at 5:49 pm
Jeffrey Williams wrote:Ola's stuff works on the 5/30 methodology for index maintenance. The maintenance plan rebuild task uses the 5/30 methodology - the code the article links to (Adaptive Index Defrag) uses the same 5/30 logic. Removing the statement from the article doesn't help much if the utilities they are recommending use that same methodology.
Those are the defaults in Ola's scripts. You can change them pretty easily.
Changing them just means using different percentages to determine when to perform reorganize vs rebuild. The problem isn't the percentage - the problem is when to choose a reorganize. There are only a few times I see where reorganize should be chosen:
LOB compaction - if you have identified an issue where you need to compact. Any other time - in my opinion - is just a stop-gap measure to get you to the next window for a full rebuild. Lots of deletes leaving empty space on the page can be compacted - or lots of page splits and out of order pages where you have noticeable performance issues and cannot rebuild online.
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
July 26, 2021 at 6:47 pm
Changing them just means using different percentages to determine when to perform reorganize vs rebuild. The problem isn't the percentage - the problem is when to choose a reorganize. There are only a few times I see where reorganize should be chosen:
LOB compaction - if you have identified an issue where you need to compact. Any other time - in my opinion - is just a stop-gap measure to get you to the next window for a full rebuild. Lots of deletes leaving empty space on the page can be compacted - or lots of page splits and out of order pages where you have noticeable performance issues and cannot rebuild online.
Oh, no arguments. I'm 100% with Jeff on this. Reorg (except for columnstore) is basically useless.
"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
July 26, 2021 at 7:24 pm
Don't forget: Microsoft is a Cloud Service provider. Quite a big one. With no plans of retreating.
Increased load on in-house servers makes the argument for a company to move to the Cloud only stronger.
Increased load on Azure servers only increases their revenue, as they charge per usage units.
It's just their business, nothing personal.
I can totally understand that if anyone on MS payroll one day publishes an article how to do things more effectively, in order to decrease the load on the servers and remove unnecessary overheads - he or she must be fired on the spot, because such suggestions would negatively affect the company's bottom line.
So, if you're after some increased efficiency and reduced overall load on SQL servers the last people you should be listening to are Microsoft employees.
Heh... way too funny mostly because, as much as I'd like to not believe that's true, it sure as hell seems to be true. Of course, I might also be because they don't actually have a clue. I've recently been looking for what MS recommends for index maintenance for SharePoint, which appears to be one of those products where just about everything has a Random GUID keyed index of one form or another. They've also identified that fragmentation is an issue but they still recommend the use of REORGANIZE. Between that and recommending a blanket lowering of Fill Factor for all indexes and still rebuilding all indexes regardless of fragmentation, I'm thinking they're actually pretty clueless.
Heh... of course, we all were at one point in time or another especially about things like REORGANIZE and what the real issues with Random GUIDs actually were (name, US).
--Jeff Moden
Change is inevitable... Change for the better is not.
July 26, 2021 at 7:33 pm
On the subject of LOB compaction and deletes and page splits...
It may take 10 runs for REORGANIZE to actually get it to work right (yep... tried it and measured it).
On deletes, don't do it especially if a Fill Factor of any kind comes into play. Reorganize can and frequently does nearly double the size of the logfile being used when compared even to an ONLINE rebuild.
On page splits, I'll say it again... REORGANIZE does NOT work the way you probably think it does. Not even close! The only possibility of it working correctly are if you have an ever-increasing index and you usually do expansive updates to the data you just insert. Using a Fill Factor will NOT work in such a situation and will only server to waste a shedload of memory and disk space.
If you DO use REORGANIZE for things like LOB compaction, make sure you follow that with a REBUILD because REORGANIZE seriously puts the screws to any index where a FILL FACTOR other than 0 or 100 is important.
I have the proof of all this... I just don't have the hours to teach it all on this thread. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
July 27, 2021 at 5:29 am
Sergiy wrote:So, if you're after some increased efficiency and reduced overall load on SQL servers the last people you should be listening to are Microsoft employees.
That's a pretty tough accusation. And, being friends with Bob Ward, I'm going to say this, it's not at all accurate. Here's just one example, and yes, I can find hundreds more, of Microsoft employees, Bob & Hanna, showing how to improve performance in Azure. Bob doesn't give out bad information. He just doesn't. Bob, Hanna, Joe Sack, Buck Woody, Pam Lahoud, James Roland-Jones, Jes Borland, the list goes on and on, Microsoft people who teach the best ways to maximize performance on their systems. Heck, a lot of what many of us teach was passed on by these people. Are there corporate hacks out there? Without a doubt. However, I would not paint with a broad brush like that for all the Microsoft employees. It's just wrong.
yeah-yeah-yeah.
So many professionals, all are good and giving only good advices.
blah-blah-blah.
Lets do a little fact check.
Is that article still online?
Is it defined as “recommendations from Microsoft”?
Are those recommendations right or wrong?
Here it is. That’s only what matters.
Your professional friends from Microsoft don’t affect the quality of the company’s recommendations, so they don’t matter.
My statement remains the same: “you cannot trust recommendations from Microsoft on effective SQL Server solutions”.
Cannot see anything wrong with that.
P.S. With so many so brilliant database professionals in Microsoft - how come that their own databases, e.g. AdventureWorks, msdb, are so horribly designed? Newbies learn from those examples, considering it “the best practices in design from the vendor”.
I’d say it’s a crime against the product. Do you again think I’m wrong on it?
_____________
Code for TallyGenerator
July 27, 2021 at 11:11 am
Do you again think I’m wrong on it?
Yes.
"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
July 27, 2021 at 8:39 pm
Unfortunately, that actually proves that they don't know. Bob Ward is still talking about the use of reorganize for index maintenance. Is it really possible that even he doesn't know the train wreck known as Reorganize? 😀 And is there really a good reason why they don't support minimal logging on managed instances? DBAs do it every day and they know the ramifications and so they work around that in special databases... you know... sandboxes that simply aren't backed up and similar "tricks"? Yes, there's a trade off but why did they take only the easy way out on managed instances?
Heh... and consider some of the open items on the feedback site and look at what they did for String_Split and Pivot and Format and TempDB overusage with inserting into IDENTITY columns, and making is so that you cannot temporarily disable the equivalent of TF1117 in TempDB, and making it so that LOBs default to in-row, and making it so that out of row pointers are "expansive" and, and, and...
Yes... they do a lot of really cool stuff. Yes, they also do a whole lot of some really shoddy and uniformed design and work and ignore other/previous problems such as how both index REBUILD and REORGANIZE work and don't work. And then there's the "hidden price to pay" on some really useful stuff because they really didn't implement things quite right. One of my favorite areas there is Partitioned Tables.
The changed article about index recommendations is a good proof of it all... they left a mistake for more than 2 decades, they've done nothing to fix the root problems (they really need to fix rebuild, reorganize, and provide a better set of tools than the gross averages produced by sys.dm_db_index_physical_stats, and their new recommendation/suggestion is just as bad as the original except that it's even more complicated and the one new tool for page examination is seriously broken when it comes to previous/next page and the column for page free space doesn't actually work for what you need it to work for the most.
I'm sure, though, that MS isn't the only software provider that has such issues. They all have a major suck factor one way or another. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
July 28, 2021 at 9:38 am
OK then.
My recent experience with "flat-earthers" taught me that it's almost useless trying to prove anything to true believers. For them facts matter only when they fit their concept.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 45 total)
You must be logged in to reply to this topic. Login to reply