Segredos da sys.dm_os_performance_counters

Olá, tudo bem?

Talvez você já conheça, mas desde o SQL Server 2005 existe uma view muito interessante que é a sys.dm_os_performance_counters. Através dela, temos acesso aos contadores de performance da instância em questão, possibilitando que pelo próprio SQL você consiga acompanhar o desempenho dos contadores. Uma prática que tenho, é guardar rotineiramente o resultado instantâneo desta view, para posteriormente analisa-lo.

Um SELECT nesta view, temos o resultado a seguir, observe que temos duas colunas que devem ser levadas em consideração a cntr_value e cntr_type:

Vamos tomar a linha onde o counter_name é “Lock Waits/sec”, no primeiro momento a conclusão que tomamos é que o valor corrente é 40, correto? Não! Por esse motivo, vamos continuar com este artigo.

O counter_type é uma questão bastante importante a ser analisado, quando o DBA estiver consultando a dm_os_performance_counters, conhecido temos os seguintes valores para esta coluna:

  • 65792 – PERF_COUNTER_LARGE_RAWCOUNT;
  • 272696320: Não documentado
  • 272696576 – PERF_COUNTER_BULK_COUNT;
  • 537003264 – PERF_LARGE_RAW_FRACTION;
  • 1073874176 – PERF_AVERAGE_BULK;
  • 1073939712 – PERF_LARGE_RAW_BASE;

Vamos entender como analisar cada tipo de indicador.

1073939712 – PERF_LARGE_RAW_BASE

Este tipo de contador, exibe o último valor observado e é utilizado como denominador para cálculos adicionais. Utilizamos esse indicador, apenas para calcular outros contadores disponíveis na view. Observaremos que esses em counter_name para esse tipo de indicador você encontrará a palavra base, exemplos: Average Wait Time Base,  Avg Disk Read IO (ms) Base,  Avg Disk Write IO (ms) Base, dentre outros. Veremos o valor deste registro para o cálculo de alguns indicadores no PERF_LARGE_RAW_FRACTION a seguir.

537003264 – PERF_LARGE_RAW_FRACTION;

Este tipo de indicador, está diretamente relacionado com o PERF_LARGE_RAW_BASE, sendo o valor exibido em cntr_value uma fração entre os dois valores. Neste caso, para exibir o valor correto e então realizar alguma análise sobre a informação, precisamos realizar um cálculo adicional. Como exemplo, podemos utilizar o cálculo do Buffer cache hit ratio:

PERF_LARGE_RAW_FRACTION

[sourcecode language=”sql”]
SELECT fraction.*, base.counter_name, base.cntr_type, base.cntr_value,
100 * (fraction.cntr_value/base.cntr_value) as ‘Buffer cache hit ratio (%)’
FROM sys.dm_os_performance_counters fraction, sys.dm_os_performance_counters base
WHERE fraction.counter_name = ‘Buffer cache hit ratio’
AND base.counter_name = ‘Buffer cache hit ratio base’
[/sourcecode]

Ou seja, o valor por si só de 166779 não diz nada, você precisa ver o valor base deste contador, para então realizar o cálculo corretamente.

1073874176 – PERF_AVERAGE_BULK

Esse tipo de contador representa uma métrica média e o cntr_value é cumulativo.Utiliza-se o valor base (PERF_LARGE_RAW_BASE), que também é cumulativo. Segundo a Microsoft, são tomadas duas amostras do valor de PERF_AVERAGE_BULK A1 e A2, bem como o PERF_LARGE_RAW_BASE valor B1 e B2. A diferença entre A1 e A2 e B1 e B2 é calculada. O valor final é então calculado como a proporção das diferenças. O exemplo abaixo ajudará a tornar isso mais claro. A formula seria:

(A2 – A1) / (B2 – B1)

Na prática:

[sourcecode language=”sql”]
DECLARE @A1 int, @A2 int, @B1 int, @B2 int;
— 1ª amostra
SET @A1 = (SELECT cntr_value FROM sys.dm_os_performance_counters c where c.counter_name = ‘Average Latch Wait Time (ms)’);
SET @B1 = (SELECT cntr_value FROM sys.dm_os_performance_counters c where c.counter_name = ‘Average Latch Wait Time Base’);
WAITFOR DELAY ’00:00:10′;
— 2ª amostra
SET @A2 = (SELECT cntr_value FROM sys.dm_os_performance_counters c where c.counter_name = ‘Average Latch Wait Time (ms)’);
SET @B2 = (SELECT cntr_value FROM sys.dm_os_performance_counters c where c.counter_name = ‘Average Latch Wait Time Base’);
–  (A2 – A1) / (B2 – B1)
SELECT @A1 Amostra1, @B1 Base1, @A2 Amostra2, @B2 Base2, (@A2-@A1) / (@B2 – @B1) AS Result
[/sourcecode]

PERF_AVERAGE_BULK

Até este momento, você diria que o Average Latch Wait Time (ms) deste servidor era 481844, quando na verdade o valor atual é 0, incrível! (hahahaha)

272696576 – PERF_COUNTER_BULK_COUNT

Este contador apresenta uma taxa como medida e a coluna cntr_value é um valor cumulativo. Desta forma, para calcula-lo corretamente precisamos obter duas amostras do contador e calcular e dividir pelo intervalo de tempo em segundo entre as duas amostras, vejamos:

[sourcecode language=”sql”]
DECLARE @A1 BIGINT, @A2 BIGINT, @T1 datetime, @T2 datetime;
SET @A1 =  (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = ‘Page lookups/sec’);
SET @T1 = GETDATE();
WAITFOR DELAY ’00:00:10′;
SET @A2 =  (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = ‘Page lookups/sec’);
SET @T2 = GETDATE();
SELECT @A1 Amostra1, @T1 Tempo1, @A2 Amostra2, @T2 Tempo2, (@A2-@A1) / DATEDIFF(second, @T1, @T2) as Result;
[/sourcecode]

PERF_COUNTER_BULK_COUNT

Sendo assim, o correto a afirmar é que no intervalo medido ocorreram apenas 7 Page Lookups e não 40081637 como a primeira vista poderíamos concluir.

65792 – PERF_COUNTER_LARGE_RAWCOUNT

Este é o mais fácil, pois ele exibe o último valor observado não precisando realizar cálculos adicionais, vejamos:

[sourcecode language=”sql”]
SELECT * FROM sys.dm_os_performance_counters as c
where c.cntr_type = 65792
[/sourcecode]

PERF_COUNTER_LARGE_RAWCOUNT

Podemos aqui concluir que a quantidade de usuário conectados são 6, o Page Life Expectancy é 3894 (apesar de que este valor exige uma observação em particular, segundo Paul Randal em Page Life Expectancy isn’t what you think…).

Conclusão

Espero que este artigo possa lhe ajudar em suas análises, fazendo-as corretamente agora!

Por gostar bastante desta view e utilizá-la em meus processos de monitoramento, é importante conheça-la no detalhe para que nenhuma análise venha a ser realizada incorretamente.

Gostou do artigo? Comente e compartilhe!

Vithor Silva | Consultor e Instrutor de SQL Server e Power BI
Microsoft Certified Trainer – MCT


Fontes

Interpreting the counter values from sys.dm_os_performance_counters

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-performance-counters-transact-sql

https://msdn.microsoft.com/library/aa394569.aspx

https://www.sqlshack.com/troubleshooting-sql-server-issues-sys-dm_os_performance_counters/

https://msdn.microsoft.com/en-us/library/cc238313.aspx

Deixe uma resposta