Continued from the previous post, "Some new/upcoming Windows Azure SQL Database DMVs and diagnostics"… Read that one to find out who Vinod and Michal are.
So, before you all run out and try it, this isn't working yet. But, like the WASD event log view, the pieces seem to be in place. So, standard disclaimer applies. I only saw it working in Vinod and Michal's demo. The stuff I'm looking at may bear no resemblance to the final product. On the other hand, it may…
So, I went looking in WASD today. Some of the extended event views exist in every database today. This jibes with the demo that showed an XEvent node in SSMS Object Explorer in the Database/Programmability folder. As opposed to XEvents sessions, etc in SQL Server which live in master, these appear to live in each database. Which only makes sense if you know how WASD works…
Obviously there is no SSMS node in SSMS today. But for those of us who like XEvents in SQL Server 2008, sans GUI, that really doesn't present a problem.
They showed four pre-defined XEvent sessions, and said that initially there would only be predefined sessions. No user-defined sessions. The names they showed matched the output from "select * from sys.event_sessions" and are:
azure_xe_activity_tracking
azure_xe_query_batch
azure_xe_query_detail
azure_xe_query_waits
You can get metadata and "current" data by using views that are similarly named to the views in SQL Server. SSMS object explorer for WASD shows the following:
sys.event_session_actions
sys.event_session_events
sys.event_session_fields
sys.event_session_targets
sys.event_sessions
sys.dm_xe_database_session_event_actions
sys.dm_xe_database_session_events
sys.dm_xe_database_session_object_columns
sys.dm_xe_database_session_targets
sys.dm_xe_database_sessions
Some of these views are more "filled in" than others. For example, the views related the XEvent targets are currently empty. And there are no targets for any of the predefined event session. Note the use of the naming convention "dm_xe_database…" for the views instead of "dm_xe_server…". Makes sense.
So, gui or no gui, target or no target, I just had to try this. Had my "stop" and "harvest" scripts available in case it did start, but…
alter event session azure_xe_query_waits on server state=start;
Produces: "session does not exist or you do not have permission"
Hmmm… how about:
alter event session azure_xe_query_waits on database state=start;
Produces: "Failed to start event session 'azure_xe_query_waits' because doing so will exceed the maximum number allowed for this database."
Sounds like that could be the ticket. In future. So, until then, folks diagnosing WASD issues wait for the announcement for this, and the event log views, with baited breath. When I asked some SQL Azure folks about the lack of announcements about the "intermediate" versions of WASD (i.e. there was no formal announcement on the Azure blog about the new DMVs), the response was "we'd like to make small version changes to WASD a non-event, and only announce large changes". This will be a big…eh… event. THANKS Vinod and Michal.
@bobbeauch