Edit

Monitor connections, sessions, and requests by using DMVs

Applies to: ✅ SQL analytics endpoint and Warehouse in Microsoft Fabric

Use existing dynamic management views (DMVs) to monitor connection, session, and request status in Microsoft Fabric. For more information about the tools and methods of executing T-SQL queries, see Query a warehouse.

In this tutorial, you learn how to monitor your running SQL queries by using dynamic management views (DMVs).

How to monitor connections, sessions, and requests by using query lifecycle DMVs

Three DMVs provide live SQL query lifecycle insights:

Together, these DMVs help you answer questions like:

  • Who is running a session?
  • When did the session start?
  • What's the ID of the connection to the Warehouse and the session that is running the request?
  • How many queries are actively running?
  • Which queries are long-running?

Prerequisites

  • A workspace with an active Fabric capacity.
  • An existing Warehouse or SQL analytics endpoint.
  • A T-SQL query tool, such as the SQL query editor or SQL Server Management Studio (SSMS).
  • Permissions to query DMVs and manage sessions.

Required permissions to query DMVs and manage sessions

  • A workspace Admin can execute all three DMVs (sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests) and see session, connection, and request information for all users within the workspace.
  • A workspace Member, Contributor, or Viewer can execute sys.dm_exec_sessions and sys.dm_exec_requests and see only their own sessions and requests within the warehouse. These roles can't execute sys.dm_exec_connections.
  • Only a workspace Admin can run the KILL command to stop a session.

Find warehouse connections and sessions

Join sys.dm_exec_connections and sys.dm_exec_sessions to view the session for each connection to the warehouse:

SELECT connections.connection_id,
    connections.connect_time,
    sessions.session_id, sessions.login_name, sessions.login_time, sessions.status
FROM sys.dm_exec_connections AS connections
INNER JOIN sys.dm_exec_sessions AS sessions
    ON connections.session_id = sessions.session_id;

Identify and KILL a long-running query

Use the following steps to find a long-running query in the warehouse, identify the user who started it, and if desired, stop the session that's running it.

  1. List active warehouse requests, ordered by how long each has been running since it arrived:

    SELECT request_id, session_id, start_time, total_elapsed_time
FROM sys.dm_exec_requests
WHERE status = 'running'
ORDER BY total_elapsed_time DESC;

  2. Find the user who started the session that contains the long-running query. Replace <session_id> with the session_id value from the previous step:

    SELECT login_name
FROM sys.dm_exec_sessions
WHERE session_id = <session_id>;

  3. If desired, cancel and roll back the session by running the KILL command with the session_id:

    KILL <session_id>;

    For example, to stop session 101:

    KILL 101;

For a step-by-step guide to diagnosing and resolving query blocking, see Troubleshoot query blocking in Fabric Data Warehouse.

Related content

  • Last updated on