31

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?

41

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:

  • 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.

14

If I understand what you're observing, this is how Management Studio renders rows, and has little to do with how SQL Server returns rows. In fact often when you are returning large results to SSMS and attempting to render them in a grid, SSMS can't keep up and SQL Server ends up waiting for the app to process more rows. In this case you'll see SQL Server accumulating ASYNC_NETWORK_IO waits.

You can control it somewhat by using Results to Text instead of Results to Grid, since SSMS can draw text faster than it can draw grids, but you'll likely find this can affect readability depending on the number of columns and the data types involved. Both are impacted by when SSMS decides to actually write results out to that pane, which depends on how full the output buffer is.

When you have multiple statements, and you want to force the buffer to render output results to the messages pane, you can use a little printing trick in between statements:

RAISERROR('', 0, 1) WITH NOWAIT;

But this won't help when you're trying to get SSMS to render rows more quickly when all the output is coming from a single statement.

More directly, you can control it by limiting how many results you are rendering in SSMS. I often see people complain about how long it takes to return a million rows to the grid. What on earth anyone is going to do with a million rows in an SSMS grid, I have no idea.

There are some hacks like OPTION (FAST 100), which will optimize for retrieving those first 100 rows (or any 100 rows if there is no outer ORDER BY), but this can come at the cost of much slower retrieval for the remainder of the rows and a plan that is more inefficient overall, so isn't really a go-to option IMHO.

1

Your question is not about SQLServer per se but:

  • SQLServer
  • network
  • SSMS as client application

Is there a way to control this?

Short answer:

  1. Try sqlcmd instead of ssms or sqlcmd-mode of ssms
  2. Check your connection and session settings

Long answer:

Of course! But not one - prob

  1. Execute your query with sqlcmd or in sqlcmd-mode in ssms.
  2. If you want exclude role of network - run your query on server with Shared Memory connection.
  3. If query performance is unsatisfiable even with Shared Memory connection - analyse your execution plans. If query performs bad through network - call your network network administrator for help. If your query performs bad only in SSMS - read further.
  4. Now we are sure that problems are on client side(ssms in this case). Look at connection and session settings in SSMS. Don't believe ssms interface and check with SQL Profiler: find your connection by spid and you get full list of session settings. Compare with settings of sqlcmd session. If nothing clicks - copy all session settings from profiler into your query script, execute in sqlcmd-mode and gradually switching settings you will find your culprit.

Good luck!

-2

To add to sp_BlitzErik's answer, take the example using a NOT IN () with a sub select. In order to determine whether an item is in the result of the nested query, it's (generally) necessary to retrieve the entire result.

So one way easy way I've found to improve performance of such queries is to rewrite them as an LEFT OUTER JOIN with where condition for RIGHT side is null (you could flip it around, of course but who uses RIGHT OUTER JOINS?). This allows for the results to start to return right away.

  • I don't think so. If the compared columns are not nullable, then the results should be the same and the plans - usually - the same, for the 3 versions of an antijoin (NOT IN, NOT EXISTS, LEFT JOIN / IS NULL). It's not necessary to retrieve the whole result. – ypercubeᵀᴹ Apr 13 '18 at 17:20
  • If the subselect is a really complex one that the produced query needs to evaluate the whole subselect before checking the NOT IN condition, WHERE t.x IN (<complex SELECT subquery>), the equivalent LEFT JOIN, LEFT JOIN (<complex SELECT subquery>) AS r ON r.x = t.x .... WHERE r.x IS NULL, then the subquery wil lhave to be evaluated, too (so same complex plan with the NOT IN version). – ypercubeᵀᴹ Apr 13 '18 at 17:24
  • @ypercubeᵀᴹ It's worked for me in the past. I've seen queries go from taking minutes to return to subsecond. – JimmyJames Apr 13 '18 at 18:02
  • @ypercubeᵀᴹ I put together a simple example in Oracle (sorry I don't have access to SQLServer at the moment) and they definitely had different explain plans. Perhaps they were not meaningful differences but they look pretty different. – JimmyJames Apr 13 '18 at 19:49
  • @JimmyJames: it's not a simple way if you want stable performance and such "optimizations" are very sensitive for SQLServer version. And don't make mistake appealing to Oracle (which version?). Historically SQLServer prefered NOT EXISTS but Oracle NOT IN in queries. But today it must be considered as error in plan generator – Alex Yu Apr 14 '18 at 12:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.