Troubleshooting SQL On-demand or Serverless DMVs

[Liliam_C_Leme]

I was working in a few SQL On-demand or SQL Serverless cases and based on an ex-PFE colleague Fabricio Catae script I created this one to help me with some troubleshooting scenarios. Feel free to adapt the script yourself for your scenario.

Basically, I want to take a snapshot of my DMVs while I am troubleshooting a scenario on SQLOD. So I asked the customer to repro the scenario while running my script and once is done. I work on the data collected and issue timestamps.

SET NOCOUNT ON
SET LOCK_TIMEOUT 30000

DECLARE @startDate DATETIME



SET NOCOUNT ON
SET LOCK_TIMEOUT 30000



WHILE 1=1
BEGIN


SET @startDate = GETDATE()


   print 'Begin Requests filtered ' + CONVERT(VARCHAR(24), GETDATE(), 121) + ' ' + convert(VARCHAR(12), datediff(ms,@startDate,getdate())) 

   select * from sys.dm_exec_requests where session_id >77

   print 'End Requests filtered ' + CONVERT(VARCHAR(24), GETDATE(), 121) + ' ' + convert(VARCHAR(12), datediff(ms,@startDate,getdate())) 

   print '================================================= ' 

   print 'Begin Requests  ' + CONVERT(VARCHAR(24), GETDATE(), 121) + ' ' + convert(VARCHAR(12), datediff(ms,@startDate,getdate())) 

   select * from sys.dm_exec_requests

   print 'End Requests  ' + CONVERT(VARCHAR(24), GETDATE(), 121) + ' ' + convert(VARCHAR(12), datediff(ms,@startDate,getdate())) 
   print '================================================= ' 

   print 'Begin Sessions ' + CONVERT(VARCHAR(24), GETDATE(), 121) + ' ' + convert(VARCHAR(12), datediff(ms,@startDate,getdate())) 

   select * from sys.dm_exec_sessions 

   print 'End Sessions ' + CONVERT(VARCHAR(24), GETDATE(), 121) + ' ' + convert(VARCHAR(12), datediff(ms,@startDate,getdate())) 

   print '================================================= ' 

   print 'Begin Queries running ' + CONVERT(VARCHAR(24), GETDATE(), 121) + ' ' + convert(VARCHAR(12), datediff(ms,@startDate,getdate())) 

	   SELECT 
		'Running' as [Status],
		Transaction_id as [Request ID],
		'SQL On-demand' as [SQL Resource],
		s.login_name as [Submitter],
		s.Session_Id as [Session ID],
		req.start_time as [Submit time],
		req.start_time as [Start time],
		'N/A' as [End time],
		req.command as [Request Type],
		SUBSTRING(
			sqltext.text, 
			(req.statement_start_offset/2)+1,   
			(
				(
					CASE req.statement_end_offset  
						WHEN -1 THEN DATALENGTH(sqltext.text)  
						ELSE req.statement_end_offset  
					END - req.statement_start_offset
				)/2
			) + 1
		) as [Query Text],
		req.total_elapsed_time as [Duration],
		'N/A' as [Queued Duration],
		req.total_elapsed_time as [Running Duration],
		'N/A' as [Data processed in bytes],
		'N/A' as [Workload group],
		'N/A' as [Source],
		'N/A' as [Pipeline],
		'N/A' as [Importance],
		'N/A' as [Classifier]
	FROM 
    sys.dm_exec_requests req
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) sqltext
    JOIN sys.dm_exec_sessions s ON req.session_id = s.session_id
 
    print 'End Queries running ' + CONVERT(VARCHAR(24), GETDATE(), 121) + ' ' + convert(VARCHAR(12), datediff(ms,@startDate,getdate()))   
    print '================================================= ' 

    print 'Begin Query requests greater than 50 ' + CONVERT(VARCHAR(24), GETDATE(), 121) + ' ' + convert(VARCHAR(12), datediff(ms,@startDate,getdate())) 

	SELECT 
		req.session_id, req.blocking_session_id AS 'blocked', 
		req.database_id AS db_id, req.command, 
		req.total_elapsed_time AS 'elapsed_time', req.cpu_time, req.granted_query_memory AS 'granted_memory', req.logical_reads, 
		req.wait_time, CAST(req.wait_type AS VARCHAR(16)) AS 'wait_type', 
		req.open_transaction_count AS 'tran_count', 
		req.reads, req.writes,  
		req.start_time, req.status, req.connection_id, req.user_id, 
		req.group_id, -- KATMAI (SQL2008)
		req.transaction_id, req.request_id, 
		CAST(req.plan_handle AS VARBINARY(26)) AS 'plan_handle', 
		CAST(req.sql_handle AS VARBINARY(26)) AS 'sql_handle', 
		req.nest_level,
		req.statement_start_offset AS 'stmt_start', req.statement_end_offset AS 'stmt_end', 
		req.query_hash, req.query_plan_hash
	FROM sys.dm_exec_requests req
	WHERE group_id > 1 AND session_id<>@@SPID 
	and req.session_id > 50


	print 'End Query requests greater than 50 ' + CONVERT(VARCHAR(24), GETDATE(), 121) + ' ' + convert(VARCHAR(12), datediff(ms,@startDate,getdate())) 
	print '================================================= ' 

	waitfor delay '00:00:03'

END 

This is as you probably noticed  a script with an infinite loop : WHILE 1=1

Basically, you would need to connect to SQL On Demand: Server-ondemand.sql.azuresynapse.net

Once connected. Run the script using the result as a text option on SSMS. The grid default option would be nearly  impossible to read properly:

While running the script as I mentioned before, please recreate the situation that you want to monitor, as for example, I wanted to monitor a block\lock scenario. Once is done, stop the script as it is an infinite loop and look for the information that you want to find over the script results.

That is it!

Liliam UK Engineer