Risolvere i problemi di prestazioni lente di SQL Server causati da problemi di I/O

Si applica a: SQL Server

Questo articolo fornisce indicazioni sui problemi di I/O che causano un rallentamento delle prestazioni di SQL Server e su come risolvere i problemi.

Definire prestazioni di I/O lente

I contatori di Performance Monitor vengono usati per determinare le prestazioni di I/O lente. Questi contatori misurano la velocità media dei servizi del sottosistema di I/O per ogni richiesta di I/O in termini di tempo di clock. I contatori specifici di Performance Monitor che misurano la latenza di I/O in Windows sono Avg Disk sec/ Read, Avg. Disk sec/Writee Avg. Disk sec/Transfer (cumulativi di letture e scritture).

In SQL Server le operazioni funzionano allo stesso modo. In genere, si esamina se SQL Server segnala colli di bottiglia di I/O misurati in tempo di clock (millisecondi). SQL Server effettua richieste di I/O al sistema operativo chiamando le funzioni Win32, ad WriteFile()esempio , ReadFile(), WriteFileGather()e ReadFileScatter(). Quando pubblica una richiesta di I/O, SQL Server moltiplica la richiesta e segnala la durata della richiesta usando i tipi di attesa. SQL Server usa i tipi di attesa per indicare attese di I/O in posizioni diverse del prodotto. Le attese correlate all'I/O sono:

• / PAGEIOLATCH_SHPAGEIOLATCH_EX
• WRITELOG
• IO_COMPLETION
• ASYNC_IO_COMPLETION
• BACKUPIO

Se queste attese superano costantemente i 10-15 millisecondi, l'I/O viene considerato un collo di bottiglia.

Metodologia

Un diagramma di flusso alla fine di questo articolo descrive la metodologia usata da Microsoft CSS per affrontare problemi di I/O lenti con SQL Server. Non si tratta di un approccio esaustivo o esclusivo, ma si è dimostrato utile per isolare il problema e risolverlo.

È possibile scegliere una delle due opzioni seguenti per risolvere il problema:

Opzione 1: eseguire i passaggi direttamente in un notebook tramite Azure Data Studio

Opzione 2: Seguire i passaggi manualmente

La metodologia è illustrata in questi passaggi:

Passaggio 1: SQL Server segnala operazioni di I/O lente?

SQL Server può segnalare la latenza di I/O in diversi modi:

• Tipi di attesa di I/O
• DMV sys.dm_io_virtual_file_stats
• Registro errori o registro eventi dell'applicazione

Tipi di attesa di I/O

Determinare se è presente una latenza di I/O segnalata dai tipi di attesa di SQL Server. I valori PAGEIOLATCH_*, WRITELOGe e ASYNC_IO_COMPLETION di diversi altri tipi di attesa meno comuni devono rimanere in genere inferiori a 10-15 millisecondi per ogni richiesta di I/O. Se questi valori sono più coerenti, esiste un problema di prestazioni di I/O e richiede ulteriori indagini. La query seguente può aiutare a raccogliere queste informazioni di diagnostica nel 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
}

Statistiche dei file in sys.dm_io_virtual_file_stats

Per visualizzare la latenza a livello di file di database come indicato in SQL Server, eseguire la query seguente:

#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"

Esaminare le AvgLatency colonne e LatencyAssessment per comprendere i dettagli della latenza.

Errore 833 segnalato nel registro errori o nel registro eventi dell'applicazione

In alcuni casi, è possibile osservare l'errore 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) nel log degli errori. È possibile controllare i log degli errori di SQL Server nel sistema eseguendo il comando di PowerShell seguente:

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"

Inoltre, per altre informazioni su questo errore, vedere la sezione MSSQLSERVER_833 .

Passaggio 2: I contatori dei perfmon indicano la latenza di I/O?

Se SQL Server segnala la latenza di I/O, fare riferimento ai contatori del sistema operativo. È possibile determinare se si verifica un problema di I/O esaminando il contatore Avg Disk Sec/Transferdella latenza . Il frammento di codice seguente indica un modo per raccogliere queste informazioni tramite PowerShell. Raccoglie i contatori in tutti i volumi del disco: "_total". Passare a un volume di unità specifico, ad esempio "D:". Per trovare i volumi che ospitano i file di database, eseguire la query seguente in 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"

Raccogliere Avg Disk Sec/Transfer le metriche sul volume preferito:

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 i valori di questo contatore sono costantemente superiori a 10-15 millisecondi, è necessario esaminare ulteriormente il problema. I picchi occasionali non sono conteggiati nella maggior parte dei casi, ma assicurarsi di controllare di più la durata di un picco. Se il picco è durato un minuto o più, si tratta più di un altopiano che di un picco.

Se i contatori di Monitoraggio prestazioni non segnalano latenza, ma sql Server lo fa, il problema è tra SQL Server e Gestione partizioni, ovvero i driver di filtro. Gestione partizioni è un livello di I/O in cui il sistema operativo raccoglie i contatori perfmon . Per risolvere la latenza, assicurarsi le esclusioni appropriate dei driver di filtro e risolvere i problemi dei driver di filtro. I driver di filtro vengono usati da programmi come software antivirus, soluzioni di backup, crittografia, compressione e così via. È possibile usare questo comando per elencare i driver di filtro nei sistemi e nei volumi a cui si collegano. È quindi possibile cercare i nomi dei driver e i fornitori di software nell'articolo Altitudini filtro allocato .

fltmc instances

Per altre informazioni, vedere Come scegliere il software antivirus da eseguire nei computer che eseguono SQL Server.

Evitare di usare Encrypting File System (EFS) e la compressione del file system perché causano un I/O asincrono che diventa sincrono e quindi più lento. Per altre informazioni, vedere l'articolo I/O su disco asincrono visualizzato come sincrono in Windows .

Passaggio 3: Il sottosistema di I/O è sovraccarico oltre la capacità?

Se SQL Server e il sistema operativo indicano che il sottosistema di I/O è lento, verificare se la causa è il sovraccarico del sistema oltre la capacità. È possibile controllare la capacità esaminando i contatori Disk Bytes/Secdi I/O , Disk Read Bytes/Seco Disk Write Bytes/Sec. Assicurarsi di verificare con l'amministratore di sistema o il fornitore dell'hardware le specifiche di velocità effettiva previste per la SAN (o un altro sottosistema di I/O). Ad esempio, è possibile eseguire il push di non più di 200 MB/sec di I/O tramite una scheda HBA da 2 GB/sec o una porta dedicata di 2 GB/sec in un commutatore SAN. La capacità di velocità effettiva prevista definita da un produttore di hardware definisce il modo in cui si procede da qui.

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)) }
    }
 }

Passaggio 4: SQL Server sta guidando l'attività di I/O pesante?

Se il sottosistema di I/O viene sovraccaricato oltre la capacità, scoprire se SQL Server è il colpevole esaminando Buffer Manager: Page Reads/Sec (il colpevole più comune) e Page Writes/Sec (molto meno comune) per l'istanza specifica. Se SQL Server è il driver di I/O principale e il volume di I/O è superiore a quello che il sistema può gestire, collaborare con i team di sviluppo applicazioni o il fornitore dell'applicazione per:

• Ottimizzare le query, ad esempio: indici migliori, aggiornare le statistiche, riscrivere le query e riprogettare il database.
• Aumentare la memoria massima del server o aggiungere più RAM nel sistema. Più RAM memorizzano nella cache più dati o pagine di indice senza rieseguire spesso la lettura dal disco, riducendo così l'attività di I/O. L'aumento della memoria può anche ridurre Lazy Writes/sec, che è determinato dallo scaricamento di Lazy Writer quando è spesso necessario archiviare più pagine di database nella memoria limitata disponibile.
• Se si rileva che le scritture di pagina sono l'origine di un'attività di I/O intensa, esaminare Buffer Manager: Checkpoint pages/sec per verificare se è dovuta a uno scaricamento di pagine di grandi dimensioni necessario per soddisfare le richieste di configurazione dell'intervallo di ripristino. È possibile usare checkpoint indiretti per uniformare le operazioni di I/O nel tempo o aumentare la velocità effettiva di I/O hardware.

Cause

In generale, i problemi seguenti sono i motivi generali per cui le query di SQL Server soffrono di latenza di I/O:

• Problemi hardware:

  • Errore di configurazione SAN (commutatore, cavi, HBA, archiviazione)

  • Superamento della capacità di I/O (sbilanciata in tutta la rete SAN, non solo nell'archiviazione back-end)

  • Problemi relativi a driver o firmware

  I fornitori di hardware e/o gli amministratori di sistema devono essere coinvolti in questa fase.

• Problemi di query: SQL Server sta saturando i volumi del disco con le richieste di I/O e sta eseguendo il push del sottosistema di I/O oltre la capacità, il che fa sì che le velocità di trasferimento di I/O siano elevate. In questo caso, la soluzione consiste nel trovare le query che causano un numero elevato di letture logiche (o scritture) e ottimizzare tali query per ridurre al minimo l'I/O del disco usando indici appropriati. Mantieni aggiornate anche le statistiche perché forniscono all'utilità di ottimizzazione query informazioni sufficienti per scegliere il piano migliore. Inoltre, la progettazione errata del database e la progettazione di query possono causare un aumento dei problemi di I/O. Di conseguenza, la riprogettazione di query e talvolta tabelle può essere utile per migliorare l'I/O.

• Driver di filtro: La risposta di I/O di SQL Server può essere gravemente influenzata se i driver di filtro del file system elaborano un traffico di I/O elevato. Le esclusioni di file corrette dall'analisi antivirus e la corretta progettazione del driver di filtro da parte dei fornitori di software sono consigliate per impedire l'impatto sulle prestazioni di I/O.

• Altre applicazioni: Un'altra applicazione nello stesso computer con SQL Server può saturare il percorso di I/O con richieste di lettura o scrittura eccessive. Questa situazione può spingere il sottosistema di I/O oltre i limiti di capacità e causare lentezza di I/O per SQL Server. Identificare l'applicazione e ottimizzarla o spostarla altrove per eliminare l'impatto sullo stack di I/O.

Rappresentazione grafica della metodologia

Informazioni sui tipi di attesa correlati all'I/O

Di seguito sono riportate le descrizioni dei tipi di attesa comuni osservati in SQL Server quando vengono segnalati problemi di I/O del disco.

PAGEIOLATCH_EX

Si verifica quando un'attività è in attesa di un latch per una pagina di dati o di indice (buffer) in una richiesta di I/O. La richiesta di latch è in modalità esclusiva. Quando il buffer viene scritto su disco, viene usata una modalità esclusiva. Attese lunghe possono indicare problemi con il sottosistema del disco.

PAGEIOLATCH_SH

Si verifica quando un'attività è in attesa di un latch per una pagina di dati o di indice (buffer) in una richiesta di I/O. La richiesta di latch è in modalità condivisa. La modalità Condivisa viene usata quando il buffer viene letto dal disco. Attese lunghe possono indicare problemi con il sottosistema del disco.

PAGEIOLATCH_UP

Si verifica quando un'attività è in attesa di un latch per un buffer in una richiesta di I/O. La richiesta di latch è in modalità di aggiornamento. Attese lunghe possono indicare problemi con il sottosistema del disco.

WRITELOG

Si verifica quando un'attività è in attesa del completamento di uno scaricamento del log delle transazioni. Si verifica uno scaricamento quando Gestione log scrive il contenuto temporaneo su disco. Le operazioni comuni che causano lo scaricamento dei log sono i commit delle transazioni e i checkpoint.

I motivi comuni per le attese WRITELOG lunghe sono:

• Latenza del disco del log delle transazioni: questa è la causa più comune di WRITELOG attese. In genere, è consigliabile mantenere i file di dati e di log in volumi separati. Le scritture del log delle transazioni sono scritture sequenziali mentre la lettura o la scrittura di dati da un file di dati è casuale. La combinazione di dati e file di log in un volume di unità (in particolare le unità disco rotanti convenzionali) causerà un eccessivo spostamento della testa del disco.

• Troppi file VLF: un numero eccessivo di file di log virtuali (VLF) può causare WRITELOG attese. Un numero eccessivo di VLF può causare altri tipi di problemi, ad esempio il ripristino a lungo termine.

• Troppe transazioni di piccole dimensioni: anche se le transazioni di grandi dimensioni possono causare il blocco, un numero eccessivo di transazioni di piccole dimensioni può causare un altro set di problemi. Se non si inizia una transazione in modo esplicito, qualsiasi inserimento, eliminazione o aggiornamento genererà una transazione (questa transazione automatica viene chiamata). Se si esegue 1.000 inserimenti in un ciclo, verranno generate 1.000 transazioni. Ogni transazione in questo esempio deve eseguire il commit, il che comporta uno scaricamento del log delle transazioni e 1.000 scaricamenti delle transazioni. Quando possibile, raggruppare singoli aggiornamenti, eliminazioni o inserimenti in una transazione più grande per ridurre lo scaricamento del log delle transazioni e aumentare le prestazioni. Questa operazione può comportare un minor numero WRITELOG di attese.

• I problemi di pianificazione fanno sì che i thread del writer di log non vengano pianificati abbastanza velocemente: prima di SQL Server 2016, un singolo thread di Log Writer eseguiva tutte le scritture del log. Se si verificano problemi con la pianificazione dei thread (ad esempio, cpu elevata), sia il thread di Log Writer che gli scaricamenti del log potrebbero subire ritardi. In SQL Server 2016 sono stati aggiunti fino a quattro thread di Log Writer per aumentare la velocità effettiva di scrittura dei log. Vedere SQL 2016 : viene semplicemente eseguito più velocemente: più ruoli di lavoro del writer di log. In SQL Server 2019 sono stati aggiunti fino a otto thread log writer, che migliorano ancora di più la velocità effettiva. Inoltre, in SQL Server 2019, ogni thread di lavoro normale può eseguire operazioni di scrittura dei log direttamente anziché pubblicare nel thread del writer di log. Con questi miglioramenti, WRITELOG le attese vengono raramente attivate da problemi di pianificazione.

ASYNC_IO_COMPLETION

Si verifica quando si verificano alcune delle seguenti attività di I/O:

• Il provider di inserimento bulk ("Inserisci bulk") usa questo tipo di attesa durante l'esecuzione di operazioni di I/O.
• Lettura del file Disassocia in LogShipping e indirizzamento di I/O asincrono per log shipping.
• Lettura dei dati effettivi dai file di dati durante un backup dei dati.

IO_COMPLETION

Si verifica durante l'attesa del completamento delle operazioni di I/O. Questo tipo di attesa coinvolge in genere I/O non correlati alle pagine di dati (buffer). Alcuni esempi:

• Lettura e scrittura dei risultati di ordinamento/hash da/verso il disco durante una fuoriuscita (controllare le prestazioni dell'archiviazione tempdb ).
• Lettura e scrittura di spool eager su disco (controllare l'archiviazione tempdb ).
• Lettura dei blocchi di log dal log delle transazioni (durante qualsiasi operazione che causa la lettura del log dal disco, ad esempio il ripristino).
• Lettura di una pagina dal disco quando il database non è ancora configurato.
• Copia di pagine in uno snapshot di database (Copia in scrittura).
• Chiusura della decompressione di file e file di database.

BACKUPIO

Si verifica quando un'attività di backup è in attesa di dati o è in attesa di un buffer per archiviare i dati. Questo tipo non è tipico, tranne quando un'attività è in attesa di un montaggio su nastro.