Introducing sys.database_connection_stats for Troubleshooting Windows Azure SQL Database

Windows Azure SQL Database (formerly Sql Azure) now has a new system view called sys.database_connection_stats to help database Administrators track the status of incoming connections. The sys.database_connection_stats gives insight into how many incoming TDS connections are successful, terminated, throttled, and failed aggregated to a five minute window.

Before this feature improvement, application developers needed to catch and record connection failures and successes on the client-side, reporting back to their database administrator how well SQL Database was coping with the application’s requests. Now with sys.database_connection_stats, this functionality has been moved to the server, allowing administrators to query SQL Databases for aggregated statistics about their database.

Permissions

The view is accessed by connecting to the master database and only Administrators with permission to access the master database have read-only access to this view. 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. Typically the view will be updated within the hour, however it might take up to 24 hours for the view to update. 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.

Querying For Database Connection Statistics

Connect to the master database for your server and execute this query to view the database connection statistics:

SELECT *

FROM sys.database_connection_stats

I am using Sql Server Management Studio 2012 and my results look like this:

Notice that the time slices are broken down into 5 minute increments for my database. Also notice that this is a low traffic database so there are some time gaps where there were no connections. If I wanted to graph this data, it would be easiest if the time gaps were filled in with zero counts before graphing. I can do this with T-SQL that looks as follows:

DECLARE @Start datetime = '2012-10-25 00:00';

DECLARE @End datetime = '2012-10-26 00:00';

DECLARE @databaseName nvarchar(100) = 'Kulshan';

WITH Times (start_time) AS

( SELECT @Start

UNION ALL

SELECT DATEADD(minute,5, start_time)

FROM Times

WHERE start_time < @End

)

SELECT Times.start_time, ISNULL(DCS.success_count,0) + ISNULL(total_failure_count,0) 'total_connection_count',

ISNULL(database_name,@databaseName) 'database_name'

FROM Times

LEFT OUTER JOIN sys.database_connection_stats AS DCS ON DCS.start_time = Times.start_time

WHERE ((DCS.start_time >= @Start AND DCS.start_time < @End) OR DCS.start_time IS NULL) AND

(DCS.database_name = @databaseName OR DCS.database_name IS NULL)

ORDER BY Times.start_time

OPTION (MAXRECURSION 0);

The results look like this:

Now I can graph it in Excel and we have a graph of connections for the day:

Conclusion and More Information

The sys.database_connection_stats system view is an important new management and troubleshooting feature for Windows Azure SQL Database. Using this new view, administrators can monitor successful connections, terminated connections and throttled connections, and correlate this to specific time windows during the ongoing operation of applications using the database. You can read more about sys.database_connection_stats on its MSDN page.

{6230289B-5BEE-409e-932A-2F01FA407A92}