Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Different execution plan between SQL and SP


Different execution plan between SQL and SP

Author
Message
SGT_squeequal
SGT_squeequal
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 1047
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 hardestw00t
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47181 Visits: 44356
Can you post the graphical plans please?


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


SGT_squeequal
SGT_squeequal
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 1047
HI Gail,




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

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

The first is always the hardestw00t
Attachments
SP_PLAN.sqlplan (14 views, 89.00 KB)
SELECT_PLAN.sqlplan (9 views, 44.00 KB)
SQLSACT
SQLSACT
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2931
Im no Guru but I think this could be Bad Parameter Sniffing

http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
Dave Ballantyne
Dave Ballantyne
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1818 Visits: 8370
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
SGT_squeequal
SGT_squeequal
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 1047
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 hardestw00t
Dave Ballantyne
Dave Ballantyne
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1818 Visits: 8370
Maybe useless to you Smile 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
SGT_squeequal
SGT_squeequal
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 1047
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 Smile

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

The first is always the hardestw00t
Dave Ballantyne
Dave Ballantyne
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1818 Visits: 8370
So after you change it to an inner join , are you still getting different plans ?



Clear Sky SQL
My Blog
SGT_squeequal
SGT_squeequal
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 1047
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 w00t

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 Sad

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

The first is always the hardestw00t
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