August 9, 2011 at 3:53 pm
We have a situation where our primary accounting software executes a query with a table hint that absolutely kills performance. While the query is still somewhat slow without the HINT its really slow with the Index Hint.
Because the T-SQL code that contains the Query Hint is not acesable and therefore can not be altered (i.e. no way to correct this bad design of a query) whats the best way to combat this thing? I've found a piece at MSDN on Forcing Query Plans to try and do this but if anyone has any better suggestions I'm all ears.
If I undertsand the pice on Forced Query Hints it does not allow for forcing the over ride to use whatever SQL Server believes to be the best method; in otherowrds how to make the system ignore the query hint to begin with and process the query like as if it never had a hint to begin with.
Kindest Regards,
Just say No to Facebook!August 9, 2011 at 5:24 pm
So the hint is being submitted in the T-SQL?
I'm not sure you can override this without removing the hint from the code. I believe the hints in the query are supposed to override everything else.
August 10, 2011 at 8:08 am
No real way around this. The whole idea of hints to take away control from the optimizer. One of the many reasons I'm not in favor of using hints.
"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
August 10, 2011 at 1:17 pm
Grant Fritchey (8/10/2011)
No real way around this. The whole idea of hints to take away control from the optimizer. One of the many reasons I'm not in favor of using hints.
Agreed. I also found it wildly humorous they were called hints: they're more like the infamous "offer you can't refuse".
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 10, 2011 at 1:26 pm
Matt Miller (#4) (8/10/2011)
Grant Fritchey (8/10/2011)
No real way around this. The whole idea of hints to take away control from the optimizer. One of the many reasons I'm not in favor of using hints.Agreed. I also found it wildly humorous they were called hints: they're more like the infamous "offer you can't refuse".
Yeah, that's a good way to describe them.
"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
August 10, 2011 at 2:50 pm
Heavy sigh...
I want to call them Query FRAGS instead of HINTS.
Thanks guys
Kindest Regards,
Just say No to Facebook!August 10, 2011 at 2:51 pm
YSLGuru (8/10/2011)
Heavy sigh...I want to call them Query FRAGS instead of HINTS.
Thanks guys
We are the query mafia, all your options are ours. All your indexes are ours. Now, pay up or we take your joins, too.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 11, 2011 at 9:42 am
I wonder if Plan Guides would be useful here. Theoretically you could override the plan the Optimizer would use for that specific query text and that plan could be one that does not make use of the hints. I haven't done it, hence the "theoretically", but it might be worth a look.
http://technet.microsoft.com/en-us/library/ms187032(SQL.90).aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 11, 2011 at 12:24 pm
opc.three (8/11/2011)
I wonder if Plan Guides would be useful here. Theoretically you could override the plan the Optimizer would use for that specific query text and that plan could be one that does not make use of the hints. I haven't done it, hence the "theoretically", but it might be worth a look.http://technet.microsoft.com/en-us/library/ms187032(SQL.90).aspx
Thanks for the info. In my orginla post when I said Forced Query plans I meant this Plan Guides feature but used the wrong description. It wil let me over ride the query but if I undertsoond it corrctly when I lkast red it you have to explcitly provide the plan to use instead and my goal was to over ride teh Query Hint in a way that lets the query optimizer determine what is best.
You see the problem is that the most effeciebt way to process the query is not the same trhoughout our cycle which is a month. Sometimes the query hint provided works better but most of the time when I;ve tested it the hint makes the query less effecient tehn running it and letting the query optimizer make that decission.
Thanks
Kindest Regards,
Just say No to Facebook!August 11, 2011 at 1:12 pm
YSLGuru (8/11/2011)
You see the problem is that the most effeciebt way to process the query is not the same trhoughout our cycle which is a month. Sometimes the query hint provided works better but most of the time when I;ve tested it the hint makes the query less effecient tehn running it and letting the query optimizer make that decission.
I see. Well, if it's more efficient without the hint the majority of the time then use the Plan Guide to override what your vendor app is doing. If not, then leave it as-is. If it matters the most to use the best plan at all times then you could add and remove the Plan Guide at different times of the month, as needed, either manually or with a scheduled job. You're in a tough spot for sure, good luck!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 14, 2012 at 8:58 pm
I know this post is a few months old, but what about using traceflags 8602, 8755 & 8722 ??
Obviously depending on what the hint is..
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply