Por diversas vezes, j\u00e1 tivemos problemas de lentid\u00e3o ao executar a sp_WhoIsActive em ambientes com press\u00e3o de processamento, disco e conten\u00e7\u00e3o no TempDB, fazendo com que o retorno da SP demorasse v\u00e1rios segundos, at\u00e9 mesmo alguns minutos, uma vez que a sp_WhoIsActive tem muita utiliza\u00e7\u00e3o de TempDB para retornar os resultados da forma que ela retorna atualmente, que em um momento de crise torne invi\u00e1vel utiliza-la.<\/strong><\/p>\n Com o objetivo de prover uma solu\u00e7\u00e3o parecida, e que fosse mais leve e retorna-se as informa\u00e7\u00f5es necess\u00e1rias para a realiza\u00e7\u00e3o de um troubleshooting, permitindo que ela seja executada rapidamente mesmo em cen\u00e1rios como o citado acima, criamos essa vers\u00e3o mais \u201cenxuta\u201d, retornado as principais informa\u00e7\u00f5es e sem utilizar os diversos par\u00e2metros que a sp_WhoIsActive original nos fornece.<\/p>\n A sp_WhoIsActive \u00e9 muito mais completa, pois dependendo dos par\u00e2metros que s\u00e3o passados na sua execu\u00e7\u00e3o ela retorna v\u00e1rias outras informa\u00e7\u00f5es que n\u00e3o est\u00e3o contempladas no script abaixo. Se voc\u00ea quiser conhecer mais a sp_WhoIsActive a documenta\u00e7\u00e3o est\u00e1 dispon\u00edvel no site http:\/\/whoisactive.com\/docs\/<\/a>.<\/p>\n Principais diferen\u00e7as da sp_WhoIsActive<\/strong><\/p>\n Resultado da execu\u00e7\u00e3o da sp_Tiny_WhoisActive<\/p>\n Uma observa\u00e7\u00e3o \u00e9 que no script utilizamos a fun\u00e7\u00e3o Format<\/a>, para formatar os contadores, ela est\u00e1 dispon\u00edvel somente a partir do SQL Server 2012<\/strong>, ent\u00e3o em vers\u00e3o anteriores voc\u00ea ter\u00e1 que remover a fun\u00e7\u00e3o.<\/p>\n\n
SELECT\r\n RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) \/ 86400 AS VARCHAR), 2) + ' ' + \r\n RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) \/ 3600) % 24 AS VARCHAR), 2) + ':' + \r\n RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) \/ 60) % 60 AS VARCHAR), 2) + ':' + \r\n RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) % 60 AS VARCHAR), 2) + '.' + \r\n RIGHT('000' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) AS VARCHAR), 3) \r\n AS Duration,\r\n A.session_id AS session_id,\r\n B.command,\r\n TRY_CAST('<?query --' + CHAR(10) + (\r\n SELECT TOP 1 SUBSTRING(X.[text], B.statement_start_offset \/ 2 + 1, ((CASE\r\n WHEN B.statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), X.[text])) * 2)\r\n ELSE B.statement_end_offset\r\n END\r\n ) - B.statement_start_offset\r\n ) \/ 2 + 1\r\n )\r\n ) + CHAR(10) + '--?>' AS XML) AS sql_text,\r\n TRY_CAST('<?query --' + CHAR(10) + X.[text] + CHAR(10) + '--?>' AS XML) AS sql_command,\r\n A.login_name,\r\n '(' + CAST(COALESCE(E.wait_duration_ms, B.wait_time) AS VARCHAR(20)) + 'ms)' + COALESCE(E.wait_type, B.wait_type) + COALESCE((CASE \r\n 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)\r\n WHEN COALESCE(E.wait_type, B.wait_type) = 'OLEDB' THEN '[' + REPLACE(REPLACE(E.resource_description, ' (SPID=', ':'), ')', '') + ']'\r\n ELSE ''\r\n END), '') AS wait_info,\r\n FORMAT(COALESCE(B.cpu_time, 0), '###,###,###,###,###,###,###,##0') AS CPU,\r\n FORMAT(COALESCE(F.tempdb_allocations, 0), '###,###,###,###,###,###,###,##0') AS tempdb_allocations,\r\n 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,\r\n FORMAT(COALESCE(B.logical_reads, 0), '###,###,###,###,###,###,###,##0') AS reads,\r\n FORMAT(COALESCE(B.writes, 0), '###,###,###,###,###,###,###,##0') AS writes,\r\n FORMAT(COALESCE(B.reads, 0), '###,###,###,###,###,###,###,##0') AS physical_reads,\r\n FORMAT(COALESCE(B.granted_query_memory, 0), '###,###,###,###,###,###,###,##0') AS used_memory,\r\n NULLIF(B.blocking_session_id, 0) AS blocking_session_id,\r\n COALESCE(G.blocked_session_count, 0) AS blocked_session_count,\r\n (CASE \r\n WHEN B.[deadlock_priority] <= -5 THEN 'Low'\r\n WHEN B.[deadlock_priority] > -5 AND B.[deadlock_priority] < 5 AND B.[deadlock_priority] < 5 THEN 'Normal'\r\n WHEN B.[deadlock_priority] >= 5 THEN 'High'\r\n END) + ' (' + CAST(B.[deadlock_priority] AS VARCHAR(3)) + ')' AS [deadlock_priority],\r\n B.row_count,\r\n COALESCE(A.open_transaction_count, 0) AS open_tran_count,\r\n (CASE B.transaction_isolation_level\r\n WHEN 0 THEN 'Unspecified' \r\n WHEN 1 THEN 'ReadUncommitted' \r\n WHEN 2 THEN 'ReadCommitted' \r\n WHEN 3 THEN 'Repeatable' \r\n WHEN 4 THEN 'Serializable' \r\n WHEN 5 THEN 'Snapshot'\r\n END) AS transaction_isolation_level,\r\n A.[status],\r\n NULLIF(B.percent_complete, 0) AS percent_complete,\r\n A.[host_name],\r\n COALESCE(DB_NAME(CAST(B.database_id AS VARCHAR)), 'master') AS [database_name],\r\n (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],\r\n COALESCE(B.start_time, A.last_request_end_time) AS start_time,\r\n A.login_time,\r\n COALESCE(B.request_id, 0) AS request_id,\r\n W.query_plan\r\nFROM\r\n sys.dm_exec_sessions AS A WITH (NOLOCK)\r\n LEFT JOIN sys.dm_exec_requests AS B WITH (NOLOCK) ON A.session_id = B.session_id\r\n JOIN sys.dm_exec_connections AS C WITH (NOLOCK) ON A.session_id = C.session_id AND A.endpoint_id = C.endpoint_id\r\n LEFT JOIN msdb.dbo.sysjobs AS D ON RIGHT(D.job_id, 10) = RIGHT(SUBSTRING(A.[program_name], 30, 34), 10)\r\n LEFT JOIN (\r\n SELECT\r\n session_id, \r\n wait_type,\r\n\t\t\twait_duration_ms,\r\n resource_description,\r\n\t\t\tROW_NUMBER() OVER(PARTITION BY session_id ORDER BY (CASE WHEN wait_type LIKE 'PAGEIO%' THEN 0 ELSE 1 END), wait_duration_ms DESC) AS Ranking\r\n FROM \r\n sys.dm_os_waiting_tasks\r\n ) E ON A.session_id = E.session_id AND E.Ranking = 1\r\n LEFT JOIN (\r\n SELECT\r\n session_id,\r\n request_id,\r\n SUM(internal_objects_alloc_page_count + user_objects_alloc_page_count) AS tempdb_allocations,\r\n SUM(internal_objects_dealloc_page_count + user_objects_dealloc_page_count) AS tempdb_current\r\n FROM\r\n sys.dm_db_task_space_usage\r\n GROUP BY\r\n session_id,\r\n request_id\r\n ) F ON B.session_id = F.session_id AND B.request_id = F.request_id\r\n LEFT JOIN (\r\n SELECT \r\n blocking_session_id,\r\n COUNT(*) AS blocked_session_count\r\n FROM \r\n sys.dm_exec_requests\r\n WHERE \r\n blocking_session_id != 0\r\n GROUP BY\r\n blocking_session_id\r\n ) G ON A.session_id = G.blocking_session_id\r\n OUTER APPLY sys.dm_exec_sql_text(COALESCE(B.[sql_handle], C.most_recent_sql_handle)) AS X\r\n OUTER APPLY sys.dm_exec_query_plan(COALESCE(B.[sql_handle], C.most_recent_sql_handle)) AS W\r\nWHERE\r\n A.session_id > 50\r\n AND A.session_id <> @@SPID\r\n AND (A.[status] != 'sleeping' OR (A.[status] = 'sleeping' AND A.open_transaction_count > 0))\r\n<\/pre>\n![\"\"](\"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2017\/07\/Resultado_Tiny_WhoisActive.png?resize=678%2C52&ssl=1\")
<\/a><\/p>\n