Product:

Microsoft AZURE SQL database

Issue:

It is running a long time, what is the SQL doing?

Solution:

In SSMS, run below query’s to get some information:

 

SELECT
r.session_id,
r.status,
r.wait_type,
r.wait_time,
r.blocking_session_id,
r.cpu_time,
r.total_elapsed_time,
DB_NAME(r.database_id) AS database_name,
t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
ORDER BY r.total_elapsed_time DESC;

 

 

SELECT
r.session_id,
s.login_name,
s.host_name,
r.status,
r.command,
r.cpu_time,
r.total_elapsed_time,
r.logical_reads,
r.reads,
r.writes,
DB_NAME(r.database_id) AS database_name,
t.text AS sql_text
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s
ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id <> @@SPID
ORDER BY r.cpu_time DESC;

 

 

 

More Information:

https://www.mssqltips.com/sqlservertip/5521/understanding-and-using-sql-server-sysdmexecrequests/ 

https://dev.to/shiviyer/mastering-performance-troubleshooting-with-sysdmexecrequests-in-azure-sql-3f0j