SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sp_executesql -- can produce very wrong execution plan


sp_executesql -- can produce very wrong execution plan

Author
Message
UncleBoris
UncleBoris
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 730
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86685 Visits: 45254
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


UncleBoris
UncleBoris
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 730
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 %
Bhaskar.Shetty
Bhaskar.Shetty
SSC Eights!
SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)SSC Eights! (854 reputation)

Group: General Forum Members
Points: 854 Visits: 509
It may be the case of parameter sniffing, alter procedure with WITH RECOMPILE and try.
UncleBoris
UncleBoris
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 730
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86685 Visits: 45254
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


UncleBoris
UncleBoris
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 730
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86685 Visits: 45254
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


UncleBoris
UncleBoris
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 730
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86685 Visits: 45254
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search