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?
From this article SQL Server 2008 Books Online (October 2009) - Performing Asynchronous Operations, I quote:
Asynchronous processing enables methods to return immediately without blocking on the calling thread. This allows much of the power and flexibility of multithreading, without requiring the developer to explicitly create threads or handle synchronization.
If you have explicitly created those threads and handled the synchronization, perhaps you probably will not find much difference.
If you mean "Why use a data providers BeginExecute___()/EndExecute____() methods vs just wrapping a normal Execute____() in a delegate, I suspect the answer is that when they were first designing the provider model for ado.net for .Net 1.0, wrapping something in a delegate and invoking it asynchronously wasn't so simple.
Other possible reasons are that this matched how the old code they were updated or wrapping for the original .Net worked or that the built-in methods allow you to easily check any returned exceptions
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.
.Net BeginInvoke simply postpones the execution into a different thread. This will be always slower than a synchronous call and consume extra resources. The only reason why this would be used would be to free the caller context to proceed to other operations (eg. return an the result of an HTTP request to the client).
SqlClient asynchronous methods, when the AsynchronousProcessing property on the connection is set to true and the BeginExecute methods of SqlCommand are used, are truly asynchronous. The SQL command is posted to the network communication channel and the completion is invoked when the result is returned by the server.
From a reliability point of view though neither of these methods is useful though. They both rely on the client process to stay around until the call is completed, otherwise the SQL Server would see the client disconnect and abandon processing, rolling back any intermediate work. Consider an ASP application that accepted an HTTP request, submitted an 'asynchronous' payment processing and returned an response. There isn't any way to guarantee that the submitted work will actually occur.
For situations when the processing requires reliability guarantees the solution is queue up the work on the server, commit it and then proceed, relying on SQL Server's own asynchronous processing capabilities. This is a method that guarantees the processing even on the presence of client disconnects, ASP process 'recycling', SQL Server mirroring or clustering failovers, hardware disaster recovery, pretty much anything you can throw at it, since is a transactionaly durable way of submitting asynchronous processing requests. For an example see Asynchronous Procedure Execution.
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).