Wait Statistics in Azure SQL Database

You need to be aware that you’re going to see differences when you’re working with Azure SQL Database when it comes to wait statistics. If you’re running a v12 Azure SQL Database (and if you’re not, go fix it), you can run a query against sys.dm_os_wait_stats. Here’s the output for the TOP 10 waits ordered by wait time on one of my databases:

Everything you’re used to seeing, right? Well… not quite. This is Azure SQL Database. So, let’s use sys.dm_db_wait_stats, a DMO that is only available within Azure. This lists waits by database. The TOP 10 here are:

You’ll notice that these results are wildly different from those above. What we’re looking is largely a server versus a database, but not completely. I mean that sys.dm_os_wait_stats is showing the waits for the instance on which my primary Azure SQL Database is currently running. Most of those waits are mine, but because it’s part of the management structure of Azure, sys.dm_os_wait_stats shows some information that’s not applicable, directly, to me. The “server” is not really that. It’s a logical container holding your database. There’s a lot more to it under the covers. To get the waits that are absolutely applicable to me and my databases, I have to go to sys.dm_db_wait_stats.

It goes further. One of the options for sys.dm_os_wait_stats is the ability to reset the waits. You have to call a DBCC command to do it:

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

However, if you run this on a Azure SQL Database, you’re going to get an error:

Msg 297, Level 16, State 11, Line 15
The user does not have permission to perform this action.

Makes sense. Azure SQL Database is a Platform as a Service. You don’t have access to the server level objects to make these types of changes.

So, we’ll fix it. I want to reset the database wait stats:

DBCC SQLPERF ('sys.dm_db_wait_stats', CLEAR);

This results in:

Msg 2526, Level 16, State 12, Line 15
Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.

If you read the documentation, you can’t reset the wait stats for the database. However, the wait stats will be reset if your database goes through a failover, which is actually hard to detect (still trying to figure this out actually). You can’t currently control the reset.

With the understanding of what you’re seeing, you can use sys.dm_db_wait_stats in the same way you would sys.dm_os_wait_stats.

Oh, and if you’re looking to filter the unnecessary wait stats, Paul Randal’s script works fine. Just substitute ‘db’ for ‘os’ and you’ll be happy.

EDIT: Just so we’re clear, sys.dm_db_wait_stats shows you just the waits in a single database. The database you’re running them from. Further, this DMV is only available in Azure SQL Database. I hope that helps the clarity of the message a little.

EDIT2: I’ve changed the description after some conversations. I think I’m closer to reality based on what I’ve been told and Jeremiah’s comment below. It’s hard because I’m explaining something I don’t directly support because I don’t work at Microsoft.

---

For lots more information on query tuning, I’m presenting an all day pre-con at SQL Day in Wroclaw Poland on May 16.