if you have limited access to a resource, as in a DbConnection or a file, when do you stop using async methods in favor of synchronous?

You shouldn't need to switch to synchronous at all. Generally speaking, async only works if it's used all the way. Async-over-sync is an antipattern.

Consider the asynchronous code:

using (connection) { await connection.OpenAsync(); using(var reader = await command.ExecuteReaderAsync()) { while(await reader.ReadAsync()) { } } }

In this code, the connection is held open while the command is executed and the data is read. Anytime that the code is waiting on the database to respond, the calling thread is freed up to do other work.

Now consider the synchronous equivalent:

using (connection) { connection.Open(); using(var reader = command.ExecuteReader()) { while(reader.Read()) { } } }

In this code, the connection is held open while the command is executed and the data is read. Anytime that the code is waiting on the database to respond, the calling thread is blocked.

With both of these code blocks, the connection is held open while the command is executed and the data is read. The only difference is that with the async code, the calling thread is freed up to do other work.

What if when waiting for step 2, other long running tasks are at the head of the line in the task scheduler?

The time to deal with thread pool exhaustion is when you run into it. In the vast majority of scenarios, it isn't a problem and the default heuristics work fine.

This is particularly true if you use async everywhere and don't mix in blocking code.

For example, this code would be more problematic:

using (connection) { await connection.OpenAsync(); using(var reader = command.ExecuteReader()) { while(reader.Read()) { } } }

Now you have asynchronous code that, when it resumes, blocks a thread pool thread on I/O. Do that a lot, and you can end up in a thread pool exhaustion scenario.

Even worse now, we await with an open connection (and most likely added latency).

The added latency is miniscule. Like sub-millisecond (assuming no thread pool exhaustion). It's immeasurably small compared to random network fluctuations.

Aren't we holding open a connection longer than necessary? Isn't this an undesirable result? Wouldn't it be better to use synchronous methods to lessen the overall connection time, ultimately resulting in our data driven application performing better?

As noted above, synchronous code would hold the connection open just as long. (Well, OK, a sub-millisecond amount less, but that Doesn't Matter).

But as I've observed, there can definitely be weirdness when there are tasks scheduled in-between awaits that ultimately delay the operation, and essentially behave like blocking because of the limitations of the underlying resource.

It would be worrying if you observed this on the thread pool. That would mean you're already at thread pool exhaustion, and you should carefully review your code and remove blocking calls.

It's less worrying if you observed this on a single-thread scheduler (e.g., UI thread or ASP.NET Classic request context). In that case, you're not at thread pool exhaustion (though you still need to carefully review your code and remove blocking calls).