6

Scenario

I run a long, complex query that returns 30+ fields and 77,000+ rows in SSMS. The results are to grid. The results are all text-heavy. The SSMS grid starts populating with results after a few seconds (3 - 4) seconds, but query execution time is 2 minutes. Most of those 2 minutes are the results appending into the grid in SSMS.

Question(s)

When was the query "done"? Was it done in the first 3 - 4 seconds as rows started appending to the SSMS grid? Is the rest of the 1 min 56 seconds just the grid UI updating with the results? If the query is "done" after the 3 - 4 seconds, then I shouldn't look to optimize it, correct? Conversely, if the query isn't done until execution stops in SSMS (the full two minutes), then I should be able to look into optimizing it?

1

2 Answers 2

17

It's done when the client receives the last query result packet from the server.

If the client is slow consuming the results then this will delay the overall query execution and it will be suspended waiting on ASYNC_NETWORK_IO.

The query doesn't run to completion and get the results buffered somewhere and only then send them. So certainly seeing some results after 3-4 seconds is no indication that the query is "Done".

e.g. I ran the below in SSMS with both "Live Query Statistics" and "Actual Execution Plan" enabled.

DECLARE @X VARCHAR(MAX) = REPLICATE('X',8000);

SET @X = REPLICATE(@X,125)

SELECT TOP (20000)  @X
FROM master.dbo.spt_values v1, master.dbo.spt_values v2
OPTION (RECOMPILE) /*Not sure why but SSMS doesn't show the live plan for me in this case without this*/

Results started appearing in the grid more or less straight away.

When it completed the Query Time Stats in the actual execution plan showed an elapsed time of 80,668 ms with the wait stats in the same plan showing 60,184 ms of ASYNC_NETWORK_IO waits

Viewing the live execution plan whilst it was running shows that rows are still flowing from the execution plan operators right up until the end (e.g. below is how it looked after a minute for me by which time it had returned 16,393 rows)

enter image description here

If the above query is running at an isolation level requiring it to hold locks until the end of the statement then this will mean that locks are held open longer due to the slow client.


The above query has no "blocking" operators so in the above case rows start streaming to the client more or less straight away.

For more about this you can see a related but inverse question about "When do the query results start"

0
12

One other good thing to note (since you're asking this in regards to performance tuning) is you can discard the results of a query in SSMS. Doing so will help you more precisely see what the runtime is of a query excluding the consumer / client's rendering time, etc. I usually turn this on when I'm more interested in the runtime of the execution of the query itself:

SSMS - Discard Query Results

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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