Bizarre duplicate query plan
-
Hi All,
Not sure if anyone has come accross something like this previously.
Got a simple select query which creates 2 different query plans even with hard coded values instead of parameters.
However this only happens when running through an API with Middleware but when we add a delay no issues.
Checked extended events to ensure the queries are being produced exactly the same.
Cleared the current plans from the cache (test environment)
Checked all indexes along with statistics and fragmentation.Any other ideas would be gratefully received .
- daniel.hughes 66721 - Tuesday, June 12, 2018 9:02 AM
Not based on what you have posted.
-
Hi,
I encountered this kind of issues due to mismatch between the SET properties in the workload. I guess you are getting two plans as you are running one through SQL instance and the other one from your API (please check SET ARITHABORT option is matching in both the cases or not). Just go to properties of the query plans and investigate the SET properties there.
Thanks,
Debasis - debasis.yours - Tuesday, June 12, 2018 9:08 AM
Interesting, something to put in a notebook should I encounter such an issue. Thanks for posting this.
- debasis.yours - Tuesday, June 12, 2018 9:08 AM
Thanks Debasis shall have a look 🙂
- dannydudek - Tuesday, June 12, 2018 9:02 AM
Can you post up both plans?
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
- dannydudek - Tuesday, June 12, 2018 9:22 AM
As .sqlplan attachments - the property sheets are important 😉
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden - dannydudek - Tuesday, June 12, 2018 9:02 AM
If you query the plans and use sys.dm_exec_plan_attributes, you can get the differences with set options as well as other properties. Pay attention to user_id as despite it's name it will indicate a plan is sharable or not. It works great for situations such as these:
sys.dm_exec_plan_attributesSue
- debasis.yours - Tuesday, June 12, 2018 9:08 AM
+100000! This is actually a very common problem and it's not just a problem with different query plans. A lot of devs don't understand how their stuff works with NULL = NULL through the API but the same query in SSMS doesn't work correctly.
Someone mark Debasis' post as the "correct" answer! 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply