Solucionar problemas de desempenho lento do SQL Server causados por problemas de E/S

Aplica-se a: SQL Server

Este artigo fornece diretrizes sobre quais problemas de E/S causam desempenho lento do SQL Server e como solucionar os problemas.

Definir desempenho de E/S lento

Os contadores do monitor de desempenho são usados para determinar o desempenho de E/S lento. Esses contadores medem a rapidez com que o subsistema de E/S atende a cada solicitação de E/S em média em termos de tempo de clock. Os contadores específicos do Monitor de Desempenho que medem a latência de E/S no Windows são Avg Disk sec/ Read, Avg. Disk sec/Writee Avg. Disk sec/Transfer (cumulativo de leituras e gravações).

No SQL Server, as coisas funcionam da mesma maneira. Normalmente, você verifica se o SQL Server relata algum afunilamento de E/S medido em tempo de relógio (milissegundos). O SQL Server faz solicitações de E/S para o sistema operacional chamando as funções Win32, como WriteFile(), ReadFile(), WriteFileGather()e ReadFileScatter(). Quando ele posta uma solicitação de E/S, o SQL Server cronometra a solicitação e relata a duração da solicitação usando tipos de espera. O SQL Server usa tipos de espera para indicar esperas de E/S em locais diferentes no produto. As esperas relacionadas à E/S são:

• / PAGEIOLATCH_SH PAGEIOLATCH_EX
• REGISTRO ESCRITO
• IO_COMPLETION
• ASYNC_IO_COMPLETION
• BACKUP

Se essas esperas excederem de 10 a 15 milissegundos de forma consistente, a E/S será considerada um gargalo.

Metodologia

Um fluxograma no final deste artigo descreve a metodologia que o Microsoft CSS usa para abordar problemas de E/S lentos com o SQL Server. Não é uma abordagem exaustiva ou exclusiva, mas provou ser útil para isolar o problema e resolvê-lo.

Você pode escolher uma das duas opções a seguir para resolver o problema:

Opção 1: executar as etapas diretamente em um notebook usando o Azure Data Studio

Opção 2: siga as etapas manualmente

A metodologia é descrita nestas etapas:

Etapa 1: O SQL Server está relatando E/S lenta?

O SQL Server pode relatar a latência de E/S de várias maneiras:

• Tipos de espera de E/S
• DMV sys.dm_io_virtual_file_stats
• Log de erros ou log de eventos do aplicativo

Tipos de espera de E/S

Determine se há latência de E/S relatada por tipos de espera do SQL Server. Os valores PAGEIOLATCH_*, WRITELOGe ASYNC_IO_COMPLETION os valores de vários outros tipos de espera menos comuns geralmente devem ficar abaixo de 10 a 15 milissegundos por solicitação de E/S. Se esses valores forem maiores de forma consistente, existe um problema de desempenho de E/S e requer uma investigação mais aprofundada. A consulta a seguir pode ajudá-lo a coletar essas informações de diagnóstico em seu sistema:

#replace with server\instance or server for default instance
$sqlserver_instance = "server\instance" 

for ([int]$i = 0; $i -lt 100; $i++)
{
   
  sqlcmd -E -S $sqlserver_instance -Q "SELECT r.session_id, r.wait_type, r.wait_time as wait_time_ms`
                                       FROM sys.dm_exec_requests r JOIN sys.dm_exec_sessions s `
                                        ON r.session_id = s.session_id `
                                       WHERE wait_type in ('PAGEIOLATCH_SH', 'PAGEIOLATCH_EX', 'WRITELOG', `
                                        'IO_COMPLETION', 'ASYNC_IO_COMPLETION', 'BACKUPIO')`
                                       AND is_user_process = 1"

  Start-Sleep -s 2
}

Estatísticas de arquivos no sys.dm_io_virtual_file_stats

Para exibir a latência no nível do arquivo do banco de dados, conforme relatado no SQL Server, execute a seguinte consulta:

#replace with server\instance or server for default instance
$sqlserver_instance = "server\instance" 

sqlcmd -E -S $sqlserver_instance -Q "SELECT   LEFT(mf.physical_name,100),   `
         ReadLatency = CASE WHEN num_of_reads = 0 THEN 0 ELSE (io_stall_read_ms / num_of_reads) END, `
         WriteLatency = CASE WHEN num_of_writes = 0 THEN 0 ELSE (io_stall_write_ms / num_of_writes) END, `
         AvgLatency =  CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 `
                        ELSE (io_stall / (num_of_reads + num_of_writes)) END,`
         LatencyAssessment = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 'No data' ELSE `
               CASE WHEN (io_stall / (num_of_reads + num_of_writes)) < 2 THEN 'Excellent' `
                    WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 2 AND 5 THEN 'Very good' `
                    WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 6 AND 15 THEN 'Good' `
                    WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 16 AND 100 THEN 'Poor' `
                    WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 100 AND 500 THEN  'Bad' `
                    ELSE 'Deplorable' END  END, `
         [Avg KBs/Transfer] =  CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 `
                    ELSE ((([num_of_bytes_read] + [num_of_bytes_written]) / (num_of_reads + num_of_writes)) / 1024) END, `
         LEFT (mf.physical_name, 2) AS Volume, `
         LEFT(DB_NAME (vfs.database_id),32) AS [Database Name]`
       FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS vfs  `
       JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id `
         AND vfs.file_id = mf.file_id `
       ORDER BY AvgLatency DESC"

Examine as AvgLatency colunas e LatencyAssessment para entender os detalhes de latência.

Erro 833 relatado no log de erros ou no log de eventos do aplicativo

Em alguns casos, você pode observar o erro 833 SQL Server has encountered %d occurrence(s) of I/O requests taking longer than %d seconds to complete on file [%ls] in database [%ls] (%d) no log de erros. Você pode verificar os logs de erros do SQL Server em seu sistema executando o seguinte comando do PowerShell:

Get-ChildItem -Path "c:\program files\microsoft sql server\mssql*" -Recurse -Include Errorlog |
   Select-String "occurrence(s) of I/O requests taking longer than Longer than 15 secs"

Além disso, para obter mais informações sobre esse erro, consulte a seção MSSQLSERVER_833 .

Etapa 2: Os contadores Perfmon indicam latência de E/S?

Se o SQL Server relatar latência de E/S, consulte contadores do sistema operacional. Você pode determinar se há um problema de E/S examinando o contador Avg Disk Sec/Transferde latência. O snippet de código a seguir indica uma maneira de coletar essas informações por meio do PowerShell. Ele reúne contadores em todos os volumes de disco: "_total". Altere para um volume de unidade específico (por exemplo, "D:"). Para descobrir quais volumes hospedam seus arquivos de banco de dados, execute a seguinte consulta no SQL Server:

#replace with server\instance or server for default instance
$sqlserver_instance = "server\instance" 
sqlcmd -E -S $sqlserver_instance -Q "SELECT DISTINCT LEFT(volume_mount_point, 32) AS volume_mount_point `
                                     FROM sys.master_files f `
                                     CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) vs"

Reúna Avg Disk Sec/Transfer métricas sobre o volume de sua escolha:

clear
$cntr = 0 

# replace with your server name, unless local computer
$serverName = $env:COMPUTERNAME

# replace with your volume name - C: , D:, etc
$volumeName = "_total"

$Counters = @(("\\$serverName" +"\LogicalDisk($volumeName)\Avg. disk sec/transfer"))

$disksectransfer = Get-Counter -Counter $Counters -MaxSamples 1 
$avg = $($disksectransfer.CounterSamples | Select-Object CookedValue).CookedValue

Get-Counter -Counter $Counters -SampleInterval 2 -MaxSamples 30 | ForEach-Object {
$_.CounterSamples | ForEach-Object {
   [pscustomobject]@{
      TimeStamp = $_.TimeStamp
      Path = $_.Path
      Value = ([Math]::Round($_.CookedValue, 5))
         turn = $cntr = $cntr +1
         running_avg = [Math]::Round(($avg = (($_.CookedValue + $avg) / 2)), 5)  
         
   } | Format-Table
     }
   }

   write-host "Final_Running_Average: $([Math]::Round( $avg, 5)) sec/transfer`n"
  
   if ($avg -gt 0.01)
   {
     Write-Host "There ARE indications of slow I/O performance on your system"
   }
   else
   {
     Write-Host "There is NO indication of slow I/O performance on your system"
   }

Se os valores desse contador estiverem consistentemente acima de 10 a 15 milissegundos, você precisará examinar o problema mais detalhadamente. Picos ocasionais não contam na maioria dos casos, mas certifique-se de verificar novamente a duração de um pico. Se o pico durou um minuto ou mais, é mais um platô do que um pico.

Se os contadores do Monitor de Desempenho não relatarem latência, mas o SQL Server sim, o problema será entre o SQL Server e o Gerenciador de Partições, ou seja, os drivers de filtro. O Gerenciador de Partição é uma camada de E/S em que o sistema operacional coleta contadores Perfmon . Para resolver a latência, garanta as exclusões adequadas de drivers de filtro e resolva problemas de driver de filtro. Os drivers de filtro são usados por programas como software antivírus, soluções de backup, criptografia, compactação e assim por diante. Você pode usar esse comando para listar drivers de filtro nos sistemas e os volumes aos quais eles se conectam. Em seguida, você pode pesquisar os nomes dos drivers e fornecedores de software no artigo Altitudes de filtro alocadas.

fltmc instances

Para obter mais informações, consulte Como escolher o software antivírus a ser executado em computadores que executam o SQL Server.

Evite usar o EFS (Sistema de Arquivos com Criptografia) e a compactação do sistema de arquivos, pois eles fazem com que a E/S assíncrona se torne síncrona e, portanto, mais lenta. Para obter mais informações, consulte o artigo E/S de disco assíncrona aparece como síncrona no Windows .

Etapa 3: O subsistema de E/S está sobrecarregado além da capacidade?

Se o SQL Server e o sistema operacional indicarem que o subsistema de E/S está lento, verifique se a causa é o sistema estar sobrecarregado além da capacidade. Você pode verificar a capacidade examinando os contadores de E/S Disk Bytes/Sec, Disk Read Bytes/Sec, ou Disk Write Bytes/Sec. Verifique com o administrador do sistema ou com o fornecedor de hardware as especificações de throughput esperadas para sua SAN (ou outro subsistema de I/O). Por exemplo, você não pode enviar mais de 200 MB/s de E/S por meio de uma placa HBA de 2 GB/s ou uma porta dedicada de 2 GB/s em um switch SAN. A capacidade de taxa de transferência esperada definida por um fabricante de hardware define como você procede a partir daqui.

clear

$serverName = $env:COMPUTERNAME
$Counters = @(
   ("\\$serverName" +"\PhysicalDisk(*)\Disk Bytes/sec"),
   ("\\$serverName" +"\PhysicalDisk(*)\Disk Read Bytes/sec"),
   ("\\$serverName" +"\PhysicalDisk(*)\Disk Write Bytes/sec")
   )
Get-Counter -Counter $Counters -SampleInterval 2 -MaxSamples 20 | ForEach-Object  {
$_.CounterSamples | ForEach-Object       {
   [pscustomobject]@{
      TimeStamp = $_.TimeStamp
      Path = $_.Path
      Value = ([Math]::Round($_.CookedValue, 3)) }
    }
 }

Etapa 4: O SQL Server está conduzindo a atividade pesada de E/S?

Se o subsistema de E/S estiver sobrecarregado além da capacidade, descubra se o SQL Server é o culpado examinando Buffer Manager: Page Reads/Sec (culpado mais comum) e Page Writes/Sec (muito menos comum) para a instância específica. Se o SQL Server for o driver de E/S principal e o volume de E/S estiver além do que o sistema pode manipular, trabalhe com as equipes de desenvolvimento de aplicativos ou o fornecedor do aplicativo para:

• Ajustar consultas, por exemplo: melhores índices, atualizar estatísticas, reescrever consultas e redesenhar o banco de dados.
• Aumente a memória máxima do servidor ou adicione mais RAM no sistema. Mais RAM armazenará em cache mais dados ou páginas de índice sem reler frequentemente do disco, o que reduzirá a atividade de E/S. O aumento da memória também pode reduzir Lazy Writes/sec, que são acionados por liberações do Lazy Writer quando há uma necessidade frequente de armazenar mais páginas de banco de dados na memória limitada disponível.
• Se você achar que as gravações de página são a fonte de atividade de E/S pesada, examine Buffer Manager: Checkpoint pages/sec se isso se deve a liberações de página massivas necessárias para atender às demandas de configuração do intervalo de recuperação. Você pode usar pontos de verificação indiretos para equilibrar a E/S ao longo do tempo ou aumentar a taxa de transferência de E/S de hardware.

Causas

Em geral, os seguintes problemas são os motivos de alto nível pelos quais as consultas do SQL Server sofrem com a latência de E/S:

• Problemas de hardware:

  • Uma configuração incorreta de SAN (switch, cabos, HBA, armazenamento)

  • Capacidade de I/O excedida (desbalanceada em toda a rede SAN, não apenas no armazenamento back-end)

  • Problemas de drivers ou firmware

  Os fornecedores de hardware e/ou administradores de sistema precisam ser engajados nesta fase.

• Problemas de consulta: o SQL Server está saturando volumes de disco com solicitações de E/S e está empurrando o subsistema de E/S além da capacidade, o que faz com que as taxas de transferência de E/S sejam altas. Nesse caso, a solução é localizar as consultas que estão causando um grande número de leituras lógicas (ou gravações) e ajustar essas consultas para minimizar a E/S de disco - usar índices apropriados é a primeira etapa para fazer isso. Além disso, mantenha as estatísticas atualizadas, pois elas fornecem ao otimizador de consulta informações suficientes para escolher o melhor plano. Além disso, o design incorreto do banco de dados e o design da consulta podem levar a um aumento nos problemas de E/S. Portanto, reprojetar consultas e, às vezes, tabelas pode ajudar com E/S aprimorada.

• Drivers de filtro: a resposta de E/S do SQL Server pode ser severamente afetada se os drivers de filtro do sistema de arquivos processarem tráfego de E/S pesado. Recomenda-se a exclusão adequada de arquivos da verificação antivírus e o design correto do driver de filtro pelos fornecedores de software para evitar o impacto no desempenho de E/S.

• Outros(s) aplicativos: outro aplicativo no mesmo computador com o SQL Server pode saturar o caminho de E/S com solicitações excessivas de leitura ou gravação. Essa situação pode empurrar o subsistema de E/S além dos limites de capacidade e causar lentidão de E/S para o SQL Server. Identifique o aplicativo e ajuste-o ou mova-o para outro lugar para eliminar seu impacto na pilha de E/S.

Representação gráfica da metodologia

Informações sobre tipos de espera relacionados a E/S

Veja a seguir as descrições dos tipos de espera comuns observados no SQL Server quando problemas de E/S de disco são relatados.

PAGEIOLATCH_EX

Ocorre quando uma tarefa está aguardando uma trava por uma página de dados ou índice (buffer) em uma solicitação de E/S. A solicitação de trava está no modo Exclusivo. Um modo exclusivo é usado quando o buffer está sendo gravado no disco. Esperas longas podem indicar problemas no subsistema de disco.

PAGEIOLATCH_SH

Ocorre quando uma tarefa está aguardando uma trava por uma página de dados ou índice (buffer) em uma solicitação de E/S. A solicitação de trava está no modo Compartilhado. O modo Compartilhado é usado quando o buffer está sendo lido do disco. Esperas longas podem indicar problemas no subsistema de disco.

PAGEIOLATCH_UP

Ocorre quando uma tarefa está aguardando uma trava para um buffer em uma solicitação de E/S. A solicitação de trava está no modo de atualização. Esperas longas podem indicar problemas no subsistema de disco.

WRITELOG

Ocorre quando uma tarefa está aguardando a conclusão de uma liberação de log de transações. Uma liberação ocorre quando o Gerenciador de Logs grava seu conteúdo temporário no disco. As operações comuns que causam liberações de log são confirmações de transação e pontos de verificação.

Os motivos comuns para longas esperas WRITELOG são:

• Latência do disco do log de transações: essa é a causa mais comum de WRITELOG esperas. Geralmente, a recomendação é manter os arquivos de dados e log em volumes separados. As gravações de log de transações são gravações sequenciais, enquanto a leitura ou gravação de dados de um arquivo de dados é aleatória. A combinação de dados e arquivos de log em um volume de unidade (especialmente unidades de disco giratórias convencionais) causará movimento excessivo da cabeça do disco.

• Muitos VLFs: muitos arquivos de log virtuais (VLFs) podem causar WRITELOG esperas. Muitos VLFs podem causar outros tipos de problemas, como recuperação longa.

• Muitas transações pequenas: embora grandes transações possam levar ao bloqueio, muitas transações pequenas podem levar a outro conjunto de problemas. Se você não iniciar explicitamente uma transação, qualquer inserção, exclusão ou atualização resultará em uma transação (chamamos isso de transação automática). Se você fizer 1.000 inserções em um loop, haverá 1.000 transações geradas. Cada transação neste exemplo precisa ser confirmada, o que resulta em uma liberação de log de transações e 1.000 liberações de transação. Quando possível, agrupe a atualização, exclua ou insira individualmente em uma transação maior para reduzir as liberações do log de transações e aumentar o desempenho. Essa operação pode levar a menos WRITELOG esperas.

• Problemas de agendamento fazem com que os threads do Gravador de Log não sejam agendados com rapidez suficiente: antes do SQL Server 2016, um único thread do Gravador de Log executava todas as gravações de log. Se houver problemas com o agendamento de threads (por exemplo, CPU alta), o thread do Gravador de Log e as liberações de log poderão ser atrasados. No SQL Server 2016, até quatro threads do Gravador de Log foram adicionados para aumentar a taxa de transferência de gravação de log. Consulte SQL 2016 – Ele é executado mais rápido: vários trabalhos do gravador de log. No SQL Server 2019, até oito threads do Gravador de Log foram adicionados, o que melhora ainda mais a taxa de transferência. Além disso, no SQL Server 2019, cada thread de trabalho regular pode fazer gravações de log diretamente em vez de postar no thread do gravador de log. Com essas melhorias, WRITELOG as esperas raramente seriam acionadas por problemas de agendamento.

ASYNC_IO_COMPLETION

Ocorre quando algumas das seguintes atividades de E/S acontecem:

• O Provedor de Inserção em Massa ("Inserir em Massa") usa esse tipo de espera ao executar E/S.
• Lendo o arquivo Undo no LogShipping e direcionando a E/S assíncrona para o Envio de Logs.
• Ler os dados reais dos arquivos de dados durante um backup de dados.

IO_COMPLETION

Ocorre enquanto se espera as operações de E/S serem concluídas. Esse tipo de espera geralmente envolve E/Ss não relacionadas a páginas de dados (buffers). Os exemplos incluem:

• Leitura e gravação de resultados de classificação/hash de/para o disco durante um derramamento (verifique o desempenho do armazenamento tempdb).
• Leitura e gravação de spools ansiosos no disco (verifique o armazenamento tempdb ).
• A leitura de blocos de log do log de transações (durante qualquer operação que faça com que o log seja lido do disco - por exemplo, recuperação).
• Ler uma página do disco quando o banco de dados ainda não está configurado.
• Copiar páginas para um instantâneo de banco de dados (Copy-on-Write).
• Fechando o arquivo de banco de dados e descompactação de arquivo.

BACKUPIO

Ocorre quando uma tarefa de backup está aguardando dados ou aguardando um buffer para armazenar dados. Esse tipo não é típico, exceto quando uma tarefa está aguardando uma montagem de fita.