Analyzing Azure SQL queries
I am working on a project for a customer to move an existing cloud solution to a sharded Azure SQL environment. Azure SQL has some major drawbacks compared to the traditional SQL Server environment. The most notable (IMHO) is the lack of the SQL Profiler. I had a hard time investigating queries that took a long time to complete.
To solve this issue I have created the following query. It displays all the active connections and shows the following information:
- Session identifier.
- Host name and program name of the connection (specify the
ApplicationName
in the connection string to get the appropriate program name). - Status of the session (running, sleeping, …).
- Identifier of the session that is blocking the current request (0 if it’s not blocked by another session).
- Status of the command (running, suspended, …).
- Active command and what the command is currently waiting on.
- Text of the SQL command (this is missing from most Azure SQL provided stored procedures, such as
sp_who2
). - Start time of the command and how long it is already running.
SELECT
s.[session_id],
s.[host_name],
s.[program_name],
s.[status] AS [session_status],
r.[blocking_session_id],
p.[cmd] as [cmd],
r.[status] as [command_status],
wt.[wait_type],
t.[text],
r.[start_time],
DATEDIFF(millisecond, r.[start_time], GETUTCDATE()) AS [duration_in_ms]
FROM [sys].[dm_exec_sessions] s
INNER JOIN [sysprocesses] p ON s.[session_id] = p.[spid]
LEFT JOIN [sys].[dm_os_waiting_tasks] wt ON s.[session_id] = wt.[session_id]
LEFT JOIN [sys].[dm_exec_requests] r ON s.[session_id] = r.[session_id]
OUTER APPLY [sys].[dm_exec_sql_text](r.[sql_handle]) AS t
WHERE s.[session_id] <> @@SPID
ORDER BY [duration_in_ms] DESC
Sometimes you need to kill all connection to the Azure SQL database. This can be accomplished by running the following query:
DECLARE KillProcessCursor CURSOR LOCAL FORWARD_ONLY
FOR
SELECT c.[session_id], s.[host_name], s.[program_name], s.[status]
FROM [sys].[dm_exec_connections] c
JOIN [sys].[dm_exec_sessions] s ON c.[session_id] = s.[session_id]
WHERE c.[session_id] <> @@SPID --AND s.[status] = 'sleeping'
ORDER BY c.[connect_time] ASC
DECLARE @spid smallint
DECLARE @hostname sysname
DECLARE @programname sysname
DECLARE @status sysname
OPEN KillProcessCursor
FETCH NEXT FROM KillProcessCursor INTO @spid, @hostname, @programname, @status
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Termination session ' + CAST(@spid as varchar(max)) + ' [' + @status + '] (' + @programname + ' on ' + @hostname + ')'
EXEC('KILL ' + @spid)
FETCH NEXT FROM KillProcessCursor INTO @spid, @hostname, @programname, @status
END
CLOSE KillProcessCursor
DEALLOCATE KillProcessCursor
GO
It shows you all the session that are killed. Uncomment the part in the WHERE clause to only kill the sleeping connections.