Is there any real benefit of using async/await in an ASP.NET application if the only I/O calls are to a backend SQL Server database that is not using the ASYNC parameter in its connection string?
async is not going to help you in your situation (that is, with a single SQL Server instance). This is assuming that most (or all) I/O-bound ASP.NET requests hit the database.
As I stated in my intro to
async on ASP.NET article:
A decade ago, a common architecture was to have one ASP.NET Web server that talked to one SQL Server database back end. In that kind of simple architecture, usually the database server is the scalability bottleneck, not the Web server. Making your database calls asynchronous would probably not help; you could certainly use them to scale the Web server, but the database server will prevent the system as a whole from scaling.
In contrast, for Azure SQL (or even SQL Server clusters),
async on ASP.NET may be beneficial. For even more scalable backends (NoSQL or web APIs),
async is even more likely to be beneficial.