or
PeterVandivier
sql-server sql-server-2017
Using `OFFSET ... NEXT` over a non-unique sort column results in predictable & repeatable variance under different execution context in the presence of of parallelism. Why does this behavior occur and is there a productive inference that can be made about the optimizer based on this behavior?

#### Some Background

I received a support ticket saying something like...

> I'm auditing some financial statements for `$time_period` and getting inconsistent results. When I execute `$stored_procedure`, I get $100; but when I run the `$ad-hoc-code` found _inside_ the SP with the same parameters, I get $200. _Why is SQL wrong?!_ 

The underlying proc was paginating results by sorting on a non-unique column. Job done right? I closed the ticket as "_Don't sort on a non-unique column_".

#### But why was it repeatable?

In reproducing & minifying the behavior, I was able to consistently induce... 

* a `ScanDirection="FORWARD"` for a **STORED-PROC EXECUTION** ([execution plan][ptp-STORED_PROC]) and
* a `ScanDirection="BACKWARD"` for an **AD-HOC F5** ([execution plan][ptp-AD_HOC_F5]). 

...given the following pre-requisites...

* Non-unique clustered index
* With a non-clustered index
* In a parallel plan
* With sufficient padding data 
  * ~60k rows is the smallest dataset I've minified to as yet
      * give me some credit I was starting vs ~200 million rows

...which causes reproducible variance give the repro dataset. 

These are the most noticable variances in [the diff of the SQLPlans][sqlplan-diff], although there are others (but I'm gonna feel real bad if this is somehow parameter sniffing & I just don't know how to read plans).

The fact that I can reproducably cause this specific change in the execution plan at this level is a little disorienting. To repeat from the **Summary**...

> Why does this behavior occur and is there a productive inference that can be made about the optimizer based on this behavior?


## [Reproduction][gist-repro]

> TODO: fix [fiddle][fiddle-repro] batching (55k more to go 😬)
> 
> TODO: plain-language describe stats histogram of psuedononimized data

very long fiddle here: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=4726813c2d4d6c23fd5d7a50756de4b6&hide=2

## Other Trivia
	
1. "_In the wild_", column `[xx]` was a `datetime2(7)` column (presumably some enterprising dev thought clustering in this was was equivalent to uniqueness). I modified it to `int` for readability while minifying the repro
2. This question has sat as a stub for about a year as of posting. If you care for the deep cut, start [here][1]

[ptp-STORED_PROC]: 
https://www.brentozar.com/pastetheplan/?id=r15VllyRB
[ptp-AD_HOC_F5]: https://www.brentozar.com/pastetheplan/?id=SJcS-lJAB
[gist-repro]: https://gist.github.com/petervandivier/a5497e7a1e8e52635499f10996c9355a
[1]: https://github.com/petervandivier/hello-world/commit/8f3143efff657dfe167493e137105c55b7cbf90a
[fiddle-repro]: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=49b7f1c1d154473b7dd42c73c6b07f0d
[sqlplan-diff]: https://gist.githubusercontent.com/petervandivier/a5497e7a1e8e52635499f10996c9355a/raw/5496f4fee31118b518382b1f48e194712eaa24c2/sqlplan.diff
Top Answer
Josh Darnell
In this specific case, the difference between the two plans can be explained by a *poor estimate* caused by local variables.  Kendra Little talks about this here: [Why Local Variables are Bad for Tuning Stored Procedures](https://www.brentozar.com/archive/2014/06/tuning-stored-procedures-local-variables-problems/)

> Local variables effectively “anonymize” the value being passed in on a parameter in SQL Server. It’s like you’re using Optimize for Unknown all the time.

The stored procedure version shows a 1-row estimate on the (forward) clustered index seek:

![sp-run.png](/image?hash=0d39a2e876c0a5cf6adadbb9131cfbfb8b742afdb038a90acd78da353f02f92c)

The optimizer is okay with "sorting" these results, because there will be so few, and letting the `TOP` operator do the rest.

The local variable version shows a 100-row *guess* on the (backward) clustered index seek:

![local-var-run.png](/image?hash=47d18ddec7e8cd11b50ae769ea4fb0f836b60360acbbd128ce97669b1339f438)

Because the potential number of rows is higher, the optimizer decides to read the index backwards rather than sorting.

You can get the forward scan plan with the ad-hoc query by adding `OPTION (RECOMPILE)` or using the literal values in the query.

---

As you noted in your question, the non-deterministic results are because of the lack of a unique sort column to be used for paging, and the different access patterns in the different plans.  In this case, the second plan isn't parallel at all really, and accesses the clustered index backwards from the first plan, which explains why you might see different results.

---

As a funny side note, the odd no-op parallel branch in the second plan is due to a `FILTER` operator that was present in the plan at some point during optimization, but was later pushed into the seek as a residual predicate.  You can see the parallel `FILTER` by enabling trace flag 9130 to prevent the predicate push from happening:

![filter-run.png](/image?hash=7d7797b7526203ad69247a0f2d24fc7b8ec1eae24b601d6ba8e76130ab537140)
Non-determinisitic sort & repeatable behavior in parallel plans
Josh Darnell
(without the row goal)
Josh Darnell
Running a 3rd time got me to the serial plan with the backwards scan.  36k-ish rows, 381.18 estimated rows out of the seek.
PeterVandivier
learning so many things today 🤷‍♂️
PeterVandivier
booo... and the "raw file" link for batches.sql is... idk timing out but not really? it's not rendering in any case
Josh Darnell
I ran the bcp of those 12k rows again (24k total rows, 254 estimated rows out of the seek), and still get the parallel plan with the variables.
PeterVandivier
oh snap, yea
PeterVandivier replying to Josh Darnell
also, wait wat?
PeterVandivier
cool, cool. i'll assume ~10k is a good volume and start there :)
Josh Darnell
If I just insert the first 12k rows from your sample dataset (which is all that made it into the gist), I get essentially the same plan for both queries.
Josh Darnell replying to PeterVandivier
I think it's all about the estimated number of rows coming into the sort.
PeterVandivier
so i wanna try to find the "tipping point" that kicks the optimizer in the nards and causes the "problem"
PeterVandivier
obviously procedurally generating the rows will let me test in size faster, but i do remember that somewhere in the 10-1000 row range both executions got the same plan
PeterVandivier
do you have any theories on what i might be able to cut out to minify the example futher? iirc, i really had pared it down to "if i drop one more column the repro breaks" and ... "60k appears to be (in an order of magnitude) the fewest rows i can do this with"
PeterVandivier
hmm... so i think next steps on cleaning up the question are still the same - need to procedurally generate the data for the fiddle to make it more portable and regen the actually "a/b" demo
PeterVandivier
yuuuuuuuup(ercube)
Josh Darnell replying to PeterVandivier
That dude knows...so much stuff.
PeterVandivier
i found a bug in the postgres odbc driver on monday and then yesterday ypercube pointed out the section of the documentation that calls out some (fairly obvious) compilation flag for handling that
Josh Darnell
"Oh, this parameter is milliseconds, not seconds"
PeterVandivier
ha
Josh Darnell
"I've found a bug in the .NET Framework!"
Josh Darnell replying to PeterVandivier
Yeah, I'm often afraid of the same thing when I think I've found something really interesting haha.
PeterVandivier
of course feel free to do your own edits as well, but i don't want to put the burden on you if you don't want to
PeterVandivier
i was afraid the answer might be mundane despite the amount of effort i put into paring it down - but it might be saveable with the a/b example repro as a demo of why proc=A, in-line=B with the proper edits to the framing of the question
Josh Darnell replying to PeterVandivier
Okay, I just finished writing up the answer.
PeterVandivier
(sry, only just asked for STMGR access)
PeterVandivier
@Josh - if you want to just "rough draft" your local vars answer to this, i'd be happy to work on an edit to the post that makes it a better/more general question
PeterVandivier replying to Jack Douglas
i'd be curious to see how you handle the UI for an "import csv" feature 🤔(if and when of course)
PeterVandivier replying to Josh Darnell
ty :) 🙌
Josh Darnell
I know I've seen that weird "distribute streams" -> "gather streams" before, but I can't remember what it means.
Josh Darnell
Oh also you had the forward and backward cases mixed up in the question text compared to your repro, so I changed that as well.
Josh Darnell
@PeterVandivier I made an edit to include the paste the plan links, as I think they lower the barrier for potential answerers.  Really interesting question!
PeterVandivier
i've self-deleted dba.se questions before after deciding they weren't up to standards and then edited and undeleted a few days later. i like that experience of being able to hide a post that i feel is sub-par but saveable (even if it's not what that functionality is _meant_ for)
PeterVandivier
new feature request though - "draft" posts so i don't keep bumping this to the top every time i edit it 😬
PeterVandivier replying to Jack Douglas
lol, true 'nuff
PeterVandivier replying to Jack Douglas
i think the actual repro may be necessary to make this a compelling question. the demo of repeatably and consistently getting result A when you execute some code that repeatably and consistently becomes B when you put that same code in a proc is what grabbed me 
Jack Douglas replying to PeterVandivier
I don't think you are going to get db<>fiddle up to 60k raw inserts — but in any event it would be better to link to the fiddle here rather than embedding it. No-one is going to want to scroll past 60k lines to get to the 'answer question' button
PeterVandivier replying to Jack Douglas
figuring out what the proportions / distributions of the data were to generate it procedurally was where i gave up last year iirc. it's almost certainly doable; and i rather suspect if i do the legwork to grok that then i'll be a ways closer to answering my own question
Jack Douglas
also the underlying question "But why was it repeatable" strikes me as being a bit irrelevant. Why does it matter? "Don't rely on non-deterministic sort order. Ever." seems to be the only sane answer.
Jack Douglas
@PeterVandivier is it possible to get a repro with *generated* data rather 60k+ inserts? Something along these lines: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=ee600488b9671e3b30253387f4f0b2b4
PeterVandivier
feels pretty niche, maybe that's why i sat on it for a year
PeterVandivier
i figured actually demonstrating the repeatable variance of exec = $100 and in-line = $200 would make the question more impactful, but is the meat of this sufficiently demonstrated by the fact that the in-line & exec repeatably and consistently choose the different scan directions? 🤔
PeterVandivier
crud... also, at some point in the last year i forgot what i did to demonstrate the actual variant output that prompted the initial question
PeterVandivier
Still a draft atm, see "_Embedded fiddles should respect &hide= args_" on https://topanswers.xyz/databases?q=30