I want to calculate the average number of connections per day to an Azure SQL Database.

This query return the SUM per day

DECLARE @StartDate DATE = DATEADD(day, -1, GETDATE()) -- 1 Days

SELECT database_name
    ,CAST(start_time AS DATE) AS start_time2
    ,SUM(success_count) AS Connections
FROM sys.database_connection_stats
WHERE start_time > @StartDate
GROUP BY database_name
    ,CAST(start_time AS DATE)
ORDER BY Connections DESC

It returns a dataset like this:

If I put AVG instead of SUM it returns:

And I know why!

This is because if you do SELECT * FROM sys.database_connection_stats you will notice that Azure SQL Database sum the connections every 5 minutes so in the column success_count you will mostly have numbers between 1 and 20.

So how can I aggregate per day and then have the average per day.

Because the goal is to see, in a span of 30 days, how many connections each database receives per day.

CodePudding user response：

Tonight I'm going to bad satisfied:

DECLARE @StartDate DATE = DATEADD(day, - 1, GETDATE()) -- 1 Days

SELECT t.name
    ,round(avg(CAST(t.Count_Connections AS FLOAT)), 2) AS AVG_Connections_per_Hour
FROM (
    SELECT name
        --,database_name
        --,success_count
        --,start_time
        ,CONVERT(DATE, start_time) AS Dating
        ,DATEPART(HOUR, start_time) AS Houring
        ,sum(CASE 
                WHEN name = database_name
                    THEN success_count
                ELSE 0
                END) AS Count_Connections
    FROM sys.database_connection_stats
    CROSS JOIN sys.databases
    WHERE start_time > @StartDate
        AND database_id != 1
    GROUP BY name
        ,CONVERT(DATE, start_time)
        ,DATEPART(HOUR, start_time)
    ) AS t
GROUP BY t.name

I had the genius idea to use:

FROM sys.database_connection_stats
CROSS JOIN sys.databases

Because the two table are not the same I'm basically creating a bug where each row of sys.databases is selected every time there is a row in sys.database_connection_stats.

Then I use CASE to select the number of connections when there are connections and to put a 0 instead if there are no connections.

I removed the master database with database_id != 1.

And I rounded the number of connections with round(avg(CAST(t.Count_Connections AS FLOAT)), 2).

I can now understand what Azure SQL Databases are good candidate for Serverless tier.