Timeline for Why does Sql Server makes this bad query plan choice?
Current License: CC BY-SA 3.0
10 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 22, 2016 at 10:47 | comment | added | Martin Smith |
I guess that explains it then - presumably the 334,348 executions plan has a higher cost and so isn't selected. You're basically doing a NOT EXISTS query. I'm not sure of the exact gory details about how it calculates the expected probability of a row existing or not existing in a second table. AFAIK generally it errs towards expecting it to exist though ("the containment assumption")
|
|
| Aug 22, 2016 at 10:42 | comment | added | John | @MartinSmith I put the xml of the bad plan up. Interestingly, the estimated number of executions for the inner side is 334348, but the actual ones are only 4 (with a top 1). The estimate is a bit more than half the total rows. For some reason Sql Server thinks it will have to look at half the articles before it finds 10 that doesn't have one. | |
| Aug 22, 2016 at 10:35 | history | edited | John | CC BY-SA 3.0 |
deleted 166 characters in body
|
| Aug 22, 2016 at 10:28 | comment | added | paparazzo | It is not clear what the problem query is from your question. If looks the the changes are in the query. | |
| Aug 22, 2016 at 10:20 | history | edited | John | CC BY-SA 3.0 |
added 284 characters in body
|
| Aug 22, 2016 at 10:18 | answer | added | paparazzo | timeline score: 1 | |
| Aug 22, 2016 at 10:15 | comment | added | Martin Smith | Are you able to share the problematic plan (as XML)? - Also might be worth looking at the XML and verifying that no plan guides have been applied. Another question - when you do force the nested loops plan what is the estimated number of executions for the inner side? | |
| Aug 22, 2016 at 10:08 | comment | added | John |
Literal 10 (from SSMS), TOP 1 has the same plan, trace flag tested and it being on or off makes no difference to the plan.
|
|
| Aug 22, 2016 at 10:04 | comment | added | Martin Smith |
Does the problem plan have a literal 10 or is it parameterized? If you try TOP 1 or TOP 2 do you get the plan that you would expect? Do you have trace flag 4138 enabled?
|
|
| Aug 22, 2016 at 9:56 | history | asked | John | CC BY-SA 3.0 |