Find Long Running Queries in Azure SQL Database

Identifying and optimizing long-running queries is crucial for maintaining the performance and efficiency of your Azure SQL Database. Slow queries can lead to resource bottlenecks, affecting the overall user experience and system stability. In this post.we will see how to find long running queries in Azure SQL database. Whether you’re a database administrator or a developer, these insights will enable you to ensure your database runs smoothly and efficiently.

SQL query to find long running queries in SQL database

-- Query to check currently running SQL queries in Azure SQL Database or SQL Server database.

SELECT 
    r.session_id,
    r.status,
    r.command,
    r.start_time,
    r.percent_complete,
    SUBSTRING(qt.text, (r.statement_start_offset/2) + 1, 
    ((CASE r.statement_end_offset 
        WHEN -1 THEN DATALENGTH(qt.text)
        ELSE r.statement_end_offset 
        END - r.statement_start_offset)/2) + 1) AS query_text,
    s.host_name,
    s.login_name,
    s.program_name,
    s.client_interface_name,
    r.database_id,
    DB_NAME(r.database_id) AS database_name,
    r.total_elapsed_time,
    r.cpu_time,
    r.reads,
    r.writes,
    r.logical_reads
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) AS qt
WHERE 
    r.session_id <> @@SPID
ORDER BY 
    r.start_time DESC;

Here,
sys.dm_exec_requests (r): Lists currently executing requests.
sys.dm_exec_sessions (s): Contains info on active user connections and internal tasks.
sys.dm_exec_sql_text (qt): Retrieves the text of the SQL batch for the given SQL handle.

Pro tips:
1. DMV queries should be run with caution in production environments.
2. Above query can also be run the check currently running queries in SQL Server database.
3. In case you want to automate database maintenance in Azure SQL, refer to this post.

See more

Kunal Rathi

With over 13 years of experience in data engineering and analytics, I've assisted countless clients in gaining valuable insights from their data. As a dedicated supporter of Data, Cloud and DevOps, I'm excited to connect with individuals who share my passion for this field. If my work resonates with you, we can talk and collaborate.