Async Programming with ADO.Net

Some years ago Remus Rusanu has posted this great answer about ADO async programming: https://stackoverflow.com/a/9434687/23900248

In particular he wrote:

A word of caution: an async SQL command is completing as soon as the first result returns to the client, and info messages count as result. You've lost all benefits of async. The print creates a result that is sent back to the client, which completes the async command and execution on the client resumes and continues with the 'reader.Read()'. Now that will block until the complex query start producing results. You ask 'who puts print in the procedure?' but the print may be disguised in something else, perhaps something as innocent looking as an INSERT that executes without first issuing a SET NOCOUNT ON.

Now we are at .NET 8, and this situation seems to persist. Is there a workaround? What is the correct way to asynchronously call a stored procedure that produces many results, including messages that, for example, inform the client about the execution status?

In example:

declare @K int=0, @Progress varchar(255)
while @K<100
    begin

    set @K=@K+1

    select Flush=1
    set @Progress=concat('Executing ', @K, ' of 100')
    raiserror(@Progress, 9, 1) with nowait
    waitfor delay '00:00:01'
    end

marc.

UPDATE

Following the discussion with Panagiotis, I'm trying to reformulate my question. Consider the TSQL fragment mentioned above; we're not interested in whether this code is good or bad, nor are we concerned about whether it's a proper or improper use of SQL Server. The only fact I ask to focus on is that this code produces a flow of 100 result sets and 100 messages, alternated.

If it weren't for that waitfor, the code execution, transmission to the client, and client-side processing would be almost instantaneous. However, that waitfor is there and causes the processing of that stored procedure to last about 100 seconds. Almost all of this time consists of waiting for the server.

On paper, this is the ideal situation to apply async/await logic. Putting it in simplified terms: "No new thread is needed, with all its complications, we use the waiting time to do something else and not block the flow and therefore, ultimately, the interface."

And indeed, this happens if the messages are removed; ADO.Net performs that call asynchronously. But if there are messages, the mechanism gets jammed. Why?

Theoretically, datasets and messages are essentially the same thing, two chunks of data in the Pipe flowing from the server to the client. Why does a message disrupt ADO.Net?

Remus writes:

The print creates a result that is sent back to the client, which completes the async command, and execution on the client resumes and continues with the 'reader.Read()'. Now that will block until the complex query starts producing results.

And Panagiotis reiterates:

Remus Rusanu warns against bad stored procedure coding, not about issues in asynchronous programming.

However, I don't understand why the arrival of the message demands marking the command as completed. The only reason that comes to my mind is that messages, being handled for historical reasons as events, break the async/await chain.

Why does ADO lock up? And more importantly, is there a purely async/await way to prevent it from locking up? Running the asynchronous call within Task.Run() solves the problem, but it seems if Task.Run is more efficient than the asynchronous method itself, it means that the asynchronous method is widely optimizable, right?

rimarc