I’ve been using Microsoft’s cloud database for some time now. I’ve had a few customers with various performance problems and thought I’d take a moment to highlight the interesting behavior you may run into as well.

As you can expect with any type of SQL Server solution, the normal wait types are all there and you will probably see them regularly.

ASYNC_NETWORK_IO

This wait type is triggered when SQL Server is waiting on the client to receive the data it requested. In other words, when a user runs a query they need to get the results to display on their screen. This wait type ticks away while the user is in the process of getting those results.

This is the wait that every DBA hopes to see since it generally has nothing to do with the database.

PAGEIOLATCH_[??]

PAGEIOLATCH waits come in a variety of forms. Most commonly seen are SH for shared and EX for exclusive. These waits occur when SQL Server is waiting for data from storage.

For an OnPrem SQL instance, I’d expect to see latency of 10ms or less. Since we don’t have visibility into the storage on Azure SQL Database, we can’t do much other than look at how long we’re waiting.

Interestingly enough, extra DTU’s aren’t going to make the storage any faster. You’re best bet here is to tune your queries and reduce the overall I/O overhead.

LCK_[?]_[?]

Lock waits are probably the most common in any relational database. Anytime you’re waiting for a resource that another session has exclusively then you’re suspended and waiting on an LCK lock.

These next two are Azure SQL Database exclusives and may catch you off guard the first time you see them.

IO_QUEUE_LIMIT

IO_QUEUE_LIMIT occurs when your database has too many asynchronous IOs pending. So what does that mean? If you read the wait type description, you’ll find that this is essentially due to your plan level and the number of DTUs assigned. The good news is I’ve never seen it wait for an extended period of time.

Increasing or decreasing the DTUs for this database will directly affect this wait type.

RUNNABLE tasks in SQL Azure Database

This last item isn’t really a wait type but is still a cause for your query to wait.

Runnable tasks are those that are waiting on nothing else except time on a scheduler, which essentially is CPU time.

Given the following example, where we have one processor, there is one scheduler. Only one session can use the scheduler at a time. When multiple sessions are ready and waiting to be processed they sit in a runnable queue and are marked with a status of RUNNABLE.

RUNNABLE tasks are nothing new to SQL Server and depending on your workload you may see them often. So what is new and how does this apply to Azure SQL Database?

You’d generally expect that at the very least one or more sessions should have a status of RUNNING. In Azure SQL Database I’ve seen cases where every session is marked as RUNNABLE, meaning that no work is being done for my queries and this is clearly a concern for the performance of my application. I assume this is due to how it was architected and that in the multi-tenant instance the schedulers are busy with other customers queries while yours are waiting.

Much like PAGEIOLATCH waits, adding additional DTUs will not remedy this concern. This would generally be resolved by tuning your CPU heavy queries or by adding processors to the instance. With Azure SQL Database, the best bet is to tune reducing the need for processor time in your queries.

 

My next entry will likely be on troubleshooting storage with SQL Server. Look for that soon.