We are happy to announce the immediate availability of two new system views for Windows Azure SQL Database that provide diagnostic tracking information for your cloud databases.
Within the master database of your SQL Database server(s), you now have the following new system views available:
|
View |
Location |
Detail |
|
sys.event_log |
master |
Exposes log information from the system to the customer about each database hosted in that server. |
|
sys.database_connection_stats |
master |
Exposes connection statistics details around connection successes and failures. |
With this improvement, system administrators can now query for connection statistics and throttling events with the same tools and techniques that they use with on-premises SQL Server using system views designed for the cloud.
The sys.event_log View
At this time, the sys.event_log view enables the following key scenarios:
-
Exposing connection statistics information
-
Exposing deadlock graphs to customers
Core connection information can be found in sys.event_log as depicted below within SQL Server Management Studio 2012 (run select * from sys.event_log against the Master database):
Furthermore, deadlock graphs are exposed in the additional_data field in sys.event_log (run select * from sys.event_log where event_type = ‘deadlock’ against the Master database):
The sys.database_connection_stats View
Database connection statistics expose a rollup of key failures in each database over time. The sys.database_connection_stats view gives insight into how many incoming TDS connections are successful, terminated, or throttled: failures are aggregated to a five minute window.
An example of the kind of data in this table is shown below (run select * from sys.database_connection_stats against the Master database):
Additionally, the Windows Azure Management Portal, for each database, now exposes connection information graphically for easier management of your Windows Azure SQL Databases:
Permissions
The two new system views are accessed by connecting to the master database and only Administrators with permission to access the master database have (read-only) access to the views. Because this data is stored in the master database, the room to store this data is not counted as part of the user’s bill.
Latency
There is some lag from when the connection is made to when an increment for it will show up in the sys.database_connection_stats view. During that time, the information within a single row may be updated after the row is first written. SQL Database is a multi-tenant system with many nodes that participate in handling the connection, each one of those nodes can add data to the row.
For More Information
We hope the new views prove useful in managing your databases and getting more detailed diagnostic information. You can read more about these views on MSDN via the links below:
- sys.event_log
- sys.database_connection_stats
In addition, Wayne Berry has posted an MSDN blog with further information including some useful queries that can be constructed with these new views.
For questions about these features, you can post on the SQL Database MSDN Support Forum.
Enjoy!
-The Windows Azure SQL Database Team