Fala pessoal,
Na dica de hoje vou falar sobre a DMV “sys.dm_exec_session_wait_stats”.
Você sabe o que ela faz? Pra que serve? Já usou alguma vez?
Bora pra explicação e exemplos práticos! #gogogo
YouTube – Vídeo:
Segue abaixo um vídeo que gravei no YouTube mostrando na prática a execução dos scripts desse post:
https://www.youtube.com/watch?v=D7mKQ0l3Gik
DMV “sys.dm_exec_session_wait_stats”:
Em primeiro lugar, DMV significa “Dynamic Management Views”. Elas apareceram no SQL Server 2005 e são muito úteis para retornar informações internas do SQL Server que podemos utilizar para fazer um troubleshooting ou analisar a performance de um banco de dados. Existem dezenas de DMVs e devo falar sobre mais algumas em posts futuros.
https://www.mssqltips.com/sqlservertutorial/273/dynamic-management-views/
A cada nova versão do SQL Server surgem novas DMVs. A “sys.dm_exec_session_wait_stats” foi introduzida no SQL Server 2016. Como o próprio nome já diz, ela retorna informações sobre os WAITs de cada sessão aberta do SQL Server. Vamos ver alguns exemplos depois para ficar um pouco mais claro beleza.
Segue abaixo a descrição de cada uma das colunas que são retornadas nessa DMV:
https://www.mssqltips.com/sqlservertip/4078/getting-per-session-wait-statistics-in-sql-server-2016/
DMV “sys.dm_os_wait_stats”:
Já a DMV “sys.dm_os_wait_stats” agrupa os valores dos WAITs e não exibe o ID da sessão. Ela é mais antiga e é compatível com todas as versões anteriores do SQL Server.
Podemos utilizar essa outra DMV quando quisermos analisar os WAITs da instância inteira ao invés de uma sessão específica.
|
1 2 3 |
SELECT * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC |
Exemplo 1 – Terminar sessão:
Vou abrir duas novas sessões (68 e 69) no SQL Server e executar a DMV.
|
1 2 3 |
SELECT * FROM sys.dm_exec_session_wait_stats ORDER BY session_id, wait_type |
Repare que ela retornou várias informações sobre os WAITs de cada sessão aberta no SQL Server. Vou terminar a sessão 69 e ela irá sumir do resultado da DMV.
Agora vamos começar a brincadeira. Vou manter a sessão 68 aberta e executar alguns comandos para gerar diferentes WAITs e mostrar como essa DMV pode ser muito útil no nosso dia a dia. Também vou filtrar para retornar apenas os WAITs dessa sessão nos próximos resultados OK.
|
1 2 3 4 |
SELECT * FROM sys.dm_exec_session_wait_stats where session_id = 68 ORDER BY wait_time_ms DESC |
Exemplo 2: WAITFOR DELAY
Primeiro vamos executar um simples WAITFOR DELAY para a query demorar 5 segundos.
|
1 |
WAITFOR DELAY '00:00:05' |
Repare que as colunas “wait_time_ms” e “max_wait_time_ms” ficaram com 5000 ms e a coluna “waiting_tasks_count” com 1 execução. Vamos executar novamente o comando, mas dessa vez com 10 segundos.
|
1 |
WAITFOR DELAY '00:00:10' |
Agora a coluna “wait_time_ms” somou o valor das duas execuções e ficou com 15001 ms. A coluna “max_wait_time_ms” ficou com o maior valor das duas, que foi o de 10001 ms. Por fim, a coluna “waiting_tasks_count” também foi incrementada e ficou com 2 execuções.
Exemplo 3: LOCKS
Imagine uma query que está demorando alguns minutos ou horas para terminar de executar. Nesse caso, existem vários motivos que podem estar fazendo essa query demorar. Com essa DMV, podemos quebrar esse tempo de espera com os WAITs e talvez identificar um possível problema, por exemplo, LOCKs.
Nesse último exemplo, vou simular um LOCK e ver o que vai aparecer na DMV.
Vamos abrir uma nova conexão, criar uma tabela, abrir uma transação (BEGIN TRAN), fazer um INSERT e manter a transação aberta.
|
1 2 3 4 5 6 7 8 9 |
USE Traces CREATE TABLE Teste_Lock ( id int ) BEGIN TRAN INSERT INTO Teste_Lock VALUES(1) |
Depois disso, na sessão 68 iremos tentar fazer um SELECT nessa tabela, mas será gerado um LOCK, pois existe uma outra transação aberta que está bloqueando essa tabela. Esperei por 7 segundos e cancelei o SELECT. A DMV ficou da seguinte forma:
|
1 2 3 |
USE Traces SELECT * FROM Teste_Lock |
Repare que apareceu um novo WAIT “LCK_M_S” com um tempo de 7 segundos.
Por fim, fiz outro SELECT na tabela para gerar mais um lock, mas dessa vez esperei mais de 1 minuto e cancelei a execução.
O comportamento foi semelhante aos testes anteriores e as colunas foram incrementadas também.
Conclusão:
Espero que você tenha entendido como a DMV “sys.dm_exec_session_wait_stats” pode ser muito útil ao fazer uma análise de performance de uma query específica que ainda está em execução.
Para analisar os WAITs da instância inteira podemos utilizar a DMV “sys.dm_os_wait_stats “.
Referências:
Segue mais alguns posts como referência sobre o assunto:
Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!
Me siga no LinkedIn e YouTube para ficar por dentro das novidades.
Abraço,
Luiz Vitor França Lima
Consultor SQL Server