With SQL 2005, using the NOEXPAND hint can result in an error:
Msg 8171, Level 16, State 2, Line 1
Hint 'noexpand' on object 'dbo.ervStats' is invalid.
I haven't tried it with 2008 yet but I expect the same behavior. That behavior can cause problems and I would like to understand the logic behind it. I would expect a Warning to be raised, not an error.
Scenario:
OLTP application using one or more indexed view to speed up some functionalities. This can happen as some db are OLTP but also used for real time reporting that can't be done on a mirror. At some point you may find out 1 indexed view is the cause of excessive locking for 1 specific site, so you remove the index....but must now recompile and redeploy the application to hundreds of desktops to remove that 8171 error message.
If a warning was issued instead of an error, the problem would not occur. Nothing is changed at the logical level and the query would run as expected is the hint was ignored.
There is possibly a very good reason to raise an error instead of a warning, but I can't see it right now. Before I create a feature request on Connect can somebody shed some light on that behavior? (I might change my mind once I understand the logic:-) )
PS: I KNOW indexed views are not meat for OLTP scenarios, but there are edge cases where they do help when designed properly. Sometimes business scenarios change and they must be changed or dropped.
thanks
L
Hi Lakusha - I'm not sure of the direct answer to your question, but as another option vs. recompiling and redeploying your application, have you looked at the possibility of using a plan guide for addressing the issue?
|||
Hi Chad,
No, I haven't looked at plan guides for this. Perhaps I am wrong, but I feel plan guides can't help much in our context. We have hundreds of servers at hundreds of locations (ISV context). I can manage plan guides easily but customers support teams won't. Even something as trivial as dropping an index on a view can prove too much because their schema becomes non standard and they must remember to drop it again after each major upgrades (and sometimes they won't and this will become a support call). Support will check immediately if they upgraded recently and forgot to run the drop index script but plan guides are another matter.
A plan guide will also lock you up. In a complex query I might want to use a NOEXPAND on a materialized view (especially when the Enterprise query optimizer sometimes can't be trusted to choose the view but also because some customers use the Enterprise versions while others use Standard...so the lowest common denominator wins) but I certainly don't want to force the plan on the entire query because it will certainly be a good plan for some customers but not for others.
I also think that discarding a hint is certainly worth a warning but not an error since it does not change anything from a logical stand point. The exact behavior could even be controlled at the db level, or even better, at the hint level.
Most of all, I would like to understand why they choose to raise an error and not a warning. In the end it is best to understand why things work a certain way.
PS: I know MS Research has done a lot of work to support outer joins and stacked indexed views but I haven't seen anything new advertized for indexed views in SQL2008...
thanks
L
|||
Hi Lakusha. Let me try to address points mentioned in this thread one by one.
The behavior of noexpand hint is same as any other hint and, as far as I know, except consistency with other hints there are no other fundamental reasons why it is an error instead of a warning. You definitely can propose this via Connect.
Plan guides will not really solve this problem (with one exception, see my next bullet) since noexpand hint is not a query hint.
You can make a plan guide with USE PLAN providing plan which has indexed view in it and this will force using indexed view same way as noexpand hint. But ... you will be pinning whole plan not allowing for potentially better alternatives as circumstances change, and in SQL Server 2005 it still will generate an error if index is not present on the view. In CTP5 we are planning to have plan guides functionality which will not error out in such case but rather generate plan as if no plan guide was present.
The only workaround I know about is to wrap your query in an SP with following logic:
if <Index is present on the view>
then <run query with noexpand hint in it>
else <run same query without noexpand hint>
There is an added inconvenience of having to wrap it into SP, but it will let you avoid redeployments you mention.
Thanks Alex.
I might make a suggestion about this on Connect then. As for plan guides, I am aware of the drawbacks. Using a SP is indeed a good suggestion. I did not consider it because, for internal reasons, we have chosen to never use them. It is the old adhoc vs SP debate again and in our case ad hoc queries won (we use a DAL that works quite a bit like LINQ).
Thanks
|||Thanks for the post. It's very helpful.
Do you mind providing some more insight regarding the 3rd bullet? I have an ad-hoc query submitted from Business Objects. When I copy the query from Profiler and add a "noexpand", the query runs lightening fast. Without the "noexpand", the query is tremendously inefficient.
So, I then ran the query (with the noexpand) and captured the showplan XML and stuffed the showplan results into the use plan option. Next, when I run the query (with the noexpand and the use plan), it runs great.
Then, when I remove the "noexpand" from the query (but not the use plan), the following error is issued:
Msg 8698, Level 16, State 0, Line 2
Query processor could not produce query plan because USE PLAN hint contains plan that could not be verified to be legal for query. Remove or replace USE PLAN hint. For best likelihood of successful plan forcing, verify that the plan provided in the USE PLAN hint is one generated automatically by SQL Server for the same query.
The reason I removed "noexpand" from the query is because Business Objects does not submit the query with noexpand. I suspect that the reason the error was issued is because the query expanded the view and the plan did not.
In all cases, the query referenced the indexed view. We are running the Enterprise version.
So, how can noexpand be implemented when my company is not the author of the code?
- Is there a machine or database level configuration that could be set?
- How can a noexpand appear in the plan but not in the query without getting the error message above?
- Other?
Any insight you provide will be greatly appreciated!
|||(DaveIII@.discussions.microsoft.com) writes: > Then, when I remove the "noexpand" from the query (but not the use > plan), the following error is issued: > > > > Msg 8698, Level 16, State 0, Line 2 > > Query processor could not produce query plan because USE PLAN hint > contains plan that could not be verified to be legal for query. Remove > or replace USE PLAN hint. For best likelihood of successful plan > forcing, verify that the plan provided in the USE PLAN hint is one > generated automatically by SQL Server for the same query. I guess this is because that in order to validate that your plan is valid, the optimizer keeps generating plans, until it has generated yours. But when the query has NOEXPAND, the optimizer never sees the indexed view; it sees the expanded query, and then will have to try view substitution to come back to the view. And since view substitution is an expensive operation, it does not try everything. Clearly, this is not a very pleasant situation. If nothing else, it's not a very intuitive behaviour. I have not checked Books Online, to see if this restriction is documented. I think you should submit this issue on Connect, but check Books Online first, as I think you should have a different angle depending on whether it's documented or not. If it's not documented, just submit it as a bug. If it's documented, you are better off making this as a suggestion, I think. Of course, in either case, the devs would appreciate if you can supply a repro. -- Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
No comments:
Post a Comment