More Azure Goodies

Microsoft keeps sneaking little things under the door for Windows Azure SQL Database. This time it’s a couple of new views, a system view and a Dynamic Management View (DMV); sys.resource_stats and sys.dm_operation_stats.

But, I also learned another fun fact, not all this stuff rolls out at the same speed. For example, if I run sys.resource_stats on a database on a server located in the North Central US data center, the output looks like this:

But, if I run the same query against the same view with a database in a data center in East Asia (I experiment with where I put things), it looks like this (click on it to expand it, probably want to do that into a second tab or window so you can refer to it while you read):

The first set of query results look a lot like the data you’ll get from sys.resource_usage. Almost identical. But that second result set… that’s different. Let’s break down what we can see and what it infers.

• Start and end times are pretty clearly in five minute increments.
• And we’re seeing this for each database in the system.
• The sku refers to whether these are business or web databases.
• The usage_in_seconds column is pretty interesting. It must mean active connections coming into the system and the amount of time they’ve spent doing whatever. That’s nice to know.
• Storage… whatever.
• avg_cpu_cores_used, this is a server on which I’ve been testing Premium, so I could see greater than one for these values, but most of them are clearly less than 1. It looks like it’s a percentage use because it’s not simply showing 1 or 2 or something, but a decimal, and one that varies between time frames.
• avg_physical_read_iops, OK. I’m excited. This will give you a very good indication of the load you’re placing on the system and you can chart it out over time. You can tell that I wasn’t running anything on these servers.
• avg_physical_write_iops, more excitement
• active_memory_used_kb, That’s awesome. We’ll be able to see both the IO activity that you do to the disks on these databases, but how you’re using memory as well. These are real monitoring tools now for observing system behavior over time
• Active_session_count, again, very useful, mainly in conjunction with the other counters to understand correlation between the number of sessions on the system and the level of activity.
• Active_worker_count, not sure what this means unless we’re talking threads? Workers versus sessions must be referring to the fact that since this is a Premium database (although not currently enabled), I can see multi-threading through the multiple available CPUs. Cool.

Now, the DMO will just generate no data or an error if you attempt to run it on a data center that hasn’t been upgraded yet. Here’s the output from sys.dm_operation_stats from my database in East Asia:

To tell the truth, I’m not that excited by this one, although maybe if there were different functions on display it might be more exciting. Let’s quickly break it down a little.

• session_activity_id, ?
• resource_type, I’ve got nothing here either. Not sure what that number suggests except for the next column
• resource_type_desc, Oh, it’s a database.
• major_resource_id, AKA, the database name… I love working with Microsoft, but their little… tick/penchant/obsession with renaming EVERYTHING on a regular basis can be quite frustrating
• minor_resource_id, the GUID used to internally identify the database maybe? Interesting. Possibly useful. I wonder if it changes during a move situation. I wish there was a way to spark a move automatically.
• operation, interesting
• percent_complete, ooooh, I like this. Good to know where to go to possibly monitor certain operations. Although, I tested it, it doesn’t respond to normal insert/update/delete or database definition language (DDL) changes. Still…
• error_code, cool
• error_description, also cool.
• error_severity, once more, cool, we’re talking the ability to monitor this DMV as a means of tracking the system for errors.
• start_time, excellent. You can see how long a given operation ran (any monitored by this thing) ran.
• last_modified_time, not only does it show the thing it names, but, when combined with the preceding column, you can tell how long it ran. Another piece of monitoring/performance data.

We now have more information, at least on those servers that support it, for tracking system usage and behavior within WASD. This offering is constantly growing and expanding.