Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_executesql -- can produce very wrong execution plan Expand / Collapse
Author
Message
Posted Sunday, June 09, 2013 6:56 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 1:56 PM
Points: 93, Visits: 463
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



Post #1461369
Posted Sunday, June 09, 2013 11:50 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:58 PM
Points: 41,513, Visits: 34,428
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 2008, MVP
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

Post #1461389
Posted Monday, June 10, 2013 12:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 1:56 PM
Points: 93, Visits: 463
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 %


Post #1461399
Posted Monday, June 10, 2013 12:30 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:36 AM
Points: 406, Visits: 383
It may be the case of parameter sniffing, alter procedure with WITH RECOMPILE and try.
Post #1461400
Posted Monday, June 10, 2013 1:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 1:56 PM
Points: 93, Visits: 463
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?

Post #1461410
Posted Monday, June 10, 2013 2:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:58 PM
Points: 41,513, Visits: 34,428
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 2008, MVP
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

Post #1461425
Posted Monday, June 10, 2013 1:00 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 1:56 PM
Points: 93, Visits: 463
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.
Post #1461731
Posted Monday, June 10, 2013 3:53 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:58 PM
Points: 41,513, Visits: 34,428
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 2008, MVP
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

Post #1461790
Posted Monday, June 10, 2013 8:45 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 1:56 PM
Points: 93, Visits: 463
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?



Post #1461843
Posted Tuesday, June 11, 2013 1:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:58 PM
Points: 41,513, Visits: 34,428
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 2008, MVP
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

Post #1461912
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse