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 12»»

Different execution plan between SQL and SP Expand / Collapse
Author
Message
Posted Tuesday, November 27, 2012 12:59 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 8, 2014 2:40 PM
Points: 271, Visits: 1,020

Hello, wonder if someone can help, i am looking at a performance issue with a stored procedure (SP). The SP its self is very simple only a few table joins and a single parameter ( integer data type).
When I execute as a stored procedure I get a totally different execution plan to when I execute as a select statement. Both queries are executed using the same method through SSMS QA
Below is an extract from statistics profiles, set one is from the SQL query, the corresponding execution plan to this query uses clustered index seeks whereas the second set of data executing as SP, you can see there are a lot of index scans. However, if i execute the SP as Exec SP_NAME PARAMETER with Recompile the query executes using the better execution plan.
AS SQL Query
Parent PhysicalOp LogicalOp
0 NULL NULL
1 Nested Loops Inner Join
2 Compute Scalar Compute Scalar
3 Nested Loops Inner Join
4 Nested Loops Inner Join
5 Nested Loops Inner Join
6 Compute Scalar Compute Scalar
7 Nested Loops Inner Join
8 Nested Loops Inner Join
9 Clustered Index Seek Clustered Index Seek
9 Filter Filter
11 Clustered Index Seek Clustered Index Seek
8 Clustered Index Seek Clustered Index Seek
6 Clustered Index Seek Clustered Index Seek
5 Clustered Index Seek Clustered Index Seek
4 Clustered Index Seek Clustered Index Seek
2 Clustered Index Seek Clustered Index Seek

AS SP
Parent PhysicalOp LogicalOp
0 NULL NULL
1 Sort Sort
2 Parallelism Gather Streams
3 Filter Filter
4 Hash Match Right Outer Join
5 Parallelism Repartition Streams
6 Index Scan Index Scan
5 Hash Match Inner Join
8 Bitmap Bitmap Create
9 Parallelism Repartition Streams
10 Hash Match Inner Join
11 Parallelism Repartition Streams
12 Clustered Index Scan Clustered Index Scan
11 Hash Match Inner Join
14 Parallelism Repartition Streams
15 Clustered Index Scan Clustered Index Scan
14 Parallelism Repartition Streams
17 Hash Match Inner Join
18 Compute Scalar Compute Scalar
19 Parallelism Repartition Streams
20 Clustered Index Scan Clustered Index Scan
18 Parallelism Repartition Streams
25 Index Scan Index Scan
8 Hash Match Inner Join
30 Bitmap Bitmap Create
31 Parallelism Repartition Streams
32 Index Scan Index Scan
30 Compute Scalar Compute Scalar
36 Parallelism Repartition Streams
37 Clustered Index Scan Clustered Index Scan


I have updated the statistics and added in a second variable inside the SP advice i have used in passed for parameter sniffing. Anyone come across this before and can anyone help shed some light on what is happening.


Cheers


*************************************************************

The first is always the hardest
Post #1389388
Posted Tuesday, November 27, 2012 1:18 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 @ 4:45 AM
Points: 40,172, Visits: 36,567
Can you post the graphical plans please?


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 #1389393
Posted Tuesday, November 27, 2012 1:40 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 8, 2014 2:40 PM
Points: 271, Visits: 1,020
HI Gail,




here you go thank you for you help, am tearing my hair out to try understand this :)


*************************************************************

The first is always the hardest


  Post Attachments 
SP_PLAN.sqlplan (14 views, 89.87 KB)
SELECT_PLAN.sqlplan (9 views, 44.06 KB)
Post #1389402
Posted Tuesday, November 27, 2012 11:46 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 6, 2014 7:17 AM
Points: 1,379, Visits: 2,690
Im no Guru but I think this could be Bad Parameter Sniffing

http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/

Post #1389520
Posted Wednesday, November 28, 2012 12:24 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
Hmm,

There is a slight difference in the code , the 'fast' version includes the extra clause @pJobId > 0
The optimizer is probably promoting this to the JobWorkJob.JobOID which give a differenting plan.

FROM
JobStay
JOIN UDStay ON JobStay.JobOID = UDStay.StayOID
JOIN Job ON JobStay.JobOID = Job.JobOID
JOIN Item ON JobStay.ItemOID = Item.ItemOID
LEFT JOIN JobWorkJob ON JobWorkJob.ForJobOID = JobStay.JobOID
LEFT JOIN JOB WJ ON WJ.JobOID = JobWorkJob.JobOID
JOIN Commodity ON Commodity.CommodityOID = Item.CommodityOID
JOIN UDCommodity ON UDCommodity.CommodityOID = Commodity.CommodityOID




WHERE
@pJobOID > 0
AND

JobWorkJob.JobOID = @pJobOID


However.....

Is this really what you want to do ?
You are left joining to JobWorkJob and then filtering the result!
Should that be an inner join ?
Or an outer join ON ID and ID and JobOID = @pJobOID




Clear Sky SQL
My Blog
Kent user group
Post #1389535
Posted Wednesday, November 28, 2012 12:36 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 8, 2014 2:40 PM
Points: 271, Visits: 1,020
HI,

thanks for your reply but i took that extra parameter @joboid>0 out of the SP as its was a usless parameter not sure what the reason behind the person who wrote it putting it in.


*************************************************************

The first is always the hardest
Post #1389543
Posted Wednesday, November 28, 2012 1:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
Maybe useless to you :) but the optimizer is chosing to do something different because of it.

What about the outer join ? Is that what is intended ?




Clear Sky SQL
My Blog
Kent user group
Post #1389556
Posted Wednesday, November 28, 2012 2:24 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 8, 2014 2:40 PM
Points: 271, Visits: 1,020
I have changed the joins now to inner joins as i realize the where clause negates on of the left joins and the other can also be inner join, not sure why they wrote this like it does i have a few more things to try with the parameter sniffing will give them a go :)


*************************************************************

The first is always the hardest
Post #1389587
Posted Wednesday, November 28, 2012 3:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
So after you change it to an inner join , are you still getting different plans ?





Clear Sky SQL
My Blog
Kent user group
Post #1389609
Posted Wednesday, November 28, 2012 3:23 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 8, 2014 2:40 PM
Points: 271, Visits: 1,020
Yup i still have the same issue

if i run the SP like

Exec SP_NAME parameter_value with recompile

the SP run fine and uses the good execution plan

if i run it with

Exec SP_NAME parameter_value

it uses the poor execution plan

If i add the option to recompile within the SP and execute

Exec SP_NAME parameter_value

then i still get the poor execution plan

Done a few changes to the SP to use local variables to try stop any parameter sniffing, no joy, tired the option (optimize for Unknown ) and still no joy


*************************************************************

The first is always the hardest
Post #1389613
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse