No post de hoje, vou mostrar pra vocês como retornar as sessões ativas do SQL Server, mostrando uso de CPU, quantidade de leituras e escritas, qual o usuário está realizando a consulta, o que está sendo executado por esta sessão, qual o software utilizado, qual o hostname e mais várias outras informações sobre a sessão e a consulta. Para quem já conhece a sp_WhoIsActive (Se você não conhece, saiba mais acessando este post), vai reparar que elas possuem um retorno bem parecido, e esse foi o intuito da query mesmo.
Por diversas vezes, já tive problemas de lentidão ao executar a sp_WhoIsActive em ambientes com alto processamento e contenção de disco e/ou TempDB, fazendo com que o retorno da SP demorasse vários segundos, até mesmo alguns minutos, uma vez que essa SP tem muita utilização de TempDB para retornar os resultados da forma que ela retorna atualmente.
Com o objetivo de prover uma solução parecida, mas que fosse mais leve e não utilizasse a TempDB, permitindo que ela seja executada rapidamente mesmo em cenários como o citado acima, eu e o Tiago Neves criamos essa versão mais “enxuta”, retornado as principais informações e sem utilizar os diversos parâmetros que a SP original nos fornece.
Qual a diferença para a sp_WhoIsActive?
Não utiliza a TempDB
Execução mais rápida
Código mais simples de entender
Pode ser facilmente utilizada como view, table-valued function ou scalar function, permitindo utilizar order by, select into, where, etc.
Além de mostrar a query em execução, mostra também o Outer Command (a sp_WhoIsActive também mostra se utilizado o parâmetro @get_outer_command = 1)
Caso a sessão seja de um job, mostra o nome do job na coluna program_name
Retorna o XML do plano de execução (a sp_WhoIsActive também mostra se utilizado o parâmetro @get_plans = 1)
Código-fonte da “sp_Tiny_WhoIsActive”
Com o código-fonte abaixo, você pode retornar as informações das sessões em execução com apenas 1 SELECT, podendo até criar uma view para facilitar as consultas futuras.
Visualizar código-fonte (Versão 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))
Caso você queira uma versão ainda mais leve e mais rápida, eu retirei alguns recursos (Como exibição do XML do plano de execução, contagem de sessões em lock, etc) e disponibilizei o código abaixo:
Visualizar código-fonte (Versão mais rápida e com alguns recursos a menos)
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 vocês podem ter observado, eu utilizo a função FORMAT, disponível a partir do SQL Server 2012. Caso você esteja numa versão anterior, a execução do código abaixo vai gerar um erro para você. Basta remover essa função para conseguir utilizar a query normalmente (Os números só não terão os separadores de milhar, mas isso é até bom para fazer ordenação), ou utilizar a versão abaixo:
Visualizar código-fonte (SQL Server 2008 e anterior)
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 da execução da query
É isso aí, pessoal!
Caso vocês tenham sugestões ou melhorias, fiquem à vontade para me avisar nos comentários ou enviar sua alteração no link https://github.com/dirceuresende/tiny-sp_whoisactive.
Espero que tenham gostado, um abraço e até a próxima!
sql server lightweight tiny sp_whoisactive identificar sessão query executando mais leve sem consumir tempdb low consumption
sql server lightweight tiny sp_whoisactive identificar sessão query executando mais leve sem consumir tempdb low consumption
Dirceu Resende
Arquiteto de Banco de Dados e BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.
Comentários (0)
Carregando comentários…