March 7, 2012 at 6:08 am
HowardW (3/7/2012)
You should bear in mind that recompiling each time isn't that expensive, so it's a valid option.
Depends on how often 'each time' is. If I have a complex query that runs 100 times a minute, I probably do not want that recompiling on every execution. Sure, modern CPUs have a lot of power, but compilation is expensive (CPU and memory) and has been getting more expensive on each new version as the optimiser gets more powerful
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 7, 2012 at 6:32 am
paul.goldstraw (3/7/2012)
What I would want is for the compiler to see somehow (don't ask me how) that the compiled value and the runtime value aren't the same and had we compiled with the runtime value, a different plan would have been generated, and to instead either generate another plan or do something that is a bit more generic, that it may not be the best plan but it's not going to be the worst.
Not the compiler, because once a valid cached plan is found the optimiser is bypassed and the cached plan goes through some simple checks (has the schema changed, has the statistics changed) and then gets executed.
To do what you want there are two main options:
- Discard the cached plan any time the parameter value is different from the compile time one and recompile. This will make cached plans virtually useless as the optimiser would be recompiling for different parameter values even when the estimated row count is identical and the resultant plans identical
- Re-estimate the row counts for each different parameter value and recompile if there's a significant difference. What's significant? Well, varies per query. I've seen cases where a couple of rows difference will change the plan and other cases where the data volume can increase by a factor of 10 with the plan staying identical. So again here they'll have to estimate what will have an effect and they'll be wrong in some cases.
Either way will add overhead to each and every query running on the server and benefit what is usually a very small percentage of the queries.
If there was a simple solution to this, it would already be in the product. I've chatted with someone from the optimiser team on a couple of occasions about this kind of problem, its not simple and there are all sorts of tradeoffs to consider.
If i have 4 stored procedures, all doing the same sorts of things, returning filtered sets of data about 1 or more tables in a format suitable for the application calling it, then why might one suddenly start suffering from this problem? This is the crux of what i'm after, what is the tipping point?
Really hard to say, there are a bunch of possible causes.
Any chance you can get an execution plan of poor performance and another one with good performance from the same procedure? The answers will be in the plan, and it's hard to guess without that.
Have you checked if it's related to SET options rather than parameter values? The symptom of being slow when called by the app but fast in management studio sometimes suggests SET option differences as they are usually set differently from SSMS vs from ADO/ADO.net/jdbc/OLEDB/ODBC
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 7, 2012 at 9:26 am
Gail,
Thanks for elaborating on the complexities of this issue. I do appreciate any resolution that I would class as a fix would have negative side-effects as parameter sniffing is, in most cases, a good thing. I guess what I would want is something that increases its intelligence as it learns the database. So a procedure that has been in place for 6 months, completing in about 2 seconds on average and with little variance, were it to suddenly take 10 times that, SQL Server would realise this is out of the ordinary and take steps to resolve the issue. No it wouldn't work for every scenario, you couldn't allow something like that to take over unless the procedure was well bedded in and and some consistency to it, but there are surely means of identifying problems as they're occurring; plans that are so obviously a load of rubbish that they are never going to work.
As for the execution plans, i have some very old ones that might be of use but I would prefer to get hold of more recent ones. I asked for a trace to be put on this morning and haven't had anything back yet but hopefully i'll hear back at some stage.
As for the SET options being the culprit, while I can't rule it out for every occasion, we did find when we first started looking at this about 2-3 years ago that the web server connects with ARITHABORT set one way, and SSMS defaults to the other way (I don't recall which way was which). However I'm fairly confident this isn't the cause as most of the time these procedures do work within the website. It's just on rare occasions that something causes it to go wrong and until the plan is recompiled it continues to go wrong. After that it returns to normal performance levels where SSMS and the website have similar performance
Thanks
Paul
March 7, 2012 at 9:37 am
I'm not suggesting that the SET options are causing the problem, just make sure when you test from SSMS you have them set exactly the same way, or you'll get a freshly compiled execution plan (Arithabort is one of the set options that is a plan cache key) and you won't be able to repo the issue.
If you can get exec plans, please zip them and post them here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply