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

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.

CodePudding user response：

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 refers to the number,on your databases's service tier.

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