SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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:

waits_os

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:

waits_db

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.

The post Wait Statistics in Azure SQL Database appeared first on Home Of The Scary DBA.

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).

Comments

Leave a comment on the original post [www.scarydba.com, opens in a new window]

Loading comments...