SQL Server – Utilizando a SP WhoIsActive para identificar Locks, Blocks, Queries Lentas, Queries em Execução e muito mais

Descrição das colunas

Antes de mostrar o que muda com cada parâmetro utilizado, vou mostrar a vocês o que cada coluna dessa SP nos retorna:

Coluna	Descrição
dd hh:mm:ss:mss	Coluna que informa há quanto tempo a query está em execução (para sessões ativas) ou há quanto tempo a última instrução foi executada pela sessão (para sessões inativas - sleeping)
session_id	Número da sessão que está executando a query (SPID)
sql_text	XML que contém um trecho da query que está em execução (ou toda a query, se for apenas um statement)
login_name	Nome do DOMINIO\USUARIO que está executando essa query
wait_info	Caso a sessão esteja com algum evento de wait, informa há quantos milissegundos esse evento está ocorrendo e qual o tipo de evento (Ex: LCK_M_S, CXPACKET, OLEDB, etc)
CPU	Medição da quantidade de ciclos de CPU utilizados pela sessão (um número muito alto significa que essa sessão já usou muito CPU do servidor, mas não quer dizer que está utilizando atualmente)
tempdb_allocations	Quantidade de páginas da TempDB (8 KB cada página) que já foram alocadas para por essa sessão através de tabelas temporárias, spools, LOBs, etc). Um número muito alto aqui, significa que essa sessão tem muitas páginas alocadas, mas não quer dizer que ela é a causadora dos eventos de Autogrow da TempDB.
tempdb_current	Quantidade de páginas da TempDB que estão sendo alocadas atualmente por essa sessão. Essa conta se resume em quantidade de páginas alocadas - quantidade de páginas desalocadas da TempDB. Um número muito alto aqui, significa que ela é uma possível causadora de eventos de Autogrow na TempDB.
blocking_session_id	Exibe o número da sessão que está bloqueando a sessão analisada (gerando um evento de wait LCK nessa sessão)
reads	Quantidade de páginas lógicas de 8 KB lidas da memória do servidor (leitura rápida)
writes	Quantidade de páginas físicas de 8 KB escritas no disco do servidor
physical_reads	Quantidade de páginas físicas de 8 KB lidas no disco do servidor (leitura lenta)
used_memory	Quantidade de páginas de 8 KB utilizadas da memória do servidor pela combinação da procedure cache memory e workspace memory grant.
status	Define a situação da execução atual da query, que pode ser um dos valores abaixo: Running: Significa que a sessão está ativa, executando um ou mais batches. Esse estado quer dizer que a sessão está conectada no banco de dados, já enviou os comandos para o servidor e está aguardando o processamento por parte do SQL Server. Suspended: Esse estado quer dizer que a sessão não está ativa, pois está aguardando algum recurso do servidor (I/O, Rede, etc). Quando esse recurso for liberado, a sessão se tornará ativa novamente e retornará o processamento. Runnable: Esse estado quer dizer que a sessão já foi atribuída para um worker thread do processador, mas não está conseguindo enviar para o CPU executar. Caso esteja ocorrendo esse evento com muita frequência e por muito tempo no seu ambiente, pode significar que você precisa de aumentar o processador do seu servidor ou diminuir o paralelismo das queries em execução (leia-se MAXDOP), que podem estar ocupando todos os núcleos. Pending: Esse estado quer dizer que a sessão está pronta e aguardando um worker thread do processador pegá-la para executar. É importante ressaltar que isso não significa que você precise aumentar o parâmetro "Max. Worker threads", talvez você precise checar o que as outras threads estão fazendo e porque elas não estão executando. Background: A solicitação está rodando em plano de fundo, geralmente utilizado pelo Resource Monitor ou Deadlock Monitor. Sleeping: A sessão está aberta e conectada no banco, mas não tem nenhuma requisição para processar.
open_tran_count	Coluna extraída da descontinuada view sysprocesses, que permite visualizar quantas transações abertas ativas a sessão está utilizando e quão profundo é o nível de aninhamento dessas transações.
percent_complete	Exibe quantos % foi concluído de queries longas (ALTER INDEX REORGANIZE, AUTO_SHRINK option with ALTER DATABASE, BACKUP DATABASE, DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKTABLE, DBCC INDEXDEFRAG, DBCC SHRINKDATABASE, DBCC SHRINKFILE, RECOVERY, RESTORE DATABASE, ROLLBACK, TDE ENCRYPTION)
host_name	Nome da máquina física de onde está vindo a conexão
database_name	Nome do database atual da conexão, onde estão sendo enviadas as queries
program_name	Nome do software utilizado durante a conexão (Ex: Microsoft SQL Server Management Studio - Query)
start_time	Mostra a data de quando a query começou a ser executada
login_time	Mostra a data que a sessão fez login na instância
request_id	Número da requisição atual da sessão. Essa coluna não tem uma interpretação muito clara sobre a sua utilidade. Quando o status da sessão for "sleeping", o valor do request_id será geralmente NULL, e caso contrário, será 0 (zero). Caso você encontre um valor maior que 0 (zero) na coluna request_id, significa que essa sessão está executando mais de um batch simultaneamente utilizando o MARS (Multiple Active Result Sets)
collection_time	Mostra a data da coleta dos dados (data de execução da sp_WhoIsActive)

Essas colunas podem ser agrupadas em 4 categorias:

Tempo e status

• [dd hh:mm:ss.mss]
• [start_time]
• [percent_complete]
• [collection_time]
• [status]

Identificadores de sessão e usuário

• [session_id]
• [request_id]
• [login_name]
• [host_name]
• [database_name]
• [program_name]

Fatores que podem causar lentidão na query

• [wait_info]
• [blocking_session_id]

Uso de recurso da sua sessão

• [sql_text]
• [CPU]
• [tempdb_allocations]
• [tempdb_current]
• [reads]
• [writes]
• [physical_reads]
• [used_memory]
• [open_tran_count]

Consultando a ajuda da sp_WhoIsActive

Essa ferramenta possui sua própria ajuda, e podemos acessá-la utilizando o parâmetro @help = 1:

e a saída é divida em 3 resultsets:

Informações sobre o criador da SP

Descrição dos parâmetros da chamada da SP

Descrição das colunas retornadas pela SP

Permissões mínimas necessárias para executar a SP

Um dos pontos importantes a se comentar, são as permissões mínimas necessárias para se utilizar a sp_WhoIsActive. Já vi muitos DBA’s concederem acesso de sysadmin para um usuário, para que ele possa utilizar essa SP, por não conhecer o que é necessário liberar para que o usuário execute a Stored Procedure na instância, visto que o mais comum, é tentar liberar apenas a permissão de EXECUTE na Stored Procedure.

Caso você não libere a permissão de view server state, você verá a mensagem de erro abaixo:

Para isso, basta executar o comando abaixo, para que o usuário consiga utilizar a sp_WhoIsActive normalmente:

Caso você não utilize nenhum parâmetro, essa é a visão que você terá ao executar a sp_WhoIsActive. Como existem muitas colunas, eu tive que cortar o resultado em 2 imagens.

Parte 1:

Parte 2

Um recurso muito legal dessa ferramenta, é a possibilidade de realizar filtros nos resultados sem precisar exportar para uma tabela física e depois filtrar. Para isso, podemos utilizar os parâmetros de filtro da própria SP.

O parâmetro @filter nos permite definir o que queremos buscar (suporta o wildcard % para realizar buscas como LIKE ‘%string%’), enquanto o parâmetro @filter_type permite definir onde queremos buscar essa informação. Os tipos possíveis para o @filter_type são:

• session: Permite pesquisar por uma sessão específica
• program: Permite buscar sessões que esteja utilizando um determinado software client para conexão com o banco de dados
• database: Esse tipo de filtro é utilizado para filtrar queries sendo executadas em um determinado database
• login: Filtro utilizado para filtrar as sessões de um determinado usuário
• host: Utilize esse filtro para visualizar apenas as sessões vindas de um hostname específico

Exemplos do uso de filtros inclusivos (@filter e @filter_type):

Exemplos do uso de filtros exclusivos (@not_filter e @not_filter_type):

Detalhando mais parâmetros dessa SP, vou demonstrar a utilidade dos parâmetros @show_own_spid, @show_system_spids e @show_sleeping_spids.

O parâmetro @show_own_spid (BIT) determina se a própria sessão que está executando a procedure fará parte do resultado final que será mostrado na tela. O valor padrão é 0 (zero), fazendo com que a própria sessão não seja mostrada por padrão.

O parâmetro @show_system_spids (BIT) determina se as sessões internas de sistema do SQL Server serão exibidas no resultado final da SP. O valor padrão é 0 (zero), fazendo com que essas sessões sejam ignoradas.

O parâmetro @show_sleeping_spids (TINYINT) determina se as sessões inativas (sleeping) serão exibidas no resultado final da SP. O valor padrão é 0 (zero), fazendo com que essas sessões sejam ignoradas. O valor 1 exibe todas as sessões inativas que possuem transação aberta e o valor 2 exibe todas as sessões inativas.

Exemplos de uso:

O resultado final dessa stored procedure é muito interessante, e com esses parâmetros vão ficar ainda mais completos. O valor padrão de todos esses parâmetros do tipo BIT é 0 (zero), fazendo com que nenhum deles seja mostrado (a menos que você altere para 1).

@get_full_inner_text

Por padrão, a instrução SQL que é retornada em forma de XML na coluna sql_text é apenas o trecho (batch) que está sendo processado no momento. Ao utilizar esse parâmetro, podemos observar todo o conteúdo do batch que foi enviado para o processamento do SQL Server.

@get_plans

Ao utilizar esse parâmetro com o valor 1, será gerado uma demonstração do plano de execução da query atual de cada sessão retornada por essa SP. Utilizando o valor 2 nesse parâmetro, é gerado o plano de execução de toda a query das sessões. Ao clicar no XML do ResultSet, o Management Studio já exibe o plano de execução dessa query. Fantástico!

@get_outer_command

Esse parâmetro é parecido com o @get_full_inner_text, mas ao invés de substituir o valor da coluna sql_text, ele mantém essa coluna com seu valor padrão (apenas o trecho em execução) e adiciona uma nova coluna chamada sql_command, que contém toda a query que a sessão está executando. Desta forma, temos as duas visões.

@get_transaction_info

Com a utilização desse parâmetro, podemos visualizar a quantidade e volume de dados escritos no log de transação de cada sessão.

@get_task_info

Parâmetro muito interessante para análise de performance, o @get_task_info permite visualizar mais informações sobre as sessões em execução. Ao utilizar o valor 1, podemos visualizar o maiores eventos de wait (que não sejam CXPACKET).

Ao utilizar o parâmetro 2, vamos visualizar o modo completo, que incluí as colunas:

• physicial_io: Mostra o números de leituras/escritas (I/O) físicas no disco
• context_switches: Mostra o número de mudanças de contextos para a conexão ativa. Uma mudança de contexto é quando o kernel do SO troca o processador de uma thread por outra (ex: uma thread de maior prioridade).

  Esse indicador é muito importante para identificar se um processo está usando mais o CPU que os outros processos e impedindo que eles cheguem ao processador. Um índice muito alto, quer dizer que está ocorrendo muita concorrência no processador e ele pode estar sobrecarregado. Um número baixo, significa que algum processo está alocando mais o CPU que deveria, gerando muito tempo de wait (e provavelmente sessões com status Pending e Runnable).

  Os valores esperados devem ser algo abaixo de 2.000 trocas por processador/segundo (alguns DBA’s consideram um valor abaixo de 5.000 como aceitável). Valores muito altos podem estar sendo causados por falhas de alocação de memória física (RAM). Um outro possível agravante é a tecnologia Intel® Hyper-Threading, que em alguns casos pode causar muitas mudanças de contexto por conta da simulação de núcleos virtuais. Caso esteja passando por esse problema, um bom teste é desativar esse recurso na placa mãe do servidor e realizar testes de performance.

• tasks: Numero de tarefas sendo utilizadas pela execução atual.

@get_locks

Parâmetro muito útil para manutenção e identificação de locks na instância. Quando ativado, mostra os objetos reservados de cada requisição, bem como o tipo de bloqueio solicitado pela sessão.

@get_avg_time

Com a utilização desse parâmetro, surge uma nova coluna no resultado final (dd hh:mm:ss.mss (avg)). Essa coluna mostra o tempo médio de execução da query atual em execução por cada sessão. Como vocês podem observar no exemplo, a minha query está em execução há mais de 2h, mas o trecho atual está demorando em média, 79 ms, em um looping de 850.000 iterações. Esse tempo é estimado baseado no plano e históricos de execução.

@get_additional_info

Com a utilização desse parâmetro, será criada uma nova coluna no resultado final chamada “additional_info”, que é um XML com várias informações e definições de comandos SET de cada sessão, conforme o exemplo abaixo:

Caso algum job do SQL Server Agent esteja em execução, a coluna additional_info dessa sessão que o Job abriu terá as informações do Job:

Caso você utilize os parâmetros @get_task_info = 2 e @get_additional_info = 1 e houver um lock em alguma sessão, o XML da coluna “additional_info” dessa sessão que está em lock terá um nó chamado block_info com as informações do block:

@find_block_leaders

Um dos meus parâmetros preferidos, o @find_block_leaders quando ativado, permite analisar cada sessão e contar quantas outras sessões estão em lock aguardando a liberação de objetos por essa sessão. Sabe quando começam eventos de lock na sua instância de produção e você tem que ficar procurando quem que está causando esses locks? Esse parâmetro é a solução para você.

@delta_interval

Esse recurso interessante serve para que você consiga realizar duas coletas de dados em um determinado período de tempo (esse período é o valor do parâmetro, em segundos) e analisar a diferença de alocação de tempdb, leituras, escritas, etc.. entre as duas coletas realizadas.

No exemplo abaixo, especifiquei um intervalo de 10 segundos entre cada coleta. Ao final dos 10 segundos, serão criadas colunas com o sufixo “_delta”, demonstrando a diferença entre a primeira e a segunda execução.

Isso é muito útil para analisar crescimento de alocação de tempdb ou leituras em disco em tempo real. Muitas vezes, analisando apenas a alocação total da sessão e a atual não é o bastante para se estimar o crescimento e alocação de recursos, fazendo deste recurso muito interessante para os DBA’s.

Além de muito completa, essa SP nos permite personalizar de várias formas o resultado final e saída gerada. Vou demonstrar agora, como fazer isso.

@output_column_list

Como já demonstrei em alguns exemplos acima, esse parâmetro serve para definir quais colunas devem fazer parte do resultado final da execução da SP.

@sort_order

Como o próprio nome sugere, esse parâmetro serve para ordenar os resultados conforme a sua necessidade, onde você escolher quais colunas utilizar para a ordenação e qual o critério (asc ou desc).

@format_output

Esse parâmetro serve para alterar a forma de visualização de algumas colunas para um modo mais “humano” de leitura. Com o valor 1, o formato de saída utilizará fontes de comprimento variável. Com o valor 2, o formato de saída utilizará fontes de comprimento fixo.

Sendo sincero, vejo diferenças entre o valor 0 e 1, mas não vejo diferenças entre os valores 1 e 2.

@return_schema e @schema

Esses parâmetros em conjunto servem para gerar o script de criação do resultado da SP. O parâmetro @return_schema quando setado para 1, ao invés de retornar o resultado da execução, gera o script de CREATE TABLE do resultado. Esse script deve ser lido utilizando uma variável de OUTPUT no parâmetro @schema, conforme demonstrado abaixo:

@destination_table

E por último, temos o parâmetro @destination_table. Ele serve para inserir o resultado da execução da SP em uma tabela física, onde podemos armazenar de histórico e consultar quando quisermos.

Para utilizar esse parâmetro, a tabela deve estar criada previamente, pois esse parâmetro vai apenas inserir os dados, não vai criar a tabela. Para obter o comando de CREATE TABLE resultante da execução dessa SP, basta olhar os parâmetros explicados logo acima (@return_schema e @schema) para fazer isso de forma fácil e em poucos segundos.