June 9, 2013 at 6:56 pm
A front end app sends a query to SQL 2008 using sp_executesql.
The query returns a count as the final result.
The query hits some large tables (2m - 3m rows)
The query is very poorly constructed (one of those SQL generators) consisting of many joins, cross DB joins (same server), sub queries, correlated sub queries etc etc.
Five parameters are passed via sp_executesql, with one of these values the most likely to change and affect the size of the subset of data.
The query normally takes a couple of seconds to run with the average number of rows in the first part of the query plan being 3000 - 5000.
Every now and then the query takes 15 - 20 minutes.
The only change is the one parameter (that mainly affects the subset of data)
In the cases we know of this change takes the subset to 50,000 - 60000 records when the slow performance happens.
Even when the same query is run via sp_executesql in SSMS it normally only takes seconds.
So having said that..I got some info the other day when the query was slow and the execution plan it used was WAY different.
It was not a case of the same plan that was not optimal for the slightly larger record set but a totally different plan.
If I run SP_UPDATESTATS it fixes the issue -- I assume it forces a plan recompile so only indirectly fixes the issue??
Comparing the same statement using sp_executesql , one good and one bad I noticed the following from the Plan XML:
Remember exactly the same query run in exactly the same way except one must have had a new plan created via the update stats.
good: (1 second)
EstimateCPU="0.000756482"
ActualRows="3608" -- from the first filter step in the plan
<ColumnReference Column="@P2" ParameterCompiledValue="(3817)" ParameterRuntimeValue="(3817)" /> -- 3817 is the value passed in
bad: (17 min)
EstimateCPU="3.91782E-05"
ActualRows="2277436" from the first filter step in the plan
<ColumnReference Column="@P2" ParameterCompiledValue="(3969)" ParameterRuntimeValue="(3817)" /> -- 3817 is the value passed in
So I guess the difference in performance is for the same step (JOIN) in the plan the good one returns 3608 rows the other one returns 2.2 million rows.
and it is SEEKing and doing Key Lookups with these 2.2 million rows.
Is that enough info for somebody to help me understand why it does this and how to fix it?
You can also see the bad query is re-using a previous plan because the ParameterCompiledValue and ParameterRuntimeValue values are different?
I thought parameter sniffing with sp_executesql would use the same plan for each parameter which may cause a SEEK instead of a SCAN rather than a totally different plan?
thanks
June 9, 2013 at 11:50 pm
Bad parameter sniffing.
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
https://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/
Or maybe stale stats.
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
June 10, 2013 at 12:24 am
Or maybe stale stats.
The large tables do have a number of updates / inserts during the day but this varies depending if a campaign is being run or not.
I do an sp_updatestats on the tables before the nightly index jobs.
Indexes may or may not be rebuilt (therefore creating new stats) depending on the fragmentation threshold at the time of the job run, so an sp_updatestats may be all they get.
Auto Update Stats is on for each DB involved.
So assuming it is out of date stats then:
1. Would an extra sp_update stats run during the day help (I don't think this locks anything does it?)
2. Or would an UPDATE STATISTICS() run nightly with as high a scan % as it possible be better?
or both...
Is is common for companies to run extra stat updates during the day?
I am still trying to fully understand SP_UPDATESTATS and when to run this command over an UPDATE STATISTICS command with a scan %
June 10, 2013 at 12:30 am
It may be the case of parameter sniffing, alter procedure with WITH RECOMPILE and try.
June 10, 2013 at 1:18 am
Obviously I could be wrong but thought the downside of Parameter sniffing could mean the same plan being used for two queries that return different size result sets. i.e the plan is built on a SEEK so all queries use a SEEK where a SCAN might be more optimal depending on what parameter is passed in?
In my case the query plans are totally different.
Is this possible with parameter sniffing?
June 10, 2013 at 2:34 am
UncleBoris (6/10/2013)
Obviously I could be wrong but thought the downside of Parameter sniffing could mean the same plan being used for two queries that return different size result sets. i.e the plan is built on a SEEK so all queries use a SEEK where a SCAN might be more optimal depending on what parameter is passed in?
Yes, that's correct
In my case the query plans are totally different.
Is this possible with parameter sniffing?
You have one plan when the query is compiled and executed with the same parameter value and a different one when the plan is compiled with one parameter value and executed with a different one.
Hence, you have a situation where the query is executed with a plan appropriate for a different parameter value (where the compile and execute parameters are different. That's bad parameter sniffing.
The fact that the plans are not staying in cache long and getting compiled often and hence you're seeing different plans at different times is another matter
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
June 10, 2013 at 1:00 pm
I suppose that next question is how do I handle this?
Assuming I can get the front end code changed(unsure about this though):
1. Simplify the query if possible?
2. Add RECOMPILE option in the query ??
If I can't get access to the front end code:
1. Run another update stats during the day?
2. Turn on adhoc for workloads so more plans stay in the plan cache longer??
3. Add a plan guide for this query so it RECOMPILES?
I don't really know what else would help
Sorry, I will have a read of your links in case you have the answers already listed.
June 10, 2013 at 3:53 pm
UncleBoris (6/10/2013)
Sorry, I will have a read of your links in case you have the answers already listed.
They are.
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
June 10, 2013 at 8:45 pm
I have read the articles and understand the "Parameter Sniffing" issues and some of the methods that may prevent / reduce this.
In my case I am still unsure why an entirely new plan was created, rather than just use the original less than optimal plan for the increased result set.
If the statistics are getting outdated on some tables then I may have to updates statistics on one or two big tables during the day - is this a possible practice to do or is it classed as bad practice / last resort?
June 11, 2013 at 1:57 am
UncleBoris (6/10/2013)
In my case I am still unsure why an entirely new plan was created, rather than just use the original less than optimal plan for the increased result set.
As I said, from what you describe you have parameter sniffing (the case where the plan was compiled with one value and used with another) as well as having plans getting frequently compiled. Two issues, not one.
If the statistics are getting outdated on some tables then I may have to updates statistics on one or two big tables during the day - is this a possible practice to do or is it classed as bad practice / last resort?
I've heard of cases where stats got updated hourly.
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply