A developer’s view

Analyzing Azure SQL queries

· Read in about 2 min · (386 Words)
SQL Server Azure SQL

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.

Comments