Some new/upcoming Windows Azure SQL Database DMVs and diagnostics

I didn't get to a whole lot of others' sessions at TechEd this year, but was pleasantly surprised by Vinod and Michal's session on "SQL Azure Administration and Application Self-Servicing". Although most of the really interesting stuff they showed wasn't in place yet (they demo'd on a "test portal" and test Windows Azure SQL Database (did I get the new name right?) servers, there are some things folks have been looking for that are available now. The near-future looks even better.

Windows Azure SQL Database (we gotta come up with an acronym for this, how about WASD for now?) has always contained a subset of DMVs from SQL Server. And the ones that are there don't expose "physical information", for example memory addresses, server-level info. That's because WASD is a multi-tenant database and you're not allowed to know what the other tenants are up to. Good.

But, when I lecture about maintaning indexes on WASD, the first comment out of folks' mouths was always "but it's missing sys.dm_db_index_physical/operational/usage_stats"! No longer. These DMV are there and work NOW, today. Modulo some physical information, of course, but its enough to tune your indexes with. Hoorah!

Here's a complete list, from the presentation (and from my experimentation, they all work now):

sys.dm_exec_query_memory_grants
sys.dm_exec_cached_plans

sys.dm_exec_trigger_stats
sys.dm_exec_procedure_stats

sys.dm_db_index_physical_stats
sys.dm_db_index_operational_stats
sys.dm_db_index_usage_stats

sys.dm_db_missing_index_details
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_columns

So diagnose away… The only one that I've had "trouble" with so far is sys.dm_db_missing_index_details. Most times I'll get an error message "Msg 40197, Level 20, State 1, Line 1 The service has encountered an error processing your request. Please try again. Error code 40143." The information I see seem to tie this error with database failover, so maybe I'll leave this one alone for the time being.

The more interesting views that don't seem to be active yet relate to SQL Server log information. You don't have access the log file directly in WASD (multi-tenant, figure it out). But there are two views in MASTER that will expose a subset of this information, as well as a portal-to-come. The actual views they mentioned seem to be there now, but return no information. So it's "in place", but not "hooked up" yet, I'm guessing. These views are:

sys.database_connection_stats – connection information
sys.event_log – event log information

The view names are obviously self-explanatory.

The coolest thing they demo'd was related to the question I always get, "Does WASD support SQL Profiler?". There will probably never be SQL Profiler support, AFAIK, but there will be (soon) Extended Event (the new profiler) support. Guess that deserves a post of its own.

@bobbeauch

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.