Buscar
  • Ignez Mello

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:

  1. Crie a procedure sp_whoisactive em sua instância.

  2. Crie um banco de dados para você usar para administrar se ambiente, caso você não tenha.

  3. 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.

#spwhoisactive

1,476 visualizações