Should my database calls be Asynchronous?

  • Article

Update 28 November 2012: The combination of await, async, and the Task object makes it much easier for you to write asynchronous code in .NET 4.5.  Now that EF 6 is supporting Async Query and Save, you should take advantage of asynchronous programming. (As of this update, EF6 is not released so don’t use it just yet for production code).  When this blog was first written, it was much more difficult to wire asynchronous code. See my two async tutorials:

Long running/expensive database calls might seem like a natural candidate for asynchronous calls. In my MSDN article, Using an Asynchronous Controller in ASP.NET MVC, I go out of my way to avoid suggesting DB calls are good candidates.  Conventional wisdom (AKA text-book thinking) suggests expensive DB calls should be made asynchronously - but I assert they should almost never be.  Performing an async operation is vastly more complicated than performing a sync operation.  If you want to see something truly frightening, compare the stack traces of a synchronous MVC action with an asynchronous MVC action. The major exception to this is when you have multiple DB calls to make in parallel, in that case aSync can be the clear winner.

The following is a partial list of links by well established ASP.NET Gurus suggesting async is the way to go for expensive DB calls. (Most of the links are excellent reads  except for the bad advise about making DB calls async).

Suppose you have the following system:

  • A web application running on IIS 7 /.NET Framework v3.5 SP1 using the default request gating (maxConcurrentRequestsPerCPU="12")
  • Your server is a dual proc (two processors).
  • You are not using asynchronous methods.
  • Your web application contains a mix of static content, quick running dynamic pages, and a few very slow pages that hit the DB. (The DB is the bottleneck on these slow pages.)

With the above configuration, you are limited to 24 concurrent requests. While your web server can easily handle the average work load, under bursty conditions, you end up with 30 concurrent calls to the slow DB pages. Once the slow DB pages tie up the 24 threads (which are not doing useful work, they are just waiting for the DB to respond), all new requests are queued and your customers are waiting, even though your CPU is free. This may sound like the perfect time to use asynchronous calls, but there are a couple of problems. The maxConcurrentRequestsPerCPU setting does request gating, so converting the slow DB calls to async won’t help (without changing this setting). If you do convert these slow methods to async, you should follow the IIS/ASP.NET performance guru Thomas Marquardt’s advice and set maxConcurrentRequestsPerCPU = "5000" and maxConcurrentThreadsPerCPU="0".

Rather than convert your blocking DB calls to asynchronous, you can use thread gating by setting maxConcurrentThreadsPerCPU=30. Now when this same app is hit by 30 concurrent slow DB requests, it will have 30 threads free to serve up the cheaper requests. It’s a lot easier to change this setting than to convert your working DB code to use asynchronous approaches.

The IIS thread pool can often handle many more simultaneous blocking requests than a database server. If the database is the bottleneck, asynchronous calls will not speed up the database response. Without a throttling mechanism, efficiently dispatching more work to an overwhelmed database server by using asynchronous calls merely shifts more of the burden to the database. If your DB is the bottleneck, asynchronous calls won’t be the magic bullet. You need to add spindles or make your queries more efficient.

One respected DB/Web architect went so far as to say:
For database applications using async operations to reduce the number of blocked threads on the web server is almost always a complete waste of time. A small web server can easily handle way more simultaneous blocking requests than your database back-end can process concurrently. Instead make sure your service calls are cheap at the database, and limit the number of concurrently executing requests to a number that you have tested to work correctly and maximize overall transaction throughput. 

Required reading:

If you observe that the “ASP.NET ApplicationsRequests in Application Queue” performance counter is non-zero, you definitely have a performance problem. (From Thomas’s Blog).