Asynchronous processing in SQL Server vs. .NET Asynchronous processing

Question

What's is the advantage of using Asynchronous processing in SQL Server over .NET Asynchronous processing? Aren't they the same? I have a hard time to understand what is the benefit of using Asynchronous processing in SQL Server instead of .NET APM. I can easily wrap a SQL call in a lambda expression and do a BeginInvoke(...).

Can someone help me the difference and the benefit of both?

Answer 1

The problem with .NET asynchronous processing (BeginInvoke(...)) is that all this is doing is spinning off a thread to process the code synchronously. A 5 minute query will tie up a thread for 5 minutes, blocking (i.e. doing nothing for ~99% of the time) while a result is calculated at the remote end. Under strain (many queries at once) this will exhaust the threadpool, tying up all threads in a blocked state. The threadpool will become unresponsive and new work requests will suffer big latency waiting for the threadpool to fire up extra threads. This is not the intended use of the threadpool, as it is designed with the expectation that the tasks it is asked to complete are to be short-lived and non-blocking.

With Begin/EndAction APM pairs, one can invoke the same action in a non-blocking way, and it is only when the result is returned via an IO completion port that it is queued as a work item in the threadpool. None of your threads are tied up in the interim, and at the point that the queued response is dealt with, data is available meaning user code does not block on IO, and can be completed quickly... a much more efficient use of the threadpool which scales to many more client requests without the cost of a thread per outstanding operation.

Answer 2

As mentioned in the earlier answers, BeginInvoke uses a .NET thread. Equally important, though, is that thread comes from the ASP.NET thread pool, so it competes with clients for the very limited thread resources. The same is true for ThreadPool.QueueUserWorkItem().

The SqlClient async calls require a SqlConnection that has async=true enabled. That mode requires a little more network overhead (which is why it's not enabled by default), but it doesn't consume a thread from the .NET thread pool. Instead, it uses async I/O.

The advantage of the latter approach is that it's much more scalable. You can process hundreds or thousands of simultaneous requests that way, where the overhead with a thread-per-call would be extreme. Plus, you would consume the entire ASP.NET thread pool very quickly (it only has a maximum of around 12 threads by default).