An adventure on SQL Server performance and features

Get faster performance and lower network usage in SQL Server Loops by avoiding the “DONE Token” overload

G

FYI: You can get the Notebook for this article on my github and experiment yourself (opens with Azure Data Studio).

Preamble

Everybody knows that using loops in SQL Server is not efficient, if you’re able to write that same logic in a set-based statement it’s guaranteed to be faster.
Still, devs can’t be helped, you just can’t seem to nail down the set-equivalent statement for the loop, or, simply, you have to write a loop because the set-statement is too complicated for other members of the team to maintain and you have to bind your query powers for a greater good.

The Source of the issue (The DONE Token)

So, unavoidable loops, how do we mitigate the issue?
Let’s see what’s going on in this simple loop:

DECLARE
    @loop_var int = 0,
    @a_string varchar(8000);


WHILE @loop_var < 5000000
BEGIN
    SET @a_string = 'A';
    SET @loop_var = @loop_var + 1;
END;
This batch on my Azure VM runs for about 2 minutes and basically does nothing.

Not everybody knows that SQL Server sends a DONE Token to the client each time that a SQL statement completes (so, everything except variable declarations); For the query above you can basically track it with extended events by tracking the “SQL Statement Completed” event.
What happens in a loop? For each statement that’s completed, a token is sent, which means that for this loop that contains 3 statements (the WHILE loop itself it’s a statement) 15 Million tokens are sent to the client.

Let’s verify this:

The extended event is set up to capture all the “SQL Statement Completed” events coming from Azure Data Studio e putting them into a histogram for counting them.

After running the query above, you’ll see the following, each statement has been executed 5 Million times (+1 for the loop constructor itself)

Extended Events show that the statement has been reported as completed 5M times, for each statement

By tracking the wait stats, we can confirm that indeed the query completion is held back by ASYNC_NETWORK_IO, which means that the client/network is struggling to keep pace with the rate of which DONE Tokens are sent; Since we’re not returning any data in the batch/loop, the only thing handled by the client are the Tokens.

Network plays a big part in this, if you try to execute the sample query above with both the server and client in the same machine you probably won’t see as much waits and the query will complete much faster. This is why ofted devs think that everything it’s ok when they’re testing the code locally but everything burns when deployed. #propertesting

How to fix it

Paul White has suggested a couple of ways to get around this:
NOCOUNT is a well known set option to avoid sending useless messages to the client, saving time and bandwidth, the BOL page states:

SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

However, setting NOCOUNT ON at the beginning of the batch above makes no difference, only DONEINPROC Tokens (as the name implies, these tokens are the same as the DONE Tokens, but for statements inside a SP) are suppressed, hence all the DONE tokens that are sent by the batch still come to the client slowing everything down, so, let’s try with a procedure:

CREATE PROCEDURE #P AS
BEGIN
    SET NOCOUNT ON;
    DECLARE
        @loop_var int = 0,
        @a_string varchar(8000);


    WHILE @loop_var < 5000000
    BEGIN
        SET @a_string = 'A';
        SET @loop_var = @loop_var + 1;
    END;
END
GO 

EXECUTE dbo.#P;
GO

The procedure above is defined as a Temporary Procedure, which behaves as a Temp Table, it will be dropped when session referring to it is closed, but nothing impedes you from creating a standard stored procedure

WAY Better.

The “SQL Statement Completed” XEvent now shows only two statements:

14999999 less events

Another way you can get around this is with my favourite thing to play with, dynamic SQL:

Works fine too.

Takeaway

It’s not ideal to have loops in SQL Server batches, however, if you have to, at least avoid overloading your client with “SQL Statement Completed”/”DONE” messages by using one of the techniques described above,

About the author

Emanuele Meazzo

1 comment

An adventure on SQL Server performance and features

Emanuele Meazzo

My Social Profiles

If you're interested in following me around in the social world, here are some URLs for you