When troubleshooting performance issues on a SQL Server instance, the first step is always to understand what is happening right now: which queries are running, which sessions are blocking others, and where time is being spent waiting. These three T-SQL scripts give you an immediate picture of the current workload using Dynamic Management Views (DMVs).

1. Blocked and Blocking Queries

If this query returns no rows, there are no blocked queries at this moment. Run it multiple times to catch short-lived blocking chains. Note: this surfaces current blocking only — cumulative short-term locking contention requires wait stats analysis (see section 2).

SELECT 'BLOCKING STATUS' AS Controllo,
    BlockedSPID     = LEFT(blocked.session_id, 5),
    BlockedQuery    = CONVERT(VARCHAR(50), blockedsql.text),
    BlockingSPID    = CONVERT(VARCHAR(50), blocking.session_id),
    BlockingQuery   = CONVERT(VARCHAR(50), blockingsql.text)
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_requests blocking
    ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY (SELECT * FROM sys.dm_exec_sql_text(blocked.sql_handle))  blockedsql
CROSS APPLY (SELECT * FROM sys.dm_exec_sql_text(blocking.sql_handle)) blockingsql;

2. Wait Event Analysis

SQL Server continuously collects wait statistics in sys.dm_os_wait_stats since the last instance restart. Resetting this view and collecting data for a short window gives you a focused picture of what the instance is spending time waiting on — I/O, CPU, locks, latches, and more. For a full reference of wait event types, see the MSDN documentation. The script below is based on Paul Randal’s well-known wait analysis methodology.

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR); -- Reset the DMV
GO

SELECT
    [owt].[session_id],
    [owt].[exec_context_id],
    [owt].[wait_duration_ms],
    [owt].[wait_type],
    [owt].[blocking_session_id],
    [owt].[resource_description],
    [es].[program_name],
    [est].[text],
    [est].[dbid],
    [eqp].[query_plan],
    [es].[cpu_time],
    [es].[memory_usage]
FROM sys.dm_os_waiting_tasks [owt]
INNER JOIN sys.dm_exec_sessions  [es]  ON [owt].[session_id] = [es].[session_id]
INNER JOIN sys.dm_exec_requests  [er]  ON [es].[session_id]  = [er].[session_id]
OUTER APPLY sys.dm_exec_sql_text   ([er].[sql_handle])   [est]
OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle])  [eqp]
WHERE [es].[is_user_process] = 1
ORDER BY [owt].[session_id], [owt].[exec_context_id];

3. Open Transactions with Query Text and Execution Plan

sp_who2 and sys.dm_exec_requests show current sessions, but they don’t easily expose the full query text and execution plan for each open transaction. This script — also from Paul Randal’s SQLskills collection — joins the transaction DMVs to retrieve exactly that, including log usage per transaction. It’s particularly useful for diagnosing long-running transactions that are holding locks or consuming transaction log space.

SELECT
    s_tst.[session_id],
    s_es.[login_name]                               AS [Login Name],
    DB_NAME(s_tdt.database_id)                      AS [Database],
    s_tdt.[database_transaction_begin_time]         AS [Begin Time],
    s_tdt.[database_transaction_log_record_count]   AS [Log Records],
    s_tdt.[database_transaction_log_bytes_used]     AS [Log Bytes],
    s_tdt.[database_transaction_log_bytes_reserved] AS [Log Reserved],
    s_est.[text]                                    AS [Last T-SQL Text],
    s_eqp.[query_plan]                              AS [Last Plan]
FROM sys.dm_tran_database_transactions s_tdt
JOIN  sys.dm_tran_session_transactions s_tst ON s_tst.[transaction_id] = s_tdt.[transaction_id]
JOIN  sys.dm_exec_sessions             s_es  ON s_es.[session_id]      = s_tst.[session_id]
JOIN  sys.dm_exec_connections          s_ec  ON s_ec.[session_id]      = s_tst.[session_id]
LEFT JOIN sys.dm_exec_requests         s_er  ON s_er.[session_id]      = s_tst.[session_id]
CROSS APPLY sys.dm_exec_sql_text  (s_ec.[most_recent_sql_handle]) AS s_est
OUTER APPLY sys.dm_exec_query_plan(s_er.[plan_handle])            AS s_eqp
ORDER BY [Begin Time] ASC;