gotchasqlMajor
Why does SQL Server return some rows while still executing the query, and sometimes not?
Viewed 0 times
sometimesrowswhyexecutingwhilethesqlreturnqueryand
Problem
There are queries where when we hit "execute", it shows some rows and it keeps growing, but the query is not over yet. Yet sometimes, it waits until the end of the query.
Why does this happen? Is there a way to control this?
Why does this happen? Is there a way to control this?
Solution
The answer, as usual (alright, most of the time), lies in the execution plan.
There are certain operators that require all rows to arrive at them before they can start processing those rows, and passing them downstream, for example:
They're either called blocking, or stop and go operators because of this, and they're often chosen when the optimizer thinks it'll have to process a whole lot of data to find your data.
There are other operators that are able to begin streaming, or passing any found rows along immediately
When queries start returning data immediately, but don't finish immediately, it's usually a sign that the the optimizer chose a plan to locate and return some rows quickly using operators that have a lower start up cost.
This can happen because of row goals introduced either by you, or by the optimizer.
It can also happen if a bad plan is chosen for some reason (lack of SARGability, parameter sniffing, insufficient statistics, etc.), but that takes more digging to figure out.
For more information, check out Rob Farley's blog here
And Paul White's series on row goals here, here, here, and here.
It should also be noted that, if you're talking about SSMS, rows only appear once an entire buffer has been filled, not just willy-nilly.
There are certain operators that require all rows to arrive at them before they can start processing those rows, and passing them downstream, for example:
- Hash Join (on building the hash table)
- Hash Match
- Sort (Except Hash Flow Distinct)
They're either called blocking, or stop and go operators because of this, and they're often chosen when the optimizer thinks it'll have to process a whole lot of data to find your data.
There are other operators that are able to begin streaming, or passing any found rows along immediately
- Nested Loops
- Index supported Merge Joins
- Stream Aggregates
When queries start returning data immediately, but don't finish immediately, it's usually a sign that the the optimizer chose a plan to locate and return some rows quickly using operators that have a lower start up cost.
This can happen because of row goals introduced either by you, or by the optimizer.
It can also happen if a bad plan is chosen for some reason (lack of SARGability, parameter sniffing, insufficient statistics, etc.), but that takes more digging to figure out.
For more information, check out Rob Farley's blog here
And Paul White's series on row goals here, here, here, and here.
It should also be noted that, if you're talking about SSMS, rows only appear once an entire buffer has been filled, not just willy-nilly.
Context
StackExchange Database Administrators Q#203877, answer score: 47
Revisions (0)
No revisions yet.