Ramon’s blog
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.

Navigation

Comments