SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Inconsistent query results when parallel plan is produced


Inconsistent query results when parallel plan is produced

Author
Message
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94308 Visits: 15023
I have come up against an interesting situation as I'm trying to optimize/re-write a query from a third-party application that doesn't finish. I have a query that finishes, but when it has a parallel plan the results are not the same each time. When I force a serial plan, I get the same results every time. I found this connect item for 2008R2 that says a fix was made for this problem, but I am definitely seeing it.

So far I have seen this on 2012 SP2 (11.0.5058.0) and on another copy of the same database on 2014 SP2 (12.0.5207.0).

I will try to setup a repro script (I'm going to try the one on the connect item) when I have time, but in lieu of that I'm attaching the Serial and Parallel execution plans (Anonymized by Plan Explorer)



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Attachments
MaxDop0_Anonymized.sqlplan (16 views, 97.00 KB)
MaxDop1_Anonymized.sqlplan (14 views, 63.00 KB)
Thom A
Thom A
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41836 Visits: 14432
What do you mean by the results are not the same? Could you be more specific? Provide sample outputs for one and the other and why they differ?


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69902 Visits: 8846
1) I too would like to see some of the data differences delineated, along with some rows that are identical. Please share any patterns you note in the results.

2) Is is possible that your joins are not 100% sufficient?

3) It is quite possible that the fix did NOT actually make it into the higher releases as ON by default. Did you try TF 4134 on both builds you are testing on?

4) Got the actual execution plans by chance?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94308 Visits: 15023
Okay, I didn't delve deeply into what was different because the row counts returned where in the 300K range. Basically the difference is that when run in serial the query would return let's say 350621 rows every time. When run in parallel the results would vary every run within say 5000 rows of the serial run.

I can get the actual execution plans, but I don't when I'll do that since I'm not at work anymore.

There's an update, the query begins with 2 CTE's and a co-worker who I sent the query to, converted those to inserts into temp tables and the inconsistent results for parallel plans went away, but the results returned were not the same as the results (row count) returned by the serial plan with the CTE's. I haven't had the time to completely compare the queries to see if the difference in results is due to a change in the query. I can say that the plan with the temp tables (I'll attach later) only parallelized the query that is the second CTE, the first CTE when changed to an insert into at temp table stayed serial and the final results query was serial as well, which I think explains why the issue with inconsistent results returned in parallel queries was eliminated.

I do plan to try TF 4134 but haven't had the chance yet. Hugo Kornelis recommended that on twitter.

Thanks, have a great weekend.

Kevin, how are things going, it's been awhile.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69902 Visits: 8846
This is quite an unusual situation, and I wonder if you haven't stumbled upon a bug (possibly known?). I hope the TF 4134 does you right, which would mean the bug WAS fixed - it just wasn't generally released due to the query plan regressions the fix caused. That actually sounds probable. But I was DEFINITELY disappointed to see that TF4134 was NOT bundled under 4199. Perhaps because it wasn't a true "performance fix", but still..


As for me, I have had a horrifically bad year from a medical standpoint. I have lost 60-70% of potential billable hours since May when I had a surgery that went south. Quality of life is still awful, I still have to take percocet and do other things to help mitigate the pain and discomfort, and a specialist said a month ago "there's nothing we can do right now. but the pain and other effects usually resolve themselves over time". If/when that happens I may well have to go under the knife again to address the original issue that actually didn't get fixed. Sad

Other than all that, and the significant emotional and psychological issues that go with it, things are actually pretty decent. Thankfully my long-term standing clients have stuck by me. Family support has been great too.

That which does not kill us makes us stronger, right?!?

I hope things are better in your world?!?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
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