Do Hints Work Anymore?
-
Interesting, if somewhat frustrating, morning...
A couple of weeks ago, one of our developers complained of the slow performance that one of his stored procedures experienced on one of our servers. After much wailing and gnashing of teeth, we (actually, Julie, my associate -- just don't want to steal any credit) discovered a solution that involved creating a covered index on the largest table in the FROM clause of the killer SELECT. It went from about 1 min 15 sec to 15 sec. Yay!
Well, this morning, the developer came over to complain again. We have a web reporting system here. On one of our servers: if the procedure was executed through Query Analyzer, it ran in 15 sec. But if it runs through the reporting system, we're back up to 1 min 15 sec. Through the Profiler, I verified that when run through our web reporting system, the Query Plan recidivistically reverts to its former behavior and uses the primary key's clustered index for the search instead of the nice covered index we had slaved all day over a hot tube to make for its royal fussiness. It still used the wrong index even when I specified "with recompile" in the procedure.
(An aside: Index selection is one area where Oracle seems to have it all over SQL Server. It's a little bit frustrating when you can spiff up and put lipstick on an index containing every single column referenced in a table, only to have the Execution Plan decide a different index has nicer legs, anyway. SQL Server's ability to pick a winning index reminds me of P. J. O'Rourke's hilarious discussion of using pointers to hunt grouse, which P.J. describes as "military field chickens". They have the ability to sit perfectly still and perfectly camouflaged, and won't flush when you get close. That's where you think a hunting dog might help, but unfortunately, grouse doo-doo smells even more like grouse than grouse does, so your expensive, highly-trained dog spends half the day pointing at little white spots on the forest floor. That's SQL Server looking for an appropriate index.)
So, sez I, why should it matter to the DBMS engine whether the procedure is executed through Query Analyzer or through our web reporting system? Why would it see the right index one way, and not the other?
Thinking I was helping matters, I then tried to stick a compiler hint on the offending table, forcing it to use the covered index instead of the clustered index. Imagine my surprise when I got this message back:
quote:
Server: Msg 8622, Level 16, State 1, Procedure p_AttendancePctCompareYears, Line 134Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
A new one on me. This must be one of those features we paid for when we upgraded from SQL Server 7: compiler hints than don't work as advertised. Thinking this might have additional explanation in the Troubleshooting Guide, I looked up the error message, and it clarified the situation by explaining:
quote:
Server: Msg 8622, Level 16, State 1, Procedure <...>, Line <...>Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
Well, I'm glad they cleared that up.
So I guess this boils down to a couple of questions:
1. Why would one execution plan be chosen when the procedure is run from Query Analyzer, and a different (wrong) plan chosen when run from our web reporting system?
2. Why don't the query hints work anymore? Is there a 'dammit' option on the table hints, i.e., "WITH (INDEX (<idxname>), dammit)"?
3. Do either of these issues, but especially the compiler hint issue, seem to implicate the MDAC drivers? Query Analyzer uses OLE-DB, I think, but the web reporting system uses ODBC. Am I looking at an incompatibility issue?
As always, your good advice is appreciated.
Edited by - Lee Dise on 12/22/2003 10:22:11 AM
-
I am assuming you have a non-clustered covering index. Does it makes a difference if you change it to CLUSTERED.
It happened to me once
* Noel -
1. I've seen this as well and I have no idea. Some piece of code in the Optimizer is not working as expected.
2.
. send to sqlwish@microsoft.com3. I wouldn't think that the access method matters, but that's an interesting one. Can you check from a third system using both access methods?
4. have you cycled the server or run a dbcc freeproccache?
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
-
quote:
I am assuming you have a non-clustered covering index. Does it makes a difference if you change it to CLUSTERED.I can't cluster this one. Already have a clustered index on the primary key, and don't want to speculate on how many other apps I'd wreck if I were to change it.
quote:
It happened to me onceMaybe the server just needs a shot of Viagra?
quote:
1. I've seen this as well and I have no idea. Some piece of code in the Optimizer is not working as expected.Well, if you've seen it and don't know what causes it, at least I don't feel like the village idiot anymore.
At least, not today.quote:
2. . send to sqlwish@microsoft.comIs that "squish", for short?
quote:
3. I wouldn't think that the access method matters, but that's an interesting one. Can you check from a third system using both access methods?Maybe. You know, this would almost have to be some sort of caching thing, wouldn't it?
quote:
4. have you cycled the server or run a dbcc freeproccache?I just ran a FREEPROCCACHE and will see shortly if (in Heidi Fleiss' words) that did the trick.
In any event, what's the deal with the compiler hint issue? Anyone know why SQL Server refuses to run my compiler hint inside the stored procedure?
Thanks!
Edited by - Lee Dise on 12/22/2003 12:15:57 PM
-
Found from BOL.
"If an index hint referring to multiple indexes is used on the fact table in a star join, SQL Server ignores the index hint and returns a warning message. Also, index ORing is disallowed for a table with an index hint specified. "
-
quote:
Found from BOL."If an index hint referring to multiple indexes is used on the fact table in a star join, SQL Server ignores the index hint and returns a warning message. Also, index ORing is disallowed for a table with an index hint specified. "
I'm not sure if any of this applies to what I'm doing. But I'll check it out, thanks!
-
I'd advise replacing the WITH RECOMPILE with a periodic and scheduled sp_recompile.
The WITH RECOMPILE option may have taken effect when 'unusual' data quantities or values (or indices) were in place....
Try including a sp_recompile of the SP as part of your daily/weekly/monthly optimisation job - after updating the statistics.
Sounds like something is going wrong with it's plan.
Is the SP run over the w/end when an index it requires isn't there? That would cause a plan which wouldn't be changed when the index is created.
-
Not sure if you have tried running the index wizard on this, if so the wizard can create some statistics which do not always get deleted unless it is completed. These statistics can cause some odd issues in the database that cannot be resolved until they are deleted. Unfortunately you do have to go through some very specific steps to delete them, and I do not have the KB article handy.
We ran into problems with these statistics when MSSQL went into an iterative recompile of an SP due to changes made to these pseudostatistics.
As stated before, you have to go through some specific steps to drop these statistics, they are not affected by the normal maintenance processes for rebuilding statistics.
-
The KB for the above comment is http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q293/1/77.ASP&NoWebContent=1
-
Also, not knowing the query which is being submitted....
The Set FORCEPLAN may be conflicting with your query hint (which is what the error message appears to be telling you)
SET FORCEPLAN -- (from BOL)
Makes the Microsoft® SQL Server™ query optimizer process a join in the same order as tables appear in the FROM clause of a SELECT statement only.
SET FORCEPLAN essentially overrides the logic used by the query optimizer to process a Transact-SQL SELECT statement. The data returned by the SELECT statement is the same regardless of this setting. The only difference is the way SQL Server processes the tables to satisfy the query.
Query optimizer hints can also be used in queries to affect how SQL Server processes the SELECT statement.
The setting of SET FORCEPLAN is set at execute or run time and not at parse time.
So if you are adding the SET FORCEPLAN then don't add it when using query hints.
If you are not adding it but it is being added by your provider (ODBC, OLEDB, ADO, ADO.NET) then you will need to track down the appropriate property to set on that command to stop the forceplan from happening.
-
This happended to me recently where a stored procedure went from 30sec to 5min execute time. The fix for me was to update all the statistics in the database.
-
I know I am going to get crucified on this one but...
If the covered index that you worked on is what you wanted is it possible to place that in your SELECT statement?
SELECT Column1, Column2, Column3
FROM table1 WITH(INDEX(IndexNameHere))
This way your stored-procedure would always use the index you want instead of picking one for you.
I do know that WITH INDEX HINT is generally NOT a good thing but...
Good Luck
AJ Ahrens
Good Hunting!AJ Ahrens
webmaster@kritter.net -
I have also seen this behavior… a sp works great in Query Analyzer, but when it runs thru the app it’s performance drops dramatically…. Looks like everyone is looking in the wrong direction… The stored procedure performance is fine in Query Analyzer, but when called from the app it slows to a crawl… your problem might be how the sp is called from within the app. How is the developer calling the sp (ADO?). If so, is he/she using a command object or some other method to execute the sp. I had a developer with the same problem… his query ran in less than a second in Query Analyzer, but from his app, it was taking over a minute…. The solution – was to use ADO2.6 Command Object vs. using an in-house developed dll. Hope that helps…
-
Another issue along with hfranz is that the application might not be constructed in a way to be able to handle the SET FORCEPLAN. I have run into problems in the past that regardless of what I did in the sp, SELECT, etc... the 3rd party app would determine (based on it's own create of indexes internal to itself) what it would use.
Hope you find the answer and can help the rest of us look in the right direction if this happens to us
AJ Ahrens
Good Hunting!AJ Ahrens
webmaster@kritter.net -
As you know the values passed as parameters have a significant impact on execution plans. I read somewhere, that when a query is compiled in a stored procedure, the optimizer does something called parameter sniffing. In other words, it looks to see if the local variables used in the query, to pass values, are the ones defined in the parameter list of the stored procedure. If they are, it will use the values passed to optimize the query. If the local variables where declared in the procedure and assigned values via a SET or SELECT then it will not use those values for optimization. The end result is a more conservative execution plan.
When you execute the query using Query Analyzer, it uses the values passed to optimize the query plan, hence using the covered index.
Somethings to try. Use a dynamic SQL Exec statement in the stored procedure to exec the query or use the SP_ExecuteSQL in the stored proc. These would end up passing values directly and maybe the optimizer will give you a better plan.
Hope this helps and have a happy holiday.
Jeff
. send to Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply