En la publicación de hoy, le mostraré cómo devolver sesiones activas de SQL Server, mostrando el uso de la CPU, la cantidad de lecturas y escrituras, qué usuario está realizando la consulta, qué ejecuta esta sesión, qué software se utiliza, cuál es el nombre de host y otra información adicional sobre la sesión y la consulta. Para los que ya conocen sp_WhoIsActive (Si no lo conoces, descubre más accediendo a esta publicación), notarás que tienen un retorno muy similar, y esa era la intención de la consulta.
En varias ocasiones he tenido problemas de lentitud al ejecutar sp_WhoIsActive en entornos con alto procesamiento y contención de disco y/o TempDB, provocando que el retorno del SP tarde varios segundos, incluso unos minutos, ya que este SP tiene mucho uso de TempDB para devolver los resultados en la forma en que regresa actualmente.
Con el objetivo de brindar una solución similar, pero más liviana y que no usara TempDB, permitiendo una ejecución rápida incluso en escenarios como el mencionado anteriormente, yo y Tiago Neves Creamos esta versión “más ágil”, devolviendo la información principal y sin utilizar los diversos parámetros que nos proporciona el SP original.
¿Cuál es la diferencia con sp_WhoIsActive?
No utiliza TempDB
Ejecución más rápida
Código más simple de entender
Se puede usar fácilmente como vista, función con valores de tabla o función escalar, lo que le permite usar ordenar por, seleccionar en, dónde, etc.
Además de mostrar la consulta en ejecución, también muestra el comando externo (sp_WhoIsActive también muestra si se usa el parámetro @get_outer_command = 1)
Si la sesión es un trabajo, muestra el nombre del trabajo en la columna nombre_programa
Devuelve el XML del plan de ejecución (sp_WhoIsActive también muestra si se utiliza el parámetro @get_plans = 1)
Código fuente de "sp_Tiny_WhoIsActive"
Con el código fuente a continuación, puede devolver información de sesiones en ejecución con solo 1 SELECCIONAR, e incluso puede crear una vista para facilitar consultas futuras.
Ver código fuente (versión completa: SQL Server 2012+)
SELECT
RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 86400 AS VARCHAR), 2) + ' ' +
RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 3600) % 24 AS VARCHAR), 2) + ':' +
RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 60) % 60 AS VARCHAR), 2) + ':' +
RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) % 60 AS VARCHAR), 2) + '.' +
RIGHT('000' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) AS VARCHAR), 3)
AS Duration,
A.session_id AS session_id,
B.command,
TRY_CAST('<?query --' + CHAR(10) + (
SELECT TOP 1 SUBSTRING(X.[text], B.statement_start_offset / 2 + 1, ((CASE
WHEN B.statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), X.[text])) * 2)
ELSE B.statement_end_offset
END
) - B.statement_start_offset
) / 2 + 1
)
) + CHAR(10) + '--?>' AS XML) AS sql_text,
TRY_CAST('<?query --' + CHAR(10) + X.[text] + CHAR(10) + '--?>' AS XML) AS sql_command,
A.login_name,
'(' + CAST(COALESCE(E.wait_duration_ms, B.wait_time) AS VARCHAR(20)) + 'ms)' + COALESCE(E.wait_type, B.wait_type) + COALESCE((CASE
WHEN COALESCE(E.wait_type, B.wait_type) LIKE 'PAGE%LATCH%' THEN ':' + DB_NAME(LEFT(E.resource_description, CHARINDEX(':', E.resource_description) - 1)) + ':' + SUBSTRING(E.resource_description, CHARINDEX(':', E.resource_description) + 1, 999)
WHEN COALESCE(E.wait_type, B.wait_type) = 'OLEDB' THEN '[' + REPLACE(REPLACE(E.resource_description, ' (SPID=', ':'), ')', '') + ']'
ELSE ''
END), '') AS wait_info,
FORMAT(COALESCE(B.cpu_time, 0), '###,###,###,###,###,###,###,##0') AS CPU,
FORMAT(COALESCE(F.tempdb_allocations, 0), '###,###,###,###,###,###,###,##0') AS tempdb_allocations,
FORMAT(COALESCE((CASE WHEN F.tempdb_allocations > F.tempdb_current THEN F.tempdb_allocations - F.tempdb_current ELSE 0 END), 0), '###,###,###,###,###,###,###,##0') AS tempdb_current,
FORMAT(COALESCE(B.logical_reads, 0), '###,###,###,###,###,###,###,##0') AS reads,
FORMAT(COALESCE(B.writes, 0), '###,###,###,###,###,###,###,##0') AS writes,
FORMAT(COALESCE(B.reads, 0), '###,###,###,###,###,###,###,##0') AS physical_reads,
FORMAT(COALESCE(B.granted_query_memory, 0), '###,###,###,###,###,###,###,##0') AS used_memory,
NULLIF(B.blocking_session_id, 0) AS blocking_session_id,
COALESCE(G.blocked_session_count, 0) AS blocked_session_count,
'KILL ' + CAST(A.session_id AS VARCHAR(10)) AS kill_command,
(CASE
WHEN B.[deadlock_priority] <= -5 THEN 'Low'
WHEN B.[deadlock_priority] > -5 AND B.[deadlock_priority] < 5 AND B.[deadlock_priority] < 5 THEN 'Normal'
WHEN B.[deadlock_priority] >= 5 THEN 'High'
END) + ' (' + CAST(B.[deadlock_priority] AS VARCHAR(3)) + ')' AS [deadlock_priority],
B.row_count,
COALESCE(A.open_transaction_count, 0) AS open_tran_count,
(CASE B.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END) AS transaction_isolation_level,
A.[status],
NULLIF(B.percent_complete, 0) AS percent_complete,
A.[host_name],
COALESCE(DB_NAME(CAST(B.database_id AS VARCHAR)), 'master') AS [database_name],
(CASE WHEN D.name IS NOT NULL THEN 'SQLAgent - TSQL Job (' + D.[name] + ' - ' + SUBSTRING(A.[program_name], 67, LEN(A.[program_name]) - 67) + ')' ELSE A.[program_name] END) AS [program_name],
H.[name] AS resource_governor_group,
COALESCE(B.start_time, A.last_request_end_time) AS start_time,
A.login_time,
COALESCE(B.request_id, 0) AS request_id,
W.query_plan
FROM
sys.dm_exec_sessions AS A WITH (NOLOCK)
LEFT JOIN sys.dm_exec_requests AS B WITH (NOLOCK) ON A.session_id = B.session_id
JOIN sys.dm_exec_connections AS C WITH (NOLOCK) ON A.session_id = C.session_id AND A.endpoint_id = C.endpoint_id
LEFT JOIN msdb.dbo.sysjobs AS D ON RIGHT(D.job_id, 10) = RIGHT(SUBSTRING(A.[program_name], 30, 34), 10)
LEFT JOIN (
SELECT
session_id,
wait_type,
wait_duration_ms,
resource_description,
ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY (CASE WHEN wait_type LIKE 'PAGE%LATCH%' THEN 0 ELSE 1 END), wait_duration_ms) AS Ranking
FROM
sys.dm_os_waiting_tasks
) E ON A.session_id = E.session_id AND E.Ranking = 1
LEFT JOIN (
SELECT
session_id,
request_id,
SUM(internal_objects_alloc_page_count + user_objects_alloc_page_count) AS tempdb_allocations,
SUM(internal_objects_dealloc_page_count + user_objects_dealloc_page_count) AS tempdb_current
FROM
sys.dm_db_task_space_usage
GROUP BY
session_id,
request_id
) F ON B.session_id = F.session_id AND B.request_id = F.request_id
LEFT JOIN (
SELECT
blocking_session_id,
COUNT(*) AS blocked_session_count
FROM
sys.dm_exec_requests
WHERE
blocking_session_id != 0
GROUP BY
blocking_session_id
) G ON A.session_id = G.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(COALESCE(B.[sql_handle], C.most_recent_sql_handle)) AS X
OUTER APPLY sys.dm_exec_query_plan(B.plan_handle) AS W
LEFT JOIN sys.dm_resource_governor_workload_groups H ON A.group_id = H.group_id
WHERE
A.session_id > 50
AND A.session_id <> @@SPID
AND (A.[status] != 'sleeping' OR (A.[status] = 'sleeping' AND A.open_transaction_count > 0))
Si desea una versión aún más ligera y rápida, eliminé algunas funciones (como mostrar el XML del plan de ejecución, contar sesiones bloqueadas, etc.) y puse el código a disposición a continuación:
Ver el código fuente (versión más rápida con menos funciones)
SELECT
RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 86400 AS VARCHAR), 2) + ' ' +
RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 3600) % 24 AS VARCHAR), 2) + ':' +
RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 60) % 60 AS VARCHAR), 2) + ':' +
RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) % 60 AS VARCHAR), 2) + '.' +
RIGHT('000' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) AS VARCHAR), 3)
AS Duration,
A.session_id AS session_id,
B.command,
TRY_CAST('<?query --' + CHAR(10) + (
SELECT TOP 1 SUBSTRING(X.[text], B.statement_start_offset / 2 + 1, ((CASE
WHEN B.statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), X.[text])) * 2)
ELSE B.statement_end_offset
END
) - B.statement_start_offset
) / 2 + 1
)
) + CHAR(10) + '--?>' AS XML) AS sql_text,
TRY_CAST('<?query --' + CHAR(10) + X.[text] + CHAR(10) + '--?>' AS XML) AS sql_command,
A.login_name,
'(' + CAST(COALESCE(E.wait_duration_ms, B.wait_time) AS VARCHAR(20)) + 'ms)' + COALESCE(E.wait_type, B.wait_type) + COALESCE((CASE
WHEN COALESCE(E.wait_type, B.wait_type) LIKE 'PAGEIOLATCH%' THEN ':' + DB_NAME(LEFT(E.resource_description, CHARINDEX(':', E.resource_description) - 1)) + ':' + SUBSTRING(E.resource_description, CHARINDEX(':', E.resource_description) + 1, 999)
WHEN COALESCE(E.wait_type, B.wait_type) = 'OLEDB' THEN '[' + REPLACE(REPLACE(E.resource_description, ' (SPID=', ':'), ')', '') + ']'
ELSE ''
END), '') AS wait_info,
FORMAT(COALESCE(B.cpu_time, 0), '###,###,###,###,###,###,###,##0') AS CPU,
FORMAT(COALESCE(F.tempdb_allocations, 0), '###,###,###,###,###,###,###,##0') AS tempdb_allocations,
FORMAT(COALESCE((CASE WHEN F.tempdb_allocations > F.tempdb_current THEN F.tempdb_allocations - F.tempdb_current ELSE 0 END), 0), '###,###,###,###,###,###,###,##0') AS tempdb_current,
FORMAT(COALESCE(B.logical_reads, 0), '###,###,###,###,###,###,###,##0') AS reads,
FORMAT(COALESCE(B.writes, 0), '###,###,###,###,###,###,###,##0') AS writes,
FORMAT(COALESCE(B.reads, 0), '###,###,###,###,###,###,###,##0') AS physical_reads,
FORMAT(COALESCE(B.granted_query_memory, 0), '###,###,###,###,###,###,###,##0') AS used_memory,
NULLIF(B.blocking_session_id, 0) AS blocking_session_id,
'KILL ' + CAST(A.session_id AS VARCHAR(10)) AS kill_command,
(CASE
WHEN B.[deadlock_priority] <= -5 THEN 'Low'
WHEN B.[deadlock_priority] > -5 AND B.[deadlock_priority] < 5 AND B.[deadlock_priority] < 5 THEN 'Normal'
WHEN B.[deadlock_priority] >= 5 THEN 'High'
END) + ' (' + CAST(B.[deadlock_priority] AS VARCHAR(3)) + ')' AS [deadlock_priority],
B.row_count,
COALESCE(A.open_transaction_count, 0) AS open_tran_count,
(CASE B.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END) AS transaction_isolation_level,
A.[status],
NULLIF(B.percent_complete, 0) AS percent_complete,
A.[host_name],
COALESCE(DB_NAME(CAST(B.database_id AS VARCHAR)), 'master') AS [database_name],
A.[program_name],
COALESCE(B.start_time, A.last_request_end_time) AS start_time,
A.login_time,
COALESCE(B.request_id, 0) AS request_id
FROM
sys.dm_exec_sessions AS A WITH (NOLOCK)
LEFT JOIN sys.dm_exec_requests AS B WITH (NOLOCK) ON A.session_id = B.session_id
JOIN sys.dm_exec_connections AS C WITH (NOLOCK) ON A.session_id = C.session_id AND A.endpoint_id = C.endpoint_id
LEFT JOIN (
SELECT
session_id,
wait_type,
wait_duration_ms,
resource_description,
ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY (CASE WHEN wait_type LIKE 'PAGEIO%' THEN 0 ELSE 1 END), wait_duration_ms) AS Ranking
FROM
sys.dm_os_waiting_tasks
) E ON A.session_id = E.session_id AND E.Ranking = 1
LEFT JOIN (
SELECT
session_id,
request_id,
SUM(internal_objects_alloc_page_count + user_objects_alloc_page_count) AS tempdb_allocations,
SUM(internal_objects_dealloc_page_count + user_objects_dealloc_page_count) AS tempdb_current
FROM
sys.dm_db_task_space_usage
GROUP BY
session_id,
request_id
) F ON B.session_id = F.session_id AND B.request_id = F.request_id
OUTER APPLY sys.dm_exec_sql_text(COALESCE(B.[sql_handle], C.most_recent_sql_handle)) AS X
WHERE
A.session_id > 50
AND A.session_id <> @@SPID
AND (A.[status] != 'sleeping' OR (A.[status] = 'sleeping' AND A.open_transaction_count > 0))
Como habrás notado, uso la función FORMATO, disponible en SQL Server 2012. Si tienes una versión anterior, ejecutar el código siguiente generará un error. Simplemente elimine esta función para poder usar la consulta normalmente (los números simplemente no tendrán separadores de miles, pero esto es incluso bueno para ordenar), o use la siguiente versión:
Ver código fuente (SQL Server 2008 y versiones anteriores)
SELECT
RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 86400 AS VARCHAR), 2) + ' ' +
RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 3600) % 24 AS VARCHAR), 2) + ':' +
RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 60) % 60 AS VARCHAR), 2) + ':' +
RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) % 60 AS VARCHAR), 2) + '.' +
RIGHT('000' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) AS VARCHAR), 3)
AS Duration,
A.session_id AS session_id,
B.command,
CAST('<?query --' + CHAR(10) + (
SELECT TOP 1 SUBSTRING(X.[text], B.statement_start_offset / 2 + 1, ((CASE
WHEN B.statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), X.[text])) * 2)
ELSE B.statement_end_offset
END
) - B.statement_start_offset
) / 2 + 1
)
) + CHAR(10) + '--?>' AS XML) AS sql_text,
CAST('<?query --' + CHAR(10) + X.[text] + CHAR(10) + '--?>' AS XML) AS sql_command,
A.login_name,
'(' + CAST(COALESCE(E.wait_duration_ms, B.wait_time) AS VARCHAR(20)) + 'ms)' + COALESCE(E.wait_type, B.wait_type) + COALESCE((CASE
WHEN COALESCE(E.wait_type, B.wait_type) LIKE 'PAGEIOLATCH%' THEN ':' + DB_NAME(LEFT(E.resource_description, CHARINDEX(':', E.resource_description) - 1)) + ':' + SUBSTRING(E.resource_description, CHARINDEX(':', E.resource_description) + 1, 999)
WHEN COALESCE(E.wait_type, B.wait_type) = 'OLEDB' THEN '[' + REPLACE(REPLACE(E.resource_description, ' (SPID=', ':'), ')', '') + ']'
ELSE ''
END), '') AS wait_info,
COALESCE(B.cpu_time, 0) AS CPU,
COALESCE(F.tempdb_allocations, 0) AS tempdb_allocations,
COALESCE((CASE WHEN F.tempdb_allocations > F.tempdb_current THEN F.tempdb_allocations - F.tempdb_current ELSE 0 END), 0) AS tempdb_current,
COALESCE(B.logical_reads, 0) AS reads,
COALESCE(B.writes, 0) AS writes,
COALESCE(B.reads, 0) AS physical_reads,
COALESCE(B.granted_query_memory, 0) AS used_memory,
NULLIF(B.blocking_session_id, 0) AS blocking_session_id,
COALESCE(G.blocked_session_count, 0) AS blocked_session_count,
'KILL ' + CAST(A.session_id AS VARCHAR(10)) AS kill_command,
(CASE
WHEN B.[deadlock_priority] <= -5 THEN 'Low'
WHEN B.[deadlock_priority] > -5 AND B.[deadlock_priority] < 5 AND B.[deadlock_priority] < 5 THEN 'Normal'
WHEN B.[deadlock_priority] >= 5 THEN 'High'
END) + ' (' + CAST(B.[deadlock_priority] AS VARCHAR(3)) + ')' AS [deadlock_priority],
B.row_count,
B.open_transaction_count,
(CASE B.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END) AS transaction_isolation_level,
A.[status],
NULLIF(B.percent_complete, 0) AS percent_complete,
A.[host_name],
COALESCE(DB_NAME(CAST(B.database_id AS VARCHAR)), 'master') AS [database_name],
A.[program_name],
H.[name] AS resource_governor_group,
COALESCE(B.start_time, A.last_request_end_time) AS start_time,
A.login_time,
COALESCE(B.request_id, 0) AS request_id,
W.query_plan
FROM
sys.dm_exec_sessions AS A WITH (NOLOCK)
LEFT JOIN sys.dm_exec_requests AS B WITH (NOLOCK) ON A.session_id = B.session_id
JOIN sys.dm_exec_connections AS C WITH (NOLOCK) ON A.session_id = C.session_id AND A.endpoint_id = C.endpoint_id
LEFT JOIN (
SELECT
session_id,
wait_type,
wait_duration_ms,
resource_description,
ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY (CASE WHEN wait_type LIKE 'PAGEIO%' THEN 0 ELSE 1 END), wait_duration_ms) AS Ranking
FROM
sys.dm_os_waiting_tasks
) E ON A.session_id = E.session_id AND E.Ranking = 1
LEFT JOIN (
SELECT
session_id,
request_id,
SUM(internal_objects_alloc_page_count + user_objects_alloc_page_count) AS tempdb_allocations,
SUM(internal_objects_dealloc_page_count + user_objects_dealloc_page_count) AS tempdb_current
FROM
sys.dm_db_task_space_usage
GROUP BY
session_id,
request_id
) F ON B.session_id = F.session_id AND B.request_id = F.request_id
LEFT JOIN (
SELECT
blocking_session_id,
COUNT(*) AS blocked_session_count
FROM
sys.dm_exec_requests
WHERE
blocking_session_id != 0
GROUP BY
blocking_session_id
) G ON A.session_id = G.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(COALESCE(B.[sql_handle], C.most_recent_sql_handle)) AS X
OUTER APPLY sys.dm_exec_query_plan(B.[plan_handle]) AS W
LEFT JOIN sys.dm_resource_governor_workload_groups H ON A.group_id = H.group_id
WHERE
A.session_id > 50
AND A.session_id <> @@SPID
AND (A.[status] != 'sleeping' OR (A.[status] = 'sleeping' AND B.open_transaction_count > 0))
Resultado de la ejecución de la consulta
¡Eso es todo, amigos!
Si tiene alguna sugerencia o mejora, no dude en hacérmelo saber en los comentarios o enviar sus cambios a través del enlace. https://github.com/dirceuresende/tiny-sp_whoisactive.
Espero que os haya gustado, un abrazo y ¡hasta la próxima!
servidor sql liviano pequeño sp_whoisactive identifica la sesión de consulta ejecutándose más liviano sin consumir tempdb de bajo consumo
servidor sql liviano pequeño sp_whoisactive identifica la sesión de consulta ejecutándose más liviano sin consumir tempdb de bajo consumo
Dirceu Resende
Arquitecto de Bases de Datos y BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.
Comentários (0)
Carregando comentários…