November 27, 2012 at 12:59 pm
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 *******
November 27, 2012 at 1:18 pm
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
November 27, 2012 at 1:40 pm
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 *******
November 27, 2012 at 11:46 pm
Im no Guru but I think this could be Bad Parameter Sniffing
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
November 28, 2012 at 12:24 am
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
November 28, 2012 at 12:36 am
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 *******
November 28, 2012 at 1:19 am
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 ?
November 28, 2012 at 2:24 am
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 *******
November 28, 2012 at 3:09 am
So after you change it to an inner join , are you still getting different plans ?
November 28, 2012 at 3:23 am
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 *******
November 28, 2012 at 3:32 am
Can you post both of the new plans ?
November 28, 2012 at 7:45 am
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