Medir latência de E/S do SQL Server por intervalos

Um arquivo com média de leitura de quatro milissegundos desde a inicialização pode apresentar um problema grave durante o incidente atual. Da mesma forma, uma manutenção antiga e lenta pode manter a média alta por muito tempo. Contadores de E/S por arquivo são cumulativos; uma amostra isolada raramente responde ao que acontece agora.

Meça um intervalo definido e associe-o à carga ativa naquele momento. Separe leituras de dados, gravações de dados e gravações de log. Os padrões e os efeitos sobre o tempo de resposta são diferentes.

Calcular diferenças antes de dividir

O diagnóstico de leitura captura duas amostras com cinco segundos de intervalo e cria somente uma tabela temporária local. Use uma conexão com as permissões de diagnóstico exigidas pela versão.

SELECT database_id, file_id, sample_ms, num_of_reads, num_of_writes,
       io_stall_read_ms, io_stall_write_ms
INTO #IoBefore
FROM sys.dm_io_virtual_file_stats(NULL, NULL);

WAITFOR DELAY '00:00:05';

SELECT DB_NAME(a.database_id) AS DatabaseName,
       mf.name AS LogicalFileName, mf.type_desc,
       a.sample_ms - b.sample_ms AS SampleMilliseconds,
       a.num_of_reads - b.num_of_reads AS Reads,
       a.num_of_writes - b.num_of_writes AS Writes,
       CAST(1.0 * (a.io_stall_read_ms - b.io_stall_read_ms)
            / NULLIF(a.num_of_reads - b.num_of_reads, 0)
            AS decimal(12,2)) AS AverageReadMs,
       CAST(1.0 * (a.io_stall_write_ms - b.io_stall_write_ms)
            / NULLIF(a.num_of_writes - b.num_of_writes, 0)
            AS decimal(12,2)) AS AverageWriteMs
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS a
JOIN #IoBefore AS b
  ON b.database_id = a.database_id AND b.file_id = a.file_id
JOIN sys.master_files AS mf
  ON mf.database_id = a.database_id AND mf.file_id = a.file_id
WHERE a.sample_ms > b.sample_ms
  AND a.num_of_reads >= b.num_of_reads
  AND a.num_of_writes >= b.num_of_writes
  AND a.io_stall_read_ms >= b.io_stall_read_ms
  AND a.io_stall_write_ms >= b.io_stall_write_ms
ORDER BY DatabaseName, mf.type_desc, LogicalFileName;
DROP TABLE #IoBefore;

AverageReadMs divide o aumento da espera de leitura pelo aumento das leituras concluídas. AverageWriteMs faz o equivalente para gravações. A multiplicação por 1.0 evita divisão inteira. NULL significa que nenhuma operação correspondente foi observada, não armazenamento com latência zero.

Observe a quantidade junto à média. Cem milissegundos em uma única leitura têm significado diferente de cem milissegundos em milhares. Cinco segundos são uma demonstração curta, não uma janela universalmente representativa. Colete várias amostras durante a lentidão e em um período normal comparável.

Os filtros rejeitam quedas óbvias dos contadores e intervalos inválidos. Eles não tornam confiáveis comparações atravessando reinícios, substituição de arquivos ou mudanças no ciclo de vida da base. Um coletor persistente deve registrar início da instância e identidade do arquivo e descartar essas quebras. Não zere contadores compartilhados apenas para facilitar a conta.

Médias também escondem a distribuição. Muitas operações rápidas e poucas muito lentas podem produzir o mesmo número que operações uniformemente medianas. Os contadores não fornecem percentis precisos nem apontam a consulta responsável por cada espera.

Relacionar latência e sintomas

Leituras lentas podem acompanhar PAGEIOLATCH, mas leituras físicas excessivas podem surgir de consultas ruins ou baixa permanência em cache. Uma consulta que lê dados desnecessários pressiona até armazenamento saudável. Examine acesso e volume antes de concluir que discos rápidos são a única solução.

Gravações lentas de log podem aumentar o tempo de COMMIT e aparecer com WRITELOG. Muitas transações pequenas podem depender de baixa latência mesmo com pouca transferência. Gravações de arquivos de dados têm outra relação com checkpoints e limpeza em segundo plano. Não una todos os caminhos em uma única nota.

Acrescente taxa de operações, bytes transferidos e duração da aplicação. Um limite de transferência saturado é diferente de poucas operações esperando individualmente. Correlacione sistema operacional e plataforma no mesmo intervalo, não com médias diárias desconectadas.

Diferencie PAGEIOLATCH de PAGELATCH. A segunda espera envolve latch em memória, não E/S. Nomes semelhantes não representam a mesma causa; trocar armazenamento não resolve uma página de alocação em memória disputada.

Escolher uma mudança fundamentada

Identifique arquivos e cargas que mudaram no início do incidente. Um backup, manutenção de índices, carga ou relatório agora simultâneos podem criar contenção nova. Infraestrutura virtual compartilhada também pode limitar recursos fora do banco.

Evite um limiar universal de milissegundos aceitáveis. Requisito do serviço, tamanho da operação, filas, arquitetura e caminho de durabilidade do log importam. Compare com uma condição saudável da própria carga e com as capacidades esperadas da plataforma.

Se uma reescrita reduz leituras, execute o mesmo relatório e observe duração e trabalho total. Se a configuração de armazenamento muda, valide também commits e resposta da aplicação. Uma média menor é útil quando explica um benefício real.

Preserve amostras, horários, contexto e ação escolhida. Isso permite repetir o diagnóstico no próximo incidente e evita transformar uma média cumulativa antiga em um julgamento permanente e sem evidência sobre o armazenamento.

Referências técnicas: Microsoft Learn: File I/O statistics · Microsoft Learn: Troubleshoot slow I/O.