This article is contributed. See the original author and article here.
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 text option on SSMS. The grid would be nearly impossible to read properly:
Recreate the situation that you want to monitor. Once 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
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.
Recent Comments