SQL Server Extended Events: Monitoring Queries Running Longer Than X Minutes

What Are Extended Events in SQL Server?
Extended Events provide a flexible and lightweight framework to capture detailed performance data in SQL Server. They help in diagnosing slow-running queries, deadlocks, waits, and other issues affecting database performance.
Why Use Extended Events Instead of SQL Profiler?
Low Overhead: Uses fewer system resources.
More Powerful: Captures granular event data.
Better Filtering: Allows precise filtering on execution time, database, users, etc.
Replaces SQL Trace/Profiler: Profiler is deprecated in newer SQL Server versions.

Step-by-Step: Configuring Extended Events for Queries Running More Than 5 Minutes
1. Create an Extended Events Session
We will create an Extended Events session to capture queries that take longer than 300 seconds (5 minutes) to execute.
Using SSMS GUI:
    Open SQL Server Management Studio (SSMS).
    Expand Management > Extended Events > Sessions.
    Right-click Sessions and choose New Session....
    Provide a name, e.g., Long_Running_Queries.
    Under Events, click "Add Event", search for sql_statement_completed, and add it.
    Under the Global Fields (Actions) tab, select:
        sql_text (to capture the query text)
        session_id (to track the session)
        database_id (to identify the database)
    Apply a Filter (Predicate):
        Click Configure, then Filter (Predicate).
        Select duration, set it to >= 3000000000 (300 seconds in microseconds).
    Configure Data Storage:
        Choose Event File as the target.
        Specify a file path for saving captured events.
    Click OK, then right-click the session and select Start Session.

Using T-SQL:
Alternatively, use the following T-SQL script to create the session:

CREATE EVENT SESSION [Long_Running_Queries]  
ON SERVER  
ADD EVENT sqlserver.sql_statement_completed (  
    WHERE duration >= 3000000000  -- 300 seconds (5 minutes) in microseconds  
)  
ADD TARGET package0.event_file (  
    SET filename = 'C:\Temp\LongRunningQueries.xel', max_file_size = 50MB  
)  
WITH (STARTUP_STATE = ON);  
GO  

2. Viewing and Analyzing the Captured Events
Using SSMS:
    Expand Management > Extended Events > Sessions.
    Right-click your session (Long_Running_Queries) and choose Watch Live Data.
    Execute long-running queries and monitor captured events in real-time.

Using T-SQL to Read the Event File:
To analyze captured events from the event file:
SELECT  
    event_data.value('(event/@name)', 'VARCHAR(100)') AS event_name,  
    event_data.value('(event/data[@name="sql_text"]/value)', 'NVARCHAR(MAX)') AS sql_text,  
    event_data.value('(event/data[@name="duration"]/value)', 'BIGINT') / 1000000 AS duration_seconds  
FROM  
(  
    SELECT CAST(event_data AS XML) AS event_data  
    FROM sys.fn_xe_file_target_read_file('C:\Temp\LongRunningQueries*.xel', NULL, NULL, NULL)  
) AS xevents  
ORDER BY duration_seconds DESC;

To stop the session:
ALTER EVENT SESSION [Long_Running_Queries] ON SERVER STATE = STOP;

To drop (delete) the session:
DROP EVENT SESSION [Long_Running_Queries] ON SERVER;