Solução de problemas do SQL Server: problemas de E / S de disco

Solução de problemas do SQL Server: problemas de E / S de disco

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br


Neste artigo, discutiremos como resolver problemas de E / S, um ponto muito importante para a solução de problemas do SQL Server. O subsistema de armazenamento é um dos fatores de desempenho significativos para os bancos de dados. Detectar e identificar problemas de E / S no SQL Server pode ser uma tarefa difícil para os administradores de banco de dados (DBAs). Geralmente, as razões subjacentes para os problemas de E / S podem ser:

  • Subsistemas de disco mal configurados ou com defeito
  • Desempenho de disco insuficiente
  • Aplicativos que geram atividades de E / S redundantes
  • Consultas mal projetadas ou não otimizadas

Analisar os sintomas deve ser um princípio importante para esclarecer o motivo subjacente que causa os problemas de E / S no SQL Server. Caso contrário, podemos perder tempo lidando com questões irrelevantes ou discutindo as questões desnecessariamente com os administradores de sistema ou armazenamento. Os tipos de espera fornecem informações muito úteis para a solução de problemas do SQL Server. Os seguintes tipos de espera podem indicar problemas de E / S, mas esses tipos de espera não são suficientes para decidir qualquer problema nos discos.

  • PAGEIOLATCH_ *
  • WRITELOG
  • ASYNC_IO_COMPLETION

Inicialmente, descreveremos brevemente esses tipos de espera e suas relações com os problemas de E / S.

PAGEIOLATCH_ *

O SQL Server reserva uma área na memória para si mesmo, e essa área usa para armazenar dados em cache e indexar páginas para reduzir as atividades do disco. Esta área de memória reservada é chamada Buffer Pool. O mecanismo de trabalho do buffer pool é muito simples; os dados são carregados do disco para a memória quando qualquer pedido é recebido para leitura ou alteração e eles são processados ​​no buffer pool. Os dados são gravados no disco novamente quando são modificados. À luz dessas informações, PAGEIOLATCH_ * ocorre ao transferir dados do disco para o buffer pool. É muito normal detectar alguns PAGEIOLATCH_ *, entretanto, isso indica um problema quando vemos esse tipo de espera com frequência e mais do que os outros tipos de espera. PAGEIOLATCH_ * não indica problemas de disco por si mesmo porque esse tipo de espera pode ocorrer por vários motivos. Por exemplo:

  • Estatísticas desatualizadas ou índices mal projetados podem causar PAGEIOLATCH_ * esperas porque esses tipos de problemas causam atividades de disco redundantes
  • Habilitar a opção CDC (Change Data Capture) pode causar carga de trabalho de E / S extra
  • Memória insuficiente pode causar problemas de PAGEIOLATCH_ * porque o SQL Server não mantém as páginas de dados por tempo suficiente no cache do buffer. O outro sinal desse problema é a métrica de expectativa de vida da página
Leia Também  Implementando esquemas em estrela no Power BI Desktop

WRITELOG

Quando qualquer modificação é executada no banco de dados, o SQL Server grava essa modificação no buffer de log e, em seguida, grava esses dados do buffer no disco. Portanto, esse tipo de espera está relacionado ao disco físico que contém o arquivo de log (ldf). Colocar arquivos de log (ldf) nos discos mais rápidos e dedicados possível será a abordagem certa para superar esses problemas. Ao mesmo tempo, as estatísticas de desempenho dos discos físicos que armazenam arquivos ldf devem ser consideradas quando esse problema ocorre. Os dados de log são gravados no disco sequencialmente e o processo de leitura também é executado sequencialmente. Devido a este princípio de funcionamento, os discos selecionados para os arquivos de log devem funcionar bem para a taxa de transferência sequencial de leitura e gravação junto com a latência mínima.

ASYNC_IO_COMPLETION

Esse tipo de espera ocorre quando o SQL Server processa operações de backup e restauração; no entanto, quando essa operação leva mais tempo do que o normal, pode ser um aviso de problemas de E / S. O BACKUPIO pode ser visto com o ASYNC_IO_COMPLETION para que possamos considerar qualquer problema no disco.

I / O Stalls

O tempo de parada de E / S é um indicador que pode ser usado para detectar problemas de E / S. O dm_io_virtual_file_stats é uma função de gerenciamento dinâmico que fornece informações detalhadas sobre os tempos de paralisação dos dados e arquivos de log para simplificar o processo de solução de problemas do SQL Server. Esta função de gerenciamento dinâmico usa dois parâmetros, o primeiro é a id do banco de dados e o segundo é o número do arquivo do banco de dados.

Podemos executar esta função de gerenciamento dinâmico como a seguir para todos os bancos de dados.

Usando a função dm_io_virtual_file_stats para solução de problemas do SQL Server.

database_id: Esta coluna representa o número de id do banco de dados, e podemos usar sys.databases tabela para obter todos os números de identificação do banco de dados.

file_id: Esta coluna representa o número de id do arquivo, e podemos usar sys.master_files tabela para obter todos os números de identificação do banco de dados.

sample_ms: Esta coluna mostra a duração desde que o servidor foi reiniciado.

Leia Também  SQL 2019 UDF (função definida pelo usuário) inlining - SQL TECH BLOG

num_of_reads: Esta coluna mostra o número de leituras físicas ocorridas desde que o servidor foi reiniciado.

num_of_bytes_reads: Esta coluna mostra a quantidade total de leituras físicas em bytes que ocorreram desde a reinicialização do servidor.

io_stall_read_ms: Esta coluna mostra a latência total para as operações de leitura em um milissegundo.

num_of_writes: Esta coluna mostra o número de gravações que ocorreram desde a reinicialização do servidor.

num_of_bytes_written: Esta coluna mostra a quantidade total de leituras em bytes que ocorreram desde a reinicialização do servidor.

io_stall_write_ms: Esta coluna mostra a latência total para as operações de gravação em um milissegundo.

io_stall: Esta coluna mostra o tempo total de latência para as operações de E / S em um milissegundo.

Os altos tempos de paralisação indicam problemas de E / S e atividades de disco ocupadas. Com a ajuda da consulta a seguir, podemos descobrir a leitura, gravação e latência total dos arquivos de banco de dados para que possamos diagnosticar quaisquer problemas de armazenamento.

Analisando a latência de I / O para solução de problemas do SQL Server

o Latência Total Média coluna representa a latência total sobre os arquivos de banco de dados, e podemos usar a tabela a seguir como referência para avaliar o desempenho do disco em relação à latência.

Excelente

<1 ms

Muito bom

<5 ms

Boa

<5 - 10 ms

Pobre

<10 - 20 ms

Ruim

<20 - 100 ms

Muito mal

<100 ms -500 ms

Horrível

> 500 ms

Usando o Monitor de desempenho para analisar problemas de E / S

Monitor de Desempenho também é conhecido como Perfmon e essa ferramenta ajuda a rastrear métricas sobre recursos de computador ou aplicativos instalados. Particularmente, o Perfmon auxilia na análise e solução de problemas de desempenho do SQL Server porque inclui alguns contadores específicos para o SQL Server além dos contadores de recursos gerais. Podemos entender que o Perfmon desempenha um papel fundamental na solução de problemas do SQL Server de acordo com esta explicação. Quando nos concentramos nos contadores de E / S do Perfmon, alguns deles vêm à tona. Em primeiro lugar, devemos ficar atentos às métricas de latência, pois esses valores podem dizer tudo sobre o desempenho do disco.

Latência é uma métrica de desempenho que mede o intervalo de tempo entre solicitações e respostas para os discos. Podemos usar os seguintes contadores para medir a latência do disco.

  • Média Seg. De disco / transferência contador mostra a latência total, e esses valores devem estar abaixo de 10 milissegundos
  • Média Disco s / leitura contador mostra a latência de leitura
  • Média Disco s / gravação contador mostra a latência de gravação

Medindo a latência do disco com solução de problemas do Perfmon para SQL Server

Quando analisamos a imagem acima, esta caixa tem um desempenho péssimo. A latência média é 0,229 segundos, então é igual a 0,229 * 1000 = 229 milissegundos.

IOPS (Operações de entrada / saída por segundo) é uma métrica de desempenho para os discos que mede o total de operações de entrada e saída executadas pelo disco em um segundo.

  • Leituras de disco / s contador indica gravação IOPS
  • Gravações de disco / s contador indica IOPS lido
  • Transferências de disco / s contador indica o número total de IOPS, este valor é igual à soma Leituras de disco / s e Gravações de disco / s contadores

o Taxa de transferência métrica indica quantos MB podem ser lidos ou gravados pelos subsistemas de disco por segundo. O valor da taxa de transferência será alterado de acordo com nossa infraestrutura de disco, tipos e fornecedores. Por esse motivo, o valor exato não pode ser fornecido para este contador.

  • Bytes de disco / s contador mostra a taxa de transferência total do disco por segundo
  • Bytes de leitura de disco / s contador mostra a capacidade de leitura
  • Bytes de gravação de disco / s contador mostra a capacidade de leitura

Medindo a taxa de transferência do disco com Perfmon para solução de problemas do SQL Server

Conclusão

Neste artigo, aprendemos os métodos básicos que ajudam a diagnosticar e solucionar problemas de E / S do SQL Server. Para superar esse tipo de problema, precisamos observar todas as métricas que podem ajudar a descobrir o problema principal. Entender o problema principal é um ponto muito significativo para a solução de problemas do SQL Server.

Esat Erkec
Últimos posts de Esat Erkec (ver tudo)