September 15, 2017 at 12:28 pm
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
Consultant - Straight Path Solutions
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
September 15, 2017 at 3:38 pm
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
September 15, 2017 at 4:31 pm
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
Consultant - Straight Path Solutions
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
September 15, 2017 at 6:02 pm
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. 🙁
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply