Different execution plan between SQL and SP

  • 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

    ParentPhysicalOpLogicalOp

    0NULLNULL

    1Nested LoopsInner Join

    2Compute ScalarCompute Scalar

    3Nested LoopsInner Join

    4Nested LoopsInner Join

    5Nested LoopsInner Join

    6Compute ScalarCompute Scalar

    7Nested LoopsInner Join

    8Nested LoopsInner Join

    9Clustered Index SeekClustered Index Seek

    9FilterFilter

    11Clustered Index SeekClustered Index Seek

    8Clustered Index SeekClustered Index Seek

    6Clustered Index SeekClustered Index Seek

    5Clustered Index SeekClustered Index Seek

    4Clustered Index SeekClustered Index Seek

    2Clustered Index SeekClustered Index Seek

    AS SP

    ParentPhysicalOpLogicalOp

    0NULLNULL

    1SortSort

    2ParallelismGather Streams

    3FilterFilter

    4Hash MatchRight Outer Join

    5ParallelismRepartition Streams

    6Index ScanIndex Scan

    5Hash MatchInner Join

    8BitmapBitmap Create

    9ParallelismRepartition Streams

    10Hash MatchInner Join

    11ParallelismRepartition Streams

    12Clustered Index ScanClustered Index Scan

    11Hash MatchInner Join

    14ParallelismRepartition Streams

    15Clustered Index ScanClustered Index Scan

    14ParallelismRepartition Streams

    17Hash MatchInner Join

    18Compute ScalarCompute Scalar

    19ParallelismRepartition Streams

    20Clustered Index ScanClustered Index Scan

    18ParallelismRepartition Streams

    25Index ScanIndex Scan

    8Hash MatchInner Join

    30BitmapBitmap Create

    31ParallelismRepartition Streams

    32Index ScanIndex Scan

    30Compute ScalarCompute Scalar

    36ParallelismRepartition Streams

    37Clustered Index ScanClustered 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 step is always the hardest *******

  • 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
  • HI Gail,

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

    ***The first step is always the hardest *******

  • Im no Guru but I think this could be Bad Parameter Sniffing

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

  • 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[/url]

  • 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 step is always the hardest *******

  • 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[/url]

  • 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 step is always the hardest *******

  • So after you change it to an inner join , are you still getting different plans ?



    Clear Sky SQL
    My Blog[/url]

  • 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 🙁

    ***The first step is always the hardest *******

  • Can you post both of the new plans ?



    Clear Sky SQL
    My Blog[/url]

  • Its ok mate thank you for your help, its now working with the better execution plan.

    I re updated statistics a second time and this as appeared to Fix the issue 🙂 why i have no idea but its working fine Now 🙂 thanks for your help it may crop its ugly head again 🙂

    ***The first step is always the hardest *******

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply