Difference between Concurrent sessions, workers, log ins in Azure SQL Database

Question

Can someone explains me the difference between Concurrent sessions, concurrent workers, external concurrent connections and concurrent log ins in Azure SQL database ?

Further, how would someone monitor active sessions,workers etc (any SQL queries) ?

Thanks.

Answer 1

Here we go:

• Sessions refers to the number of concurrent connections allowed to a SQL database at a time.
• Workers can be thought of as the processes in the SQL database that are processing queries.

The maximum number of sessions and workers allowed depends on your databases's service tier: https://www.bluematador.com/docs/troubleshooting/azure-sql-sessions-and-workers#:~:text=Sessions%20refers%20to%20the%20number,on%20your%20databases's%20service%20tier.

I have a query that you can run on the master database and can give you statistics about that:

DECLARE @StartDate date = DATEADD(day, -30, GETDATE()) -- 14 Days

SELECT
    @@SERVERNAME AS ServerName
    ,database_name AS DatabaseName
    ,sysso.edition
    ,sysso.service_objective
    ,(SELECT TOP 1 dtu_limit FROM sys.resource_stats AS rs3 WHERE rs3.database_name = rs1.database_name ORDER BY rs3.start_time DESC)  AS DTU
    /*,(SELECT TOP 1 storage_in_megabytes FROM sys.resource_stats AS rs2 WHERE rs2.database_name = rs1.database_name ORDER BY rs2.start_time DESC)  AS StorageMB */
    /*,(SELECT TOP 1 allocated_storage_in_megabytes FROM sys.resource_stats AS rs4 WHERE rs4.database_name = rs1.database_name ORDER BY rs4.start_time DESC)  AS Allocated_StorageMB*/ 
    ,avcon.AVG_Connections_per_Hour
    ,CAST(MAX(storage_in_megabytes) / 1024 AS DECIMAL(10, 2)) StorageGB
    ,CAST(MAX(allocated_storage_in_megabytes) / 1024 AS DECIMAL(10, 2)) Allocated_StorageGB
    ,MIN(end_time) AS StartTime
    ,MAX(end_time) AS EndTime
    ,CAST(AVG(avg_cpu_percent) AS decimal(4,2)) AS Avg_CPU
    ,MAX(avg_cpu_percent) AS Max_CPU
    ,(COUNT(database_name) - SUM(CASE WHEN avg_cpu_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name) * 100 AS [CPU Fit %]
    ,CAST(AVG(avg_data_io_percent) AS decimal(4,2)) AS Avg_IO
    ,MAX(avg_data_io_percent) AS Max_IO
    ,(COUNT(database_name) - SUM(CASE WHEN avg_data_io_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name) * 100 AS [Data IO Fit %]
    ,CAST(AVG(avg_log_write_percent) AS decimal(4,2)) AS Avg_LogWrite
    ,MAX(avg_log_write_percent) AS Max_LogWrite
    ,(COUNT(database_name) - SUM(CASE WHEN avg_log_write_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name) * 100 AS [Log Write Fit %]
    ,CAST(AVG(max_session_percent) AS decimal(4,2)) AS 'Average % of sessions'
    ,MAX(max_session_percent) AS 'Maximum % of sessions'
    ,CAST(AVG(max_worker_percent) AS decimal(4,2)) AS 'Average % of workers'
    ,MAX(max_worker_percent) AS 'Maximum % of workers'
  
  
FROM sys.resource_stats AS rs1
inner join sys.databases dbs on rs1.database_name = dbs.name
INNER JOIN sys.database_service_objectives sysso on sysso.database_id = dbs.database_id
inner join 

(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) avcon on avcon.name = rs1.database_name


WHERE start_time > @StartDate
GROUP BY database_name, sysso.edition, sysso.service_objective,avcon.AVG_Connections_per_Hour
ORDER BY database_name , sysso.edition, sysso.service_objective