Lesson Learned #231: Hands-On-Labs: What is Azure SQL working on

Microsoft

Jul 25, 2022

One of the main questions that we have in a live performance troubleshooting scenario is to answer the question what is Azure SQL Database or Managed Instance working on?. In this video I'm going to share some insights how to find the information.

In critical service requests when our customers need an immediately response and resolution for their performance issue, we often run queries like you have below (besides other ones) to know the process that are currently running. In order to have a easy way to capture this information I developed this Powershell script that you could find here, running it you could have this information and save it to a file.

SELECT
    substring(REPLACE(REPLACE(SUBSTRING(ST.text, (req.statement_start_offset/2) + 1, (
(CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE req.statement_end_offset END
- req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 1, 4000) AS statement_text
,req.database_id as database_id
,program_name as program_name
,req.session_id as session_id
, req.cpu_time as cpu_time_ms
, req.status as status
, wait_time as wait_time
, wait_resource as wait_resource 
, wait_type as wait_type 
, last_wait_type as last_wait_type
, req.total_elapsed_time as total_elapsed_time
, total_scheduled_time as total_scheduled_time
, req.row_count as [Row Count]
, command as command
, scheduler_id as scheduler_id
, memory_usage as memory_usage
, req.writes as writes
, req.reads as reads 
, req.logical_reads as logical_reads
, blocking_session_id as blocking_session_id
, CASE blocking_session_id WHEN 0 THEN 'Noblocking' ELSE ( select t.text as BlockerQuery FROM sys.dm_exec_connections as connblocker cross apply sys.dm_exec_sql_text(connblocker.most_recent_sql_handle) AS T where blocking_session_id=connblocker.session_id ) end AS BlockedQuery
, host_name as host_name
, host_process_id as host_process_id
, login_time as login_time
, client_net_address as client_net_adress
, TextPlan.query_plan as QueryPlan
FROM sys.dm_exec_requests AS req
INNER JOIN sys.dm_exec_connections as Conn on req.session_id=Conn.session_id
inner join sys.dm_exec_sessions as sess on sess.session_id = req.session_id
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as ST
CROSS APPLY sys.dm_exec_text_query_plan(req.plan_handle,0,-1) as TextPlan
where req.session_id <> @@SPID

Main Goal of this PowerShell script.

Collect live data during a process execution every 5 seconds.
Obtain details about query text, wait time, execution plan, program name and other details.
Additionally, you could configure a filter to obtain only the queries that you have some specific interest, for example, high cpu, elapsed time or wait type.
This PowerShell script will continuously running until you press CTRL-C and it could be run in unattended mode.

Configuration file

server=servername.database.windows.net
user=username
password=Password
Db=DatabaseName
ApplicationName=SQL Performance Monitoring
RetryLogicNumberAttempts=10
RetryLogicNumberAttemptsBetweenAttemps=5
ConnectionTimeout=60
CommandTimeout=3600
SecondsToWait= 5
orderby=order by req.database_id
ViewType=ALL
MaxLenStatement=4000

Configuration file:

Default content: ALL;ALL;ALL;"All Queries"
Additional rules:
- cpu_time_ms;-gt;100;"High CPU"
- last_wait_type;-eq;"PAGELATCH_EX";"High PageLatchEX"

Example of data gathered:

Rule "High PageLatchEX"
statement_text:SELECT TOP 600000 * INTO #t FROM dbo.Example1
database_id:7
program_name:Testing by JMJD - SQL (HighTempDB)
session_id:67
cpu_time_ms:271
status:suspended
wait_resource:2:1:107
last_wait_type:PAGELATCH_EX
blocking_session_id:77
BlockerQuery: SELECT TOP 600000 * INTO #t FROM dbo.Example1; DROP TABLE #t; ||