Identificar queries lentas com o sp_whoisactive
Escrita por from Adam Machanic (Blog – @AdamMachanic), esta procedure é uma das maravilhas na caixa de ferramentas de qualquer DBA. Particularmente, ela já me salvou em diversas ocasiões. Neste vídeo, o guru Brent Ozar explica as principais características desta procedure. E aqui você pode baixar a última versão da procedure e instalá-la em seu ambiente. Não se preocupe, ela só vai ajudar você. Pode instalar sem medo! :-D
A procedure sp_whoisactive retorna informações sobre o que está acontecendo naquele momento na sua instância. Para executá-la, basta digitar:
sp_whoisactive
Esta procedure pode receber uma série de parâmetros. Eu gosto muito destes três. É bom lembrar que, quanto mais parâmetros você passar, mais informações a procedure terá que buscar na instância, e mais lento pode ser o resultado. Meu pior retorno até hoje demorou 8 segundos. Tá, eu estava no meio de uma crise no ambiente, com tudo quase parando, então me pareceu mais 8 minutos. :-p
O resultado destes parâmetros é apresentado numa coluna exibida depois da coluna Physical_Reads.
@get_locs = 1
Retorna um trecho XML onde podemos clicar para ver trecho que você pode clicar para ver o que tabela, linha, objeto, lock que a consulta possui. É útil quando você está tentando descobrir por que uma consulta está bloqueando outras.
@get_task_info = 2
Se a query entrou em paralelismo e você está recebendo problemas com o wait type CXPACKET, você pode descobrir o que cada task na query está esperando.
@get_plans =1
Exibe o plano de execução da query "em execução".
Uma boa idéia para manter um histórico do ambiente é criar uma rotina que armazena o resultado da procedure sp_whoisactive, várias vezes ao dia, numa tabela. É uma excelente alternativa para mapear o ambiente, e é muito fácil de fazer:
Crie a procedure sp_whoisactive em sua instância.
Crie um banco de dados para você usar para administrar se ambiente, caso você não tenha.
Crie uma chamada em loopara armazenar o resultado da procedure na tabela. Eu, particularmente, crio um job que roda a cada minuto.
Script para criação da tabela
SP_whoisactive permite a criação da tabela de destino facilmente. Se quiser montar uma rotina que cria uma tabela a cada execução, inclua a data de criação no nome da tabela. Observe o comentário:
DECLARE @destination_table VARCHAR(4000) ;
SET @destination_table = 'Resultado_WhoIsActive' -- + '_' + CONVERT(VARCHAR, GETDATE(), 112) ;
DECLARE @schema VARCHAR(4000) ;
EXEC sp_WhoIsActive @get_transaction_info = 1,
@get_plans = 1,
@return_schema = 1,
@schema = @schema OUTPUT ;
SET @schema = REPLACE(@schema, '<table_name>', @destination_table) ;
PRINT @schema
EXEC(@schema) ;
Execução periódica
Pode ser óbvio, mas é melhor lembrar: a chamada do sp_whoisactive tem que ser igual à usada para criar a tabela, senão a procedure vai dar erro.
O loop abaixo roda 10 vezes, com 5 segundos de intervalo. Eu uso desta forma quando preciso validar algo urgente na instância:
DECLARE @destination_table VARCHAR(4000) , @msg NVARCHAR(1000), @numberOfRuns INT ;
SET @destination_table = 'Resultado_WhoIsActive_' + CONVERT(VARCHAR, GETDATE(), 112) ; SET @numberOfRuns = 10 ;
WHILE @numberOfRuns > 0 BEGIN; EXEC dbo.sp_WhoIsActive @get_transaction_info = 1, @get_plans = 1, @destination_table = @destination_table ;
SET @numberOfRuns = @numberOfRuns - 1 ;
IF @numberOfRuns > 0 BEGIN SET @msg = CONVERT(CHAR(19), GETDATE(), 121) + ': ' + 'Logged info. Waiting...' RAISERROR(@msg,0,0) WITH nowait ; WAITFOR DELAY '00:00:05' END ELSE BEGIN SET @msg = CONVERT(CHAR(19), GETDATE(), 121) + ': ' + 'Done.' RAISERROR(@msg,0,0) WITH nowait ;
END
END ;
Eu uni o útil ao agradável, e adaptei o código acima para rodar num job a cada minuto. Assim, tenho um retrato do ambiente para análise futura.
Visualizando o resultado
Agora, a parte "mais difícil": visualizar o resultado do loop acima:
DECLARE @destination_table NVARCHAR(2000), @dSQL NVARCHAR(4000) ;
SET @destination_table = 'Resultado_WhoIsActive_' + CONVERT(VARCHAR, GETDATE(), 112) ; SET @dSQL = N'SELECT collection_time, * FROM dbo.' + QUOTENAME(@destination_table) + N' order by 1 desc' ;
print @dSQL
EXEC sp_executesql @dSQL
Com o resultado na mão, podemos analisar o ambiente, identificar gargalos, enfim...
Dica final: o resultado da coluna Query Plan é exibido como um XML quando clicamos. Para visualizar o plano de execução no formato gráfico, salve o XML com a extensão SQLPLAN e então abra o arquivo no SQL Server. O resultado será exibido graficamente.