O que ocorre em um computador quando os dados ultrapassam os limites de um buffer?

Avançar para o conteúdo principal

Não há mais suporte para esse navegador.

Atualize o Microsoft Edge para aproveitar os recursos, o suporte técnico e as atualizações de segurança mais recentes.

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

• Artigo
• 12/12/2022
• 14 minutos para o fim da leitura

Neste artigo

Aplica-se ao: SQL Server

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

Definir desempenho lento de E/S

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 serviços cada solicitação de E/S em média em termos de tempo de relógio. Os contadores específicos do Monitor de Desempenho que medem a latência de E/S no Windows Avg Disk sec/ Readsão e Avg. Disk sec/WriteAvg. Disk sec/Transfer (cumulativos de leituras e gravações).

Em SQL Server, as coisas funcionam da mesma maneira. Normalmente, você verifica se SQL Server gargalos de E/S medidos no horário do relógio (milissegundos). SQL Server faz solicitações de E/S para o sistema operacional chamando as funções Win32WriteFile(), como , ReadFile()e WriteFileGather()ReadFileScatter(). Quando ele posta uma solicitação de E/S, SQL Server vezes a solicitação e relata a duração da solicitação usando tipos de espera. 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
• WRITELOG
• IO_COMPLETION
• ASYNC_IO_COMPLETION
• BACKUPIO

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

Observação

Para fornecer contexto e perspectiva, no mundo da solução de problemas do SQL Server, o Microsoft CSS observou casos em que uma solicitação de E/S levou mais de um segundo e até 15 segundos por sistemas de E/S de transferência precisam de otimização. Por outro lado, o Microsoft CSS viu sistemas em que a taxa de transferência está abaixo de um milissegundo/transferência. Com a tecnologia SSD/NVMe de hoje, as taxas de transferência anunciadas variam em dezenas de microssegundos por transferência. Portanto, a figura de 10 a 15 milissegundos/transferência é um limite muito aproximado que selecionamos com base na experiência coletiva entre o Windows e os engenheiros do SQL Server ao longo dos anos. Normalmente, quando os números ultrapassam esse limite aproximado, SQL Server os usuários começam a ver latência em suas cargas de trabalho e reportá-los. Por fim, a taxa de transferência esperada de um subsistema de E/S é definida pelo fabricante, modelo, configuração, carga de trabalho e, potencialmente, vários outros fatores.

Metodologia

Um fluxograma no final deste artigo descreve a metodologia que o Microsoft CSS usa para abordar problemas de E/S lentos com SQL Server. Não é uma abordagem completa 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 por meio do Azure Data Studio

Opção 2: siga as etapas manualmente

A metodologia é descrita nestas etapas:

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

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 SQL Server espera. Os valores PAGEIOLATCH_*e WRITELOGASYNC_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 consistentemente, um problema de desempenho de E/S existirá e exigirá mais investigação. 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 arquivo no sys.dm_io_virtual_file_stats

Para exibir a latência no nível de arquivo do banco de dados conforme relatado 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 colunas AvgLatency e LatencyAssessment os detalhes da 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 SQL Server logs de erro no 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 MSSQLSERVER_833 seção.

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

Se SQL Server relatório de latência de E/S, consulte os contadores do sistema operacional. Você pode determinar se há um problema de E/S examinando o contador de latência Avg Disk Sec/Transfer. 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 localizar quais volumes hospedam seus arquivos de banco de dados, execute a seguinte consulta em seu 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 em seu volume de 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 forem consistentemente acima de 10 a 15 milissegundos, você precisará examinar ainda mais o problema. 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 SQL Server, o problema será entre o SQL Server e o Gerenciador de Partições, ou seja, os drivers de filtro. O Gerenciador de Partições é uma camada de E/S em que o sistema operacional coleta contadores Perfmon . Para resolver a latência, certifique-se de 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 nos volumes aos quais eles se anexam. Em seguida, você pode pesquisar os nomes de driver 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 estão executando SQL Server.

Evite usar o EFS (Encrypting File System) e a compactação do sistema de arquivos porque 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íncrono exibido como síncrono no Windows.

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

Se SQL Server e o sistema operacional indicarem que o subsistema de E/S está lento, verifique se a causa é o sistema que está sendo sobrecarregado além da capacidade. Você pode verificar a capacidade examinando os contadores de Disk Bytes/SecE/S, Disk Read Bytes/Secou Disk Write Bytes/Sec. Verifique com o administrador do sistema ou com o fornecedor de hardware as especificações de taxa de transferência esperadas para sua SAN (ou outro subsistema de E/S). Por exemplo, você pode enviar por push não mais de 200 MB/s de E/S por meio de um cartão HBA de 2 GB/s ou 2 GB/s de porta dedicada em um comutador SAN. A capacidade de taxa de transferência esperada definida por um fabricante de hardware define como você continua 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: a SQL Server está impulsionando a atividade pesada de E/S?

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

• Ajuste as consultas, por exemplo: índices melhores, estatísticas de atualização, reescrita de consultas e reprojete 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 frequentemente ler novamente do disco, o que reduzirá a atividade de E/S.

Causas

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

• Problemas de hardware:

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

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

  • Problemas de drivers ou firmware

  Fornecedores de hardware e/ou administradores de sistema precisam estar envolvidos neste estágio.

• Problemas de consulta: SQL Server está saturando volumes de disco com solicitações de E/S e está enviando 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 usando í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, design de banco de dados incorreto e design de consulta podem levar a um aumento nos problemas de E/S. Portanto, recriar consultas e, às vezes, tabelas pode ajudar com e/S aprimorada.

• Drivers de filtro: A SQL Server de E/S poderá ser afetada severamente se os drivers de filtro do sistema de arquivos processarem tráfego intenso de E/S. As exclusões de arquivo adequadas da verificação de antivírus e do design correto do driver de filtro por fornecedores de software são recomendadas para evitar o impacto no desempenho de E/S.

• Outros aplicativos: Outro aplicativo no mesmo computador com SQL Server pode saturar o caminho de E/S com solicitações de leitura ou gravação excessivas. Essa situação pode empurrar o subsistema de E/S além dos limites de capacidade e causar lentidão de E/S para 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

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

PAGEIOLATCH_EX

Ocorre quando uma tarefa está aguardando uma trava para 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 em disco. Esperas longas podem indicar problemas com o subsistema de disco.

PAGEIOLATCH_SH

Ocorre quando uma tarefa está aguardando uma trava para 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 com o 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 com o 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 Log grava seu conteúdo temporário no disco. 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 esperas longas são WRITELOG :

• Latência do disco de log de transações: essa é a causa mais comum de WRITELOG esperas. Em geral, a recomendação é manter os dados e os arquivos de 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 giradas convencionais) causará movimentação excessiva da cabeça do disco.

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

• Muitas transações pequenas: embora transações grandes 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 essa 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ções. Quando possível, a group individual update, delete, or insert into a bigger transaction to reduce transaction log flushes and increase performance. Essa operação pode levar a menos WRITELOG esperas.

• Problemas de agendamento fazem com que os threads do Gravador de Log não recebam agendamento rápido o suficiente: antes do SQL Server 2016, um único thread do Gravador de Log realizou todas as gravações de log. Se houver problemas com o agendamento de threads (por exemplo, alta CPU), 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 rapidamente: vários trabalhos de 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 disparadas por problemas de agendamento.

ASYNC_IO_COMPLETION

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

• 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 E/S assíncrona para envio de logs.
• Lendo os dados reais dos arquivos de dados durante um backup de dados.

IO_COMPLETION

Ocorre enquanto aguarda a conclusão das operações de E/S. 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 despejo (verifique o desempenho do armazenamento de tempdb ).
• Ler e gravar spools adiantados no disco (verifique o armazenamento de 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 estiver configurado.
• Copiando páginas para um instantâneo de banco de dados (cópia em gravação).
• Fechamento de arquivo de banco de dados e descompactação de arquivo.

BACKUPIO

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

Recursos adicionais

Recursos adicionais

Neste artigo

O que ocorre num computador quando os dados ultrapassam os limites de um buffer?

Que é uma vulnerabilidade que permite que os criminosos injetem scripts em páginas Web visualizadas pelos utilizadores?

Quais são as duas maneiras de proteger um computador contra malware?

O que é que um rootkit modifica?