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

  • Artigo

Aplica-se ao: SQL Server

Este artigo fornece orientações sobre que problemas de E/S causam um desempenho lento do SQL Server e como resolver os problemas.

Definir um desempenho de E/S lento

Os contadores do monitor de desempenho são utilizados para determinar o desempenho de E/S lento. Estes contadores medem a rapidez com que o subsistema de E/S presta serviços a cada pedido 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 são Avg Disk sec/ Read, Avg. Disk sec/Writee Avg. Disk sec/Transfer (cumulativos de leituras e escritas).

No SQL Server, as coisas funcionam da mesma forma. Normalmente, pode ver se o SQL Server comunica estrangulamentos de E/S medidos na hora do relógio (milissegundos). O SQL Server faz pedidos de E/S para o SO ao chamar as funções Win32, como WriteFile(), ReadFile(), WriteFileGather()e ReadFileScatter(). Quando publica um pedido de E/S, o SQL Server compara o pedido e comunica a duração do pedido através de tipos de espera. O SQL Server utiliza tipos de espera para indicar esperas de E/S em locais diferentes no produto. As esperas relacionadas com E/S são:

Se estas esperas excederem 10 a 15 milissegundos de forma consistente, a E/S é considerada um estrangulamento.

Observação

Para fornecer contexto e perspetiva, no mundo da resolução de problemas do SQL Server, o Microsoft CSS observou casos em que um pedido de E/S demorou mais de um segundo e até 15 segundos por transferência– tais sistemas de E/S precisam de otimização. Por outro lado, o Microsoft CSS viu sistemas em que o débito é inferior a um milissegundo/transferência. Com a tecnologia SSD/NVMe atual, as taxas de débito anunciadas variam em dezenas de microssegundos por transferência. Por conseguinte, o valor de 10 a 15 milissegundos/transferências é um limiar muito aproximado que selecionámos com base na experiência coletiva entre engenheiros do Windows e do SQL Server ao longo dos anos. Normalmente, quando os números ultrapassam este limiar aproximado, os utilizadores do SQL Server começam a ver latência nas respetivas cargas de trabalho e a reportá-los. Em última análise, o débito esperado de um subsistema de E/S é definido 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 utiliza 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.

Pode escolher uma das duas opções seguintes para resolver o problema:

Opção 1: executar os passos diretamente num bloco de notas através do Azure Data Studio

Observação

Antes de tentar abrir este bloco de notas, certifique-se de que o Azure Data Studio está instalado no seu computador local. Para instalá-lo, aceda a Saiba como instalar o Azure Data Studio.

Abrir o Bloco de Notas no Azure Data Studio

Opção 2: siga os passos manualmente

A metodologia está descrita nestes passos:

Passo 1: Os relatórios do SQL Server estão lentos?

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

  • Tipos de espera de E/S
  • DMV sys.dm_io_virtual_file_stats
  • Registo de erros ou registo de Eventos da Aplicação
Tipos de espera de E/S

Determine se existe latência de E/S comunicada pelos tipos de espera do SQL Server. Os valores PAGEIOLATCH_*, WRITELOGe ASYNC_IO_COMPLETION e os valores de vários outros tipos de espera menos comuns devem geralmente permanecer abaixo de 10 a 15 milissegundos por pedido de E/S. Se estes valores forem maiores de forma consistente, existe um problema de desempenho de E/S e requer uma investigação mais aprofundada. A seguinte consulta pode ajudá-lo a recolher estas informações de diagnóstico no 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 ficheiros no sys.dm_io_virtual_file_stats

Para ver a latência ao nível do ficheiro da base de dados, conforme comunicado 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"

Observe as AvgLatency colunas e LatencyAssessment para compreender os detalhes da latência.

Erro 833 comunicado no Registo de Erros ou no Registo de Eventos da Aplicação

Em alguns casos, poderá 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 registo de erros. Pode verificar os registos de erros do SQL Server no seu sistema ao executar 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 este erro, veja a secção MSSQLSERVER_833 .

Passo 2: Os Contadores de Perfmon indicam latência de E/S?

Se o SQL Server comunicar latência de E/S, veja Contadores de SO. Pode determinar se existe um problema de E/S ao examinar o contador Avg Disk Sec/Transferde latência . O fragmento de código seguinte indica uma forma de recolher estas informações através do PowerShell. Recolhe contadores em todos os volumes de disco: "_total". Altere para um volume de unidade específico (por exemplo, "D:"). Para localizar os volumes que alojam os seus ficheiros de base 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 no seu volume de eleição:

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 deste contador estiverem consistentemente acima de 10 a 15 milissegundos, terá de analisar o problema mais detalhadamente. Os picos ocasionais não contam na maioria dos casos, mas certifique-se de que verifica novamente a duração de um pico. Se o pico durou um minuto ou mais, é mais um planalto do que um pico.

Se os contadores do Monitor de desempenho não comunicam latência, mas o SQL Server o faz, o problema é entre o SQL Server e o Gestor de Partições, ou seja, controladores de filtro. O Gestor de Partições é uma camada de E/S onde o SO recolhe contadores de Perfmon . Para resolver a latência, confirme as exclusões adequadas dos controladores de filtro e resolva problemas do controlador de filtro. Os controladores de filtro são utilizados por programas como Software antivírus, Soluções de cópia de segurança, Encriptação, Compressão, etc. Pode utilizar este comando para listar os controladores de filtro nos sistemas e os volumes a que se ligam. Em seguida, pode procurar os nomes dos controladores e os fornecedores de software no artigo Altitudes de filtro alocadas .

fltmc instances

Para obter mais informações, veja How to choose antivirus software to run on computers that are running SQL Server (Como escolher software antivírus para ser executado em computadores com o SQL Server).

Evite utilizar o Sistema de Encriptação de Ficheiros (EFS) e a compressão do sistema de ficheiros porque fazem com que a E/S assíncrona se torne síncrona e, portanto, mais lenta. Para obter mais informações, veja o artigo E/S de disco assíncrono é apresentado como síncrono no Windows .

Passo 3: o subsistema de E/S está sobrecarregado para além da capacidade?

Se o SQL Server e o SO indicarem que o subsistema de E/S está lento, verifique se a causa é o sistema estar sobrecarregado para além da capacidade. Pode verificar a capacidade ao observar os contadores Disk Bytes/Secde E/S , Disk Read Bytes/Secou Disk Write Bytes/Sec. Certifique-se de que verifica junto do Administrador de Sistema ou do fornecedor de hardware as especificações de débito esperadas para a SAN (ou outro subsistema de E/S). Por exemplo, não pode emitir mais de 200 MB/s de E/S através de um cartão HBA de 2 GB/seg ou de 2 GB/seg de porta dedicada num comutador SAN. A capacidade de débito esperada definida por um fabricante de hardware define como proceder 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)) }
    }
 }

Passo 4: O SQL Server está a conduzir a atividade de E/S intensiva?

Se o subsistema de E/S estiver sobrecarregado para além da capacidade, descubra se o SQL Server é o responsável por observar Buffer Manager: Page Reads/Sec (o culpado mais comum) e Page Writes/Sec (muito menos comum) para a instância específica. Se o SQL Server for o controlador de E/S principal e o volume de E/S estiver para além do que o sistema pode processar, trabalhe com as equipas de Desenvolvimento de Aplicações ou o fornecedor da aplicação para:

  • Ajuste as consultas, por exemplo: índices melhores, estatísticas de atualização, reescrita de consultas e redesenhar a base de dados.
  • Aumente a memória máxima do servidor ou adicione mais RAM no sistema. Mais RAM colocará em cache mais dados ou páginas de índice sem leitura frequente 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 impulsionados por descargas lentas do Escritor quando existe uma necessidade frequente de armazenar mais páginas de base de dados na memória limitada disponível.
  • Se descobrir que as escritas de página são a origem de uma atividade de E/S intensiva, examine Buffer Manager: Checkpoint pages/sec para ver se se deve a grandes descargas de página necessárias para satisfazer as exigências de configuração do intervalo de recuperação. Pode utilizar pontos de verificação indiretos para reduzir a E/S ao longo do tempo ou aumentar o débito 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 de latência de E/S:

  • Problemas de hardware:

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

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

    • Problemas de controladores ou firmware

    Os fornecedores de hardware e/ou os administradores de sistema têm de estar envolvidos nesta fase.

  • Problemas de consulta: O SQL Server está a saturar volumes de disco com pedidos de E/S e está a emitir o subsistema de E/S para além da capacidade, o que faz com que as taxas de transferência de E/S sejam elevadas. Neste caso, a solução é localizar as consultas que estão a causar um elevado número de leituras lógicas (ou escritas) e otimizar essas consultas para minimizar a utilização de E/S do disco com índices adequados é o primeiro passo para o fazer. Além disso, mantenha as estatísticas atualizadas, uma vez que fornecem ao otimizador de consultas informações suficientes para escolher o melhor plano. Além disso, a estruturação incorreta da base de dados e a estrutura de consultas podem levar a um aumento dos problemas de E/S. Por conseguinte, redesenhar consultas e, por vezes, tabelas pode ajudar a melhorar a E/S.

  • Controladores de filtro: A resposta de E/S do SQL Server pode ser severamente afetada se os controladores de filtro do sistema de ficheiros processarem tráfego de E/S pesado. Recomenda-se exclusões de ficheiros adequadas da análise de antivírus e da conceção correta do controlador de filtro por parte dos fornecedores de software para evitar o impacto no desempenho de E/S.

  • Outras aplicações: Outra aplicação no mesmo computador com o SQL Server pode saturar o caminho de E/S com pedidos de leitura ou escrita excessivos. Esta situação pode emitir o subsistema de E/S para além dos limites de capacidade e causar lentidão de E/S para o SQL Server. Identifique a aplicação e otimize-a ou mova-a para outro local para eliminar o impacto na pilha de E/S.

Representação gráfica da metodologia

Representação visual da metodologia para corrigir problemas de E/S lentos com o SQL Server.

Seguem-se descrições dos tipos de espera comuns observados no SQL Server quando são comunicados problemas de E/S do disco.

PAGEIOLATCH_EX

Ocorre quando uma tarefa está à espera de um bloqueio temporário para uma página de dados ou índice (memória intermédia) num pedido de E/S. O pedido de bloqueio temporário está no modo Exclusivo. É utilizado um modo Exclusivo quando a memória intermédia está a ser escrita no disco. Esperas longas podem indicar problemas com o subsistema de disco.

PAGEIOLATCH_SH

Ocorre quando uma tarefa está à espera de um bloqueio temporário para uma página de dados ou índice (memória intermédia) num pedido de E/S. O pedido de bloqueio temporário está no modo Partilhado. O modo Partilhado é utilizado quando a memória intermédia está a ser lida a partir do disco. Esperas longas podem indicar problemas com o subsistema de disco.

PAGEIOLATCH_UP

Ocorre quando uma tarefa está à espera de um bloqueio temporário para uma memória intermédia num pedido de E/S. O pedido de bloqueio temporário encontra-se no Modo de atualização. Esperas longas podem indicar problemas com o subsistema de disco.

WRITELOG

Ocorre quando uma tarefa está à espera que um registo de transações seja concluído. Ocorre uma remoção de cache quando o Gestor de Registos escreve os respetivos conteúdos temporários no disco. As operações comuns que causam a descarga de registos são consolidações de transações e pontos de verificação.

Os motivos comuns das longas esperas WRITELOG são:

  • Latência do disco de registo de transações: esta é a causa mais comum de WRITELOG esperas. Geralmente, a recomendação é manter os dados e os ficheiros de registo em volumes separados. As escritas do registo de transações são escritas sequenciais ao ler ou escrever dados a partir de um ficheiro de dados é aleatória. Misturar dados e ficheiros de registo num volume de unidade (especialmente unidades de disco rotativos convencionais) causará movimento excessivo do cabeçalho do disco.

  • Demasiados VLFs: demasiados ficheiros de registo virtual (VLFs) podem causar WRITELOG esperas. Demasiadas VLFs podem causar outros tipos de problemas, como a recuperação longa.

  • Demasiadas transações pequenas: embora as transações grandes possam levar ao bloqueio, demasiadas transações pequenas podem levar a outro conjunto de problemas. Se não iniciar explicitamente uma transação, qualquer inserção, eliminação ou atualização resultará numa transação (chamamos a esta transação automática). Se fizer 1000 inserções num ciclo, serão geradas 1000 transações. Cada transação neste exemplo tem de ser consolidada, o que resulta numa descarga do registo de transações e em 1000 descargas de transações. Sempre que possível, agrupe a atualização individual, elimine ou insira numa transação maior para reduzir a descarga do registo de transações e aumentar o desempenho. Esta operação pode levar a menos WRITELOG esperas.

  • Os problemas de agendamento fazem com que os threads do Escritor de Registos não fiquem agendados com rapidez suficiente: antes do SQL Server 2016, um único thread do Escritor de Registos efetuou todas as escritas de registo. Se o agendamento de threads tiver ocorrido (por exemplo, CPU elevada), tanto o thread do Escritor de Registos como as descargas de registos poderão ser atrasadas. No SQL Server 2016, foram adicionados até quatro threads do Escritor de Registos para aumentar o débito de escrita de registos. Veja SQL 2016 – É executado mais rapidamente: várias funções de trabalho de escritor de registos. No SQL Server 2019, foram adicionados até oito threads do Log Writer, o que melhora ainda mais o débito. Além disso, no SQL Server 2019, cada thread de trabalho normal pode efetuar escritas de registo diretamente em vez de publicar no tópico Escritor de registos. Com estas melhorias, WRITELOG as esperas raramente seriam acionadas por problemas de agendamento.

ASYNC_IO_COMPLETION

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

  • O Fornecedor de Inserção em Massa ("Inserir Em Massa") utiliza este tipo de espera ao efetuar E/S.
  • Ler o ficheiro Anular em LogShipping e direcionar E/S Assíncrona para Envio de Registos.
  • Ler os dados reais dos ficheiros de dados durante uma cópia de segurança de dados.

IO_COMPLETION

Ocorre enquanto aguarda a conclusão das operações de E/S. Geralmente, este tipo de espera envolve E/S não relacionada com páginas de dados (memórias intermédias). Os exemplos incluem:

  • Leitura e escrita de resultados de ordenação/hash de/para disco durante um derrame (verifique o desempenho do armazenamento tempdb ).
  • Leitura e escrita de spools ansiosos para o disco (verifique o armazenamento da tempdb ).
  • Ler blocos de registo do registo de transações (durante qualquer operação que faça com que o registo seja lido a partir do disco , por exemplo, recuperação).
  • Ler uma página a partir do disco quando a base de dados ainda não está configurada.
  • Copiar páginas para um instantâneo de base de dados (Copy-on-Write).
  • Fechar o ficheiro da base de dados e a descompressão de ficheiros.

BACKUPIO

Ocorre quando uma tarefa de cópia de segurança está à espera de dados ou está à espera que uma memória intermédia armazene dados. Este tipo não é típico, exceto quando uma tarefa está à espera de uma montagem em banda.