Find current long SQL Queries in MS SQL Server

Find current long SQL Queries in SQL Server

Find the current running long SQL Queries in MS SQL Server

SELECT r.session_id,
       st.TEXT AS batch_text,
       r.start_time,
       r.status,
       r.total_elapsed_time
FROM sys.dm_exec_requests AS r
     CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE DB_NAME(r.database_id) = 'AdventureWorks2022'
ORDER BY cpu_time DESC;

Find the current long-running SQL Queries in MS SQL Server with XML Plan:

SELECT r.session_id,
       st.TEXT AS batch_text,
       qp.query_plan AS 'XML Plan',
       r.start_time,
       r.status,
       r.total_elapsed_time
FROM sys.dm_exec_requests AS r
     CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
     CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE DB_NAME(r.database_id) = 'AdventureWorks2022'
ORDER BY cpu_time DESC;

If you want to stop or kill the long-running queries session then use the KILL command:

KILL session_id
example:
kill 52

Catch long running and non committed queries:

catch long-running or uncommitted transactions:

SELECT [s_tst].[session_id],
[database_name] = DB_NAME (s_tdt.database_id),
[s_tdt].[database_transaction_begin_time], 
[sql_text] = [s_est].[text] 
FROM sys.dm_tran_database_transactions [s_tdt]
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];

Refer: https://docs.microsoft.com/en-us/troubleshoot/sql/performance/understand-resolve-blocking

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.