Al solucionar problemas de rendimiento en una instancia de SQL Server, el primer paso es siempre entender qué está ocurriendo en este momento: qué consultas se están ejecutando, qué sesiones están bloqueando a otras y en qué esperas se está invirtiendo el tiempo. Estos tres scripts T-SQL ofrecen una imagen inmediata de la carga de trabajo actual mediante las Vistas de Administración Dinámica (DMVs).

1. Consultas Bloqueadas y Bloqueantes

Si esta consulta no devuelve filas, no hay consultas bloqueadas en este momento. Ejecútela varias veces para detectar cadenas de bloqueo de corta duración. Nota: esto muestra únicamente el bloqueo actual — la contención de bloqueos acumulada a corto plazo requiere un análisis de estadísticas de espera (véase la sección 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. Análisis de Eventos de Espera

SQL Server recopila continuamente estadísticas de espera en sys.dm_os_wait_stats desde el último reinicio de la instancia. Restablecer esta vista y recopilar datos durante un intervalo breve proporciona una imagen enfocada de en qué está invirtiendo tiempo la instancia — E/S, CPU, bloqueos, pestillos y más. Para una referencia completa de los tipos de eventos de espera, consulte la documentación de MSDN. El script siguiente está basado en la conocida metodología de análisis de esperas de Paul Randal.

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. Transacciones Abiertas con Texto de Consulta y Plan de Ejecución

sp_who2 y sys.dm_exec_requests muestran las sesiones actuales, pero no exponen fácilmente el texto completo de la consulta ni el plan de ejecución de cada transacción abierta. Este script — también de la colección SQLskills de Paul Randal — combina las DMVs de transacciones para recuperar exactamente eso, incluyendo el uso del log por transacción. Es especialmente útil para diagnosticar transacciones de larga duración que mantienen bloqueos o consumen espacio en el log de transacciones.

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;