October 20, 2017 at 2:19 am
Good Morning Folks!
I am working on an optimisation project that is focusing heavily on indexing within the database. At the moment I am exploring whether or not indexes should be rewritten or removed.
What I would like to achieve is the following:
1. The index should still be maintained and statistics updated as required during the evaluation period so that a rebuild isn't required if I need to fall back.
2. The index should be invisible to the optimiser but without the use of hints to push the optimiser in a particular direction. The optimiser should still have the full range of indexes to choose from excluding this particular one.
3. The index should not be disabled for the evaluation period to prevent rebuilds in the event of a possible failback should the new index not meet expectations.
For the Oracle people out there:
I would like to use a feature similar to ALTER INDEX ix_test INVISIBLE to achieve this.
Any ideas?
Regards,
Kev
October 20, 2017 at 7:06 am
I could be wrong but, without disabling or dropping the index, making indexes invisible to the optimizer in SQL Server isn't possible.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 20, 2017 at 9:03 am
Jeff Moden - Friday, October 20, 2017 7:06 AMI could be wrong but, without disabling or dropping the index, making indexes invisible to the optimizer in SQL Server isn't possible.
Hi Jeff, thanks for the comment.
I think it would be a fantastic feature for SQL Server in a future version. In Oracle it helped enormously because I could easily revert back to a previous index if the index didn't perform in production the same way it did in testing (from 11g onwards anyway....)
Regards,
Kev
October 20, 2017 at 9:11 am
kevaburg - Friday, October 20, 2017 9:03 AMJeff Moden - Friday, October 20, 2017 7:06 AMI could be wrong but, without disabling or dropping the index, making indexes invisible to the optimizer in SQL Server isn't possible.Hi Jeff, thanks for the comment.
I think it would be a fantastic feature for SQL Server in a future version. In Oracle it helped enormously because I could easily revert back to a previous index if the index didn't perform in production the same way it did in testing (from 11g onwards anyway....)
Regards,
Kev
I absolutely agree. It would be especially useful when working with/trying to optimize queries on very large tables, which I have to do a whole lot. It would also be cool if they had a DMV for which stats are actually being used by count and by last date used like they do with indexes.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 20, 2017 at 10:13 am
See if this helps...
October 20, 2017 at 10:40 am
Jason A. Long - Friday, October 20, 2017 10:13 AMSee if this helps...Disabling vs. Dropping Indexes
Excellent link, Jason. The problem is that even if you just disable and index, it must still go through the throws of a rebuild when you re-enable it. You can't simply make an index "invisible to the optimizer" without disabling it. Brent also failed to mention that Unique NCIs are also prime candidates as a target for Foreign Keys. If you disable such an NCI, you also disable the FK and it will need to be rebuilt after you rebuild the index to re-enable it.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 20, 2017 at 11:01 am
Jeff Moden - Friday, October 20, 2017 10:40 AMJason A. Long - Friday, October 20, 2017 10:13 AMSee if this helps...Disabling vs. Dropping Indexes
Excellent link, Jason. The problem is that even if you just disable and index, it must still go through the throws of a rebuild when you re-enable it. You can't simply make an index "invisible to the optimizer" without disabling it. Brent also failed to mention that Unique NCIs are also prime candidates as a target for Foreign Keys. If you disable such an NCI, you also disable the FK and it will need to be rebuilt after you rebuild the index to re-enable it.
Exactly... The only "advantage" disabling dives you, is the fact that the original index definition is maintained.
For someone like me, who focuses too much in some ares and not enough in other, that's not nothing...
It makes it easy to keep up with the original definition and... I have had more than one , "oh crap... which one is the original?" moment...
October 20, 2017 at 1:39 pm
You might achieve what you're wanting to do using hints. FORCESCAN forces scan on index instead of seek, INDEX(1) forces use of clustered index, and INDEX(0) forces a table scan.
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 20, 2017 at 3:50 pm
Eric M Russell - Friday, October 20, 2017 1:39 PMYou might achieve what you're wanting to do using hints. FORCESCAN forces scan on index instead of seek, INDEX(1) forces use of clustered index, and INDEX(0) forces a table scan.
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query
Uhh! Great call. I forgot about the index hints. If you know the ID of the index, you can also use that in the INDEX() hint.
The only trouble with that is it does require a code change but that's also the nature of tweeking for performance so it shouldn't be a real big problem, especially if you working on individual queries in a stored procedure (or whatever).
--Jeff Moden
Change is inevitable... Change for the better is not.
October 21, 2017 at 12:42 am
Jason A. Long - Friday, October 20, 2017 11:01 AMJeff Moden - Friday, October 20, 2017 10:40 AMJason A. Long - Friday, October 20, 2017 10:13 AMSee if this helps...Disabling vs. Dropping Indexes
Excellent link, Jason. The problem is that even if you just disable and index, it must still go through the throws of a rebuild when you re-enable it. You can't simply make an index "invisible to the optimizer" without disabling it. Brent also failed to mention that Unique NCIs are also prime candidates as a target for Foreign Keys. If you disable such an NCI, you also disable the FK and it will need to be rebuilt after you rebuild the index to re-enable it.
Exactly... The only "advantage" disabling dives you, is the fact that the original index definition is maintained.
For someone like me, who focuses too much in some ares and not enough in other, that's not nothing...
It makes it easy to keep up with the original definition and... I have had more than one , "oh crap... which one is the original?" moment...
Retaining the definition isn't the problem here and I don't consider it an advantage or disadvantage because we have mechanisms in place for version controlling code such as this. I could drop the index but then I loose the statistics for it. On a very large table this means that a rollback in production becomes very expensive because the whole index needs to be rebuilt.
If I disable the index however, the statistics are not dropped but then also not maintained. The critical evaluation period in production is one calendar month before it is considered safe to drop the index and on an index that is disabled means rebuilding the whole structure.
October 21, 2017 at 12:56 am
Jeff Moden - Friday, October 20, 2017 3:50 PMEric M Russell - Friday, October 20, 2017 1:39 PMYou might achieve what you're wanting to do using hints. FORCESCAN forces scan on index instead of seek, INDEX(1) forces use of clustered index, and INDEX(0) forces a table scan.
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-queryUhh! Great call. I forgot about the index hints. If you know the ID of the index, you can also use that in the INDEX() hint.
The only trouble with that is it does require a code change but that's also the nature of tweeking for performance so it shouldn't be a real big problem, especially if you working on individual queries in a stored procedure (or whatever).
The background is that there is a certain amount of open-heart surgery going on at the moment and although an index may perform well in testing, we make the assumption that it won't necessarily be the case in production. If a new index doesn't perform well in production (or at least doesn't meet the measured expectations) we can drop it, make the original index visible again and carry on as normal with no downtime. For me the is a SQL Server Holy Grail that performs perfectly in Oracle from 11g and has helped me tremendously in similar situations.
Another reason I want to avoid hints is this: I attended a lecture from a MCM here in Germany and he made this statement:
"If you feel the only way to solve a problem is to use a hint you are making a conscious ascertation that you understand the optimiser better than the 200-300 professional developers in Redmond that developed it".
It is that statement that makes me act with a great deal of caution when dealing with hints. In some cases they have actually been banned from development in our house unless there truly is no other solution and all other options have been exhausted. The main point not to forget is that at the point I need an invisible index I am on a production system. Do I really want to take those chances at that stage of the process?
October 21, 2017 at 3:09 pm
kevaburg - Saturday, October 21, 2017 12:56 AMAnother reason I want to avoid hints is this: I attended a lecture from a MCM here in Germany and he made this statement:
"If you feel the only way to solve a problem is to use a hint you are making a conscious ascertation that you understand the optimiser better than the 200-300 professional developers in Redmond that developed it".
Heh... You mean the same folks that developed the crippled PIVOT even though MS has a great model of a non-crippled version in ACCESS... the ones that crippled performance with FORMAT... the ones that made recursive CTEs run slower and more resource intensive than a While Loop... the ones that created a splitter function with no guarantee of sort order and no return of element position... the ones that cause online rebuilds to sometimes corrupt the clustered index in 2012... the ones that refuse to have a built in Tally Table function because they don't understand how it would be used... the ones that keep deprecating functionality and frequently replacing it with something that doesn't work as well... the ones that caused SSIS installations to go corrupt after an SP release... the ones that still haven't figured out how to do a Peter Norton style shrink of a database even though the technology has existed for more than 3 decades... the ones that still haven't created an easy to use true CSV file importer for T-SQL.. the ones that have to issue CUs faster than most people balance their checkbook to fix stuff on a product that's been around for 30 years... or the ones that blew some major portions of the code at work out of the water with their new cardinality estimator?
I have a huge appreciation for the problems associated with complexities of the code behind SQL Server but the folks in Redmond aren't gods (and that's not a slam against them but could be in the future if they keep going the way they're going 😉 ). You don't actually have to know or understand the optimizer as they claim to know when it's done something wrong and needs some help and that's a part of the reason why they did include index hints.
To wit, I take strong exception to the statement you quoted because sometimes the answer is "The people in Redmond don't have to solve this particular problem... I DO"!!
--Jeff Moden
Change is inevitable... Change for the better is not.
October 23, 2017 at 11:54 am
Jeff Moden - Saturday, October 21, 2017 3:09 PMkevaburg - Saturday, October 21, 2017 12:56 AMAnother reason I want to avoid hints is this: I attended a lecture from a MCM here in Germany and he made this statement:
"If you feel the only way to solve a problem is to use a hint you are making a conscious ascertation that you understand the optimiser better than the 200-300 professional developers in Redmond that developed it".Heh... You mean the same folks that developed the crippled PIVOT even though MS has a great model of a non-crippled version in ACCESS... the ones that crippled performance with FORMAT... the ones that made recursive CTEs run slower and more resource intensive than a While Loop... the ones that created a splitter function with no guarantee of sort order and no return of element position... the ones that cause online rebuilds to sometimes corrupt the clustered index in 2012... the ones that refuse to have a built in Tally Table function because they don't understand how it would be used... the ones that keep deprecating functionality and frequently replacing it with something that doesn't work as well... the ones that caused SSIS installations to go corrupt after an SP release... the ones that still haven't figured out how to do a Peter Norton style shrink of a database even though the technology has existed for more than 3 decades... the ones that still haven't created an easy to use true CSV file importer for T-SQL.. the ones that have to issue CUs faster than most people balance their checkbook to fix stuff on a product that's been around for 30 years... or the ones that blew some major portions of the code at work out of the water with their new cardinality estimator?
I have a huge appreciation for the problems associated with complexities of the code behind SQL Server but the folks in Redmond aren't gods (and that's not a slam against them but could be in the future if they keep going the way they're going 😉 ). You don't actually have to know or understand the optimizer as they claim to know when it's done something wrong and needs some help and that's a part of the reason why they did include index hints.
To wit, I take strong exception to the statement you quoted because sometimes the answer is "The people in Redmond don't have to solve this particular problem... I DO"!!
Wow.....I feel like I have just been verbally body-slammed! 😀
I can appreciate your comments without any qualms at all although the problems you have outlined are not problems I have (thankfully) had to deal with up until now. But the sorts of issues that SQL Server has are not unlike those found in Oracle (now we are talking about problems......ORA600, ORA7445) but then this is also the reason for my original post. Testing at any level will produce results that can sometimes digress from what we would regard as normal in a production environment and I have seen this in the cardinality estimator first hand. In this case though, reverting back to the legacy estimator solved the problem.
I am not regarding the Redmond guys as Gods, we all know they aren't but they work under a different environment (sandboxed, safe, call it what you will) and can't test every permutation of every possible outcome so problems will occur especially in programs as large and complex as Microsoft products have become over the years. My OP was based on finding a way to help alleviate a problem associated with performance optimisation with minimal risk and here, how the optimiser works and selects an index plays a large role. All I wanted was quite simply to remove the index from the list of available indexes to choose from without losing control should I need to roll back.
Up until now my primary position has been as a DBA and during that time I have experienced a lot of problems because of quite simply bad SQL and that badness was resolved using hints, heavy use of parallelism (because more cores = more threads = better performance.....we have all seen this as well) and a desire to ignore what the optimiser has planned because a) the developers can't read execution plans, b) the developers don't understand indexes and statistics and c) don't understand the consequences of putting this all straight when it eventually goes wrong - the job of the DBA.
The only advice I give the developers is to use hints only if every other avenue has been exhausted, even if that means going to Microsoft and using the support they pay for. I don't believe in using hints if they aren't really necessary because I see them as more of a plaster on a festering wound than a solution and I know for a fact that if we relax that rule the promise of "we will use the hint for now and fix the problem later" will never come to fruition.
I have gone massively off-track but I just wanted to justify why I take this stand the way I do. The single most valuable asset a company has is data, at least in my opinion, and I just want to be sure that it is treated the way I believe it should be treated.
October 23, 2017 at 5:45 pm
For what it's worth, I absolutely agree that the use of index hints should generally be an absolute last resort and then you should still go back and see what you're missing.
And sorry for the long winded retort. Allow me to explain the fervor there.
I regularly try to dispel supposed "Best Practice" recommendations that are based on hear-say, anecdotes, old-rev lessons that mostly no longer apply (the use of SELECT/INTO is a really good example there), proverbial old wives tales, or just because enough people think it's a cool-kid thing to say. I've heard the "smarter than Redmond" anecdote from numerous people and, in context and in person (to get the tonal inflection, which is frequently based in technical humor), it's quite easy to understand that the speaker is saying that it's generally a bad idea. I have no problem with that.
The problems occur when someone cites the fact that person is an MCM (the best cert there ever was or ever will be) to support an argument for something. A thousand lurkers will read something like that and take it as absolute gospel. Word spreads and suddenly we have another "Best Practice" that people, especially the auditors and less informed DBAs that I have to work with, and then I have to go through all sorts of proofs to prove to them that they don't know what they don't know.
Heh... I know you think that sounds extraordinary but it's happened several times. SELECT/INTO locking TempDB, Recursive CTEs being good, cursors and while loops being absolutely forbidden, not being allowed to use Ad Hoc Distributed Queries, not being allowed to use OLE Automation Procedures, being forced to use MERGE for joined updates instead of update because someone doesn't actually know how to use it correctly, table hints, index hints, query hints (OPTION (RECOMPILE will save your life for "Minimal Logging), and the king of them all, not being allowed to us xp_CmdShell because of its supposed security risk. And all of those have been forbidden by folks that don't actually understand what's going on but insist on not using them because they're supposedly a "Best Practice" even if they understand that there can be exceptions to "Best Practices".
So, every time I see someone cite something someone said and then with no proof as to utility or harm says "Good enough for me" just because of the speaker's supposed or actual pedigree, I go nuts.
Help stop dis-information and misinformation. Stop promulgating such anecdotal recommendations.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 23, 2017 at 5:49 pm
p.s. And I absolutely understand your stance and reasoning when it comes to the absolute dribble that some call "code". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply