Problemen met trage SQL Server-prestaties oplossen die worden veroorzaakt door I/O-problemen

Van toepassing op: SQL Server

Overzicht

Dit artikel bevat een stapsgewijze methodologie voor het vaststellen en oplossen van trage SQL Server prestaties die worden veroorzaakt door I/O-knelpunten van schijf. Hierin wordt uitgelegd hoe u I/O-latentie kunt identificeren met behulp van SQL Server wachttypen, de sys.dm_io_virtual_file_stats dynamische beheerweergave en Windows Performance Monitor tellers. U leert hoe u kunt bepalen of het I/O-subsysteem wordt overweldigd, of SQL Server het primaire stuurprogramma van I/O is, en welke hardware, query, filterstuurprogramma of oorzaken op toepassingsniveau moeten worden onderzocht. Gebruik deze richtlijnen om de bron van I/O-latentie te isoleren en de juiste oplossing toe te passen voor on-premises SQL Server implementaties.

Trage I/O-prestaties definiëren

Prestatiemeteritems worden gebruikt om trage I/O-prestaties te bepalen. Deze tellers meten hoe snel het I/O-subsystemen gemiddeld elke I/O-aanvraag afhandelt in termen van kloktijd. De specifieke prestatiemeteritems die I/O-latentie in Windows meten, zijn Avg Disk sec/ Read, Avg. Disk sec/Writeen Avg. Disk sec/Transfer (cumulatief van zowel lees- als schrijfbewerkingen).

In SQL Server werken dingen op dezelfde manier. Doorgaans kijkt u of SQL Server eventuele I/O-knelpunten rapporteert die zijn gemeten in de kloktijd (milliseconden). SQL Server doet I/O-aanvragen naar het besturingssysteem door de Win32-functies zoals WriteFile(), ReadFile(), WriteFileGather(), en ReadFileScatter() aan te roepen. Wanneer SQL Server een I/O-verzoek indient, meet het de tijd van het verzoek en rapporteert het de duur van het verzoek met wachttypen. SQL Server gebruikt wachttypen om I/O-wachttijden op verschillende plaatsen in het product aan te geven. De I/O-gerelateerde wachttijden zijn:

Als deze wachttijden consistent langer zijn dan 10-15 milliseconden, wordt I/O beschouwd als een knelpunt.

Notitie

Om context te bieden, heeft Microsoft SQL Server systemen waargenomen waarbij een I/O-aanvraag één seconde duurde, en zo hoog als 15 seconden per overdracht. Voor dergelijke I/O-systemen is optimalisatie vereist. Daarentegen heeft Microsoft ook systemen gezien waarbij de doorvoer lager is dan één milliseconde per overdracht. Met de huidige SSD- en NVMe-technologie, geadverteerde doorvoersnelheden variëren in tientallen microseconden per overdracht.

De 10-15 milliseconden per overdrachtscijfer is een geschatte drempelwaarde die is geselecteerd op basis van collectieve ervaring tussen Windows en SQL Server technici. Wanneer getallen deze drempelwaarde overschrijden, zien SQL Server gebruikers latentie in hun werkbelastingen zien. Uiteindelijk wordt de verwachte doorvoer van een I/O-subsysteem gedefinieerd door de fabrikant, het model, de configuratie, de workload en andere factoren.

Methodologie voor het isoleren van I/O-knelpunten

Een stroomdiagram aan het einde van dit artikel beschrijft de methodologie die vaak wordt gebruikt om trage I/O-problemen met SQL Server te benaderen. Deze methodologie is niet volledig of exclusief, maar is handig voor het isoleren en oplossen van het probleem.

De methodologie wordt in deze stappen beschreven:

Stap 1: Rapporteert SQL Server trage I/O?

SQL Server kan I/O-latentie op verschillende manieren rapporteren:

  • I/O-wachttypen
  • DMV sys.dm_io_virtual_file_stats
  • Foutenlogboek of toepassingslogboek

I/O-wachttypen

Controleer of SQL Server wachttypen rapport-I/O-latentie rapporteren. De waarden PAGEIOLATCH_*, WRITELOGen ASYNC_IO_COMPLETION, samen met de waarden van verschillende andere minder gangbare wachttypen, moeten over het algemeen onder de 10-15 milliseconden per I/O-aanvraag blijven. Als deze waarden dit bereik consistent overschrijden, bestaat er een I/O-prestatieprobleem en is verder onderzoek vereist. Met de volgende query kunt u deze diagnostische gegevens op uw systeem verzamelen:

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

Bestandsstatistieken in sys.dm_io_virtual_file_stats

Als u de latentie op databasebestandsniveau wilt weergeven zoals gerapporteerd in SQL Server, voert u de volgende query uit:

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

Bekijk de AvgLatency en LatencyAssessment kolommen om inzicht te hebben in de latentiedetails.

Fout 833 gerapporteerd in foutenlogboek of toepassingslogboek

In sommige gevallen ziet u mogelijk fout 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) in het foutenlogboek. U kunt foutenlogboeken van SQL Server op uw systeem controleren door de volgende PowerShell-opdracht uit te voeren:

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"

Zie de sectie MSSQLSERVER_833 voor meer informatie over deze fout.

Stap 2: Geven prestatiemeteritems de I/O-latentie aan?

Als SQL Server I/O-latentie rapporteert, raadpleegt u de prestatiemeteritems van het besturingssysteem. U kunt bepalen of er een I/O-probleem is door de latentiemeteritem Avg Disk Sec/Transferte onderzoeken. Het volgende codefragment geeft één manier aan om deze informatie te verzamelen via PowerShell. Het verzamelt tellers op alle schijfvolumes: '_total'. Ga naar een specifiek stationvolume (bijvoorbeeld 'D:'). Als u wilt zoeken naar welke volumes uw databasebestanden worden gehost, voert u de volgende query uit in uw 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"

Verzamel Avg Disk Sec/Transfer metrische gegevens over uw gewenste volume:

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

Als de waarden van deze teller consistent hoger zijn dan 10-15 milliseconden, moet u verder onderzoeken. Af en toe worden pieken meestal niet meegerekend, maar zorg dat u de duur van een piek dubbel controleert. Als de piek één minuut of meer duurde, is het meer van een plateau dan een piek.

Als de Performance Monitor tellers geen latentie rapporteren, maar SQL Server dat wel doet, is het probleem tussen SQL Server en Partition Manager, dat wil gezegd filterstuurprogramma's. Partition Manager is een I/O-laag waarin het besturingssysteem prestatiemeteritems verzamelt. Als u de latentie wilt oplossen, moet u de juiste uitsluitingen van filterstuurprogramma's garanderen en problemen met het filterstuurprogramma oplossen. Programma's zoals antivirussoftware, back-upoplossingen, versleuteling, compressie en vergelijkbare software gebruiken filterstuurprogramma's. Gebruik deze opdracht om filterstuurprogramma's op de systemen en de volumes waaraan ze zijn gekoppeld weer te geven. Zoek vervolgens de namen van stuurprogramma's en softwareleveranciers op in het artikel Toegewezen filterhoogten .

fltmc instances

Zie Antivirussoftware kiezen die moet worden uitgevoerd op computers waarop SQL Server wordt uitgevoerd voor meer informatie.

Vermijd het gebruik van het Encrypting File System (EFS) en bestandssysteemcompressie omdat ze asynchrone I/O synchroon maken en daarom langzamer laten verlopen. Zie voor meer informatie het artikel Asynchrone schijf-I/O lijkt synchroon op Windows.

Stap 3: Is het I/O-subsysteem overbelast boven de capaciteit?

Als SQL Server en het besturingssysteem aangeven dat het I/O-subsysteem traag is, controleert u of het systeem zijn capaciteit overschrijdt. U kunt de capaciteit controleren door te kijken naar I/O-tellers Disk Bytes/Sec, Disk Read Bytes/Secof Disk Write Bytes/Sec. Neem contact op met uw systeembeheerder of hardwareleverancier voor de verwachte doorvoerspecificaties voor uw SAN (of een ander I/O-subsysteem). U kunt bijvoorbeeld maximaal 200 MB per seconde aan I/O overdragen via een HBA-kaart van 2 GB per seconde of een toegewezen poort van 2 GB per seconde op een SAN-switch. De verwachte doorvoercapaciteit die door een hardwarefabrikant is gedefinieerd, bepaalt hoe u vanaf hier verder gaat.

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

Stap 4: Wordt SQL Server gebruikt om de zware I/O-activiteit te stimuleren?

Als het I/O-subsysteem boven de capaciteit wordt belast, controleer dan of SQL Server de boosdoener is door naar Buffer Manager: Page Reads/Sec te kijken (meest voorkomende boosdoener) en Page Writes/Sec (veel minder vaak) voor de specifieke instantie. Als SQL Server het belangrijkste I/O-stuurprogramma is en het I/O-volume verder gaat dan wat het systeem kan verwerken, neemt u contact op met de ontwikkelteams van toepassingen of de leverancier van de toepassing voor:

  • Optimaliseer queries, bijvoorbeeld: betere indexen, statistieken bijwerken, queries herschrijven en de database herontwerpen.
  • Verhoog het maximale servergeheugen of voeg meer RAM-geheugen toe aan het systeem. Meer RAM-geheugen slaat meer gegevens of indexpagina's op zonder regelmatig opnieuw te lezen van schijf, waardoor I/O-activiteit wordt verminderd. Meer geheugen kan ook Lazy Writes/sec verminderen, wat wordt veroorzaakt door Lazy Writer flushes wanneer er regelmatig behoefte is om meer databasepagina's op te slaan in het beperkte beschikbare geheugen.
  • Als u merkt dat schrijfbewerkingen van pagina's de bron zijn van zware I/O-activiteit, controleert u Buffer Manager: Checkpoint pages/sec of dit te wijten is aan massale pagina flushes die nodig zijn om te voldoen aan de herstelintervalconfiguratie-eisen. U kunt indirecte controlepunten gebruiken om I/O in de loop van de tijd te gelijkmatiger te maken of de I/O-doorvoer van hardware te verhogen.

Veelvoorkomende hoofdoorzaken van SQL Server I/O-latentie

Over het algemeen zijn de volgende problemen de belangrijkste redenen waarom SQL Server-query's last hebben van I/O-latentie:

  • Hardwareproblemen:

    • Een ONJUISTE SAN-configuratie (switch, kabels, HBA, opslag)

    • I/O-capaciteit overschreden (onevenwichtig in het hele SAN-netwerk, niet alleen back-endopslag)

    • Problemen met stuurprogramma's of firmware

    Neem in deze fase contact op met hardwareleveranciers en systeembeheerders.

  • Queryproblemen: SQL Server verzadiging van schijfvolumes met I/O-aanvragen en pusht het I/O-subsysteem buiten de capaciteit, waardoor I/O-overdrachtssnelheden hoog zijn. Zoek in dit geval de query's die een groot aantal logische leesbewerkingen (of schrijfbewerkingen) veroorzaken en stem deze query's af om de I/O van de schijf te minimaliseren. Het gebruik van de juiste indexen is de eerste stap om dat te doen. Zorg er ook voor dat statistieken worden bijgewerkt wanneer ze de queryoptimalisatie voorzien van voldoende informatie om het beste plan te kiezen. Onjuist databaseontwerp en queryontwerp kunnen leiden tot een toename van I/O-problemen. Daarom kan het opnieuw ontwerpen van query's en soms tabellen helpen bij het verbeteren van I/O.

  • Filterstuurprogramma's: Als stuurprogramma's voor bestandssysteemfilters intensief I/O-verkeer verwerken, kunnen ze de SQL Server I/O-reactie ernstig beïnvloeden. Als u de invloed op I/O-prestaties wilt voorkomen, sluit u bestanden goed uit van antivirusscans en zorgt u ervoor dat het juiste filterstuurprogrammaontwerp door softwareleveranciers wordt gebruikt.

  • Andere toepassingen: Een andere toepassing op dezelfde computer met SQL Server kan het I/O-pad overbelasten met overmatige lees- of schrijfaanvragen. Deze situatie kan het I/O-subsysteem buiten de capaciteitslimieten duwen en I/O-traagheid veroorzaken voor SQL Server. Identificeer de toepassing en stem deze af of verplaats deze ergens anders om de impact op de I/O-stack te elimineren.

Grafische weergave van de methodologie

Visuele weergave van de methodologie om trage I/O-problemen met SQL Server op te lossen.

De volgende beschrijvingen hebben betrekking op de veelvoorkomende wachttypen die worden weergegeven in SQL Server wanneer I/O-problemen met schijf worden gerapporteerd.

PAGEIOLATCH_EX

Treedt op wanneer een taak wacht op een vergrendeling voor een gegevens- of indexpagina (buffer) in een I/O-aanvraag. De vergrendelingsaanvraag bevindt zich in de modus Exclusief. Er wordt een exclusieve modus gebruikt wanneer de buffer naar de schijf wordt geschreven. Lange wachttijden kunnen duiden op problemen met het schijfsubsysteem.

PAGEIOLATCH_SH

Treedt op wanneer een taak wacht op een vergrendeling voor een gegevens- of indexpagina (buffer) in een I/O-aanvraag. De vergrendelingsaanvraag bevindt zich in de modus Gedeeld. De modus Gedeeld wordt gebruikt wanneer de buffer wordt gelezen van de schijf. Lange wachttijden kunnen duiden op problemen met het schijfsubsysteem.

PAGEIOLATCH_UP

Wanneer een taak wacht op een vergrendeling voor een buffer in een I/O-aanvraag, treedt dit op. De vergrendelingsaanvraag bevindt zich in de updatemodus. Lange wachttijden kunnen duiden op problemen met het schijfsubsysteem.

WRITELOG

Treedt op wanneer een taak wacht totdat een transactielogboek is leeggemaakt. Een flush treedt op wanneer de Log Manager zijn tijdelijke inhoud naar de schijf schrijft. Veelvoorkomende bewerkingen die ertoe leiden dat logboeken worden leeggemaakt, zijn transactiedoorvoeringen en controlepunten.

Veelvoorkomende redenen voor lange wachttijden WRITELOG zijn:

  • Latentie van transactielogboekschijf: dit is de meest voorkomende oorzaak van WRITELOG wachttijden. Over het algemeen is het raadzaam om de gegevens en logboekbestanden op afzonderlijke volumes te bewaren. Schrijfbewerkingen voor transactielogboeken zijn sequentiële schrijfbewerkingen, terwijl het lezen of schrijven van gegevens uit een gegevensbestand willekeurig is. Het combineren van gegevens en logboekbestanden op één schijfvolume (met name conventionele draaiende schijfstations) veroorzaakt overmatige verplaatsing van de schijfkop.

  • Te veel VLF's: te veel virtuele logboekbestanden (VLF's) kunnen wachttijden veroorzaken WRITELOG . Te veel VLF's kunnen andere soorten problemen veroorzaken, zoals een lange herstelperiode.

  • Te veel kleine transacties: hoewel grote transacties tot blokkeren kunnen leiden, kunnen te veel kleine transacties leiden tot een andere set problemen. Als u een transactie niet expliciet start, resulteert een invoeg-, verwijder- of updatebewerking in een transactie (deze automatische transactie wordt genoemd). Als u 1000 invoegingen in een lus uitvoert, worden er 1000 transacties gegenereerd. Elke transactie in dit voorbeeld moet worden doorgevoerd, wat resulteert in een leeggemaakt transactielogboek en 1000 transacties worden leeggemaakt. Groepeer indien mogelijk afzonderlijke updates, verwijder of voeg deze in een grotere transactie in om het leegmaken van transactielogboeken te verminderen en de prestaties te verbeteren. Deze bewerking kan leiden tot minder WRITELOG wachttijden.

  • Planningsproblemen zorgen ervoor dat Logboekschrijver-threads niet snel genoeg worden gepland: vóór SQL Server 2016 heeft één Log Writer-thread alle logboekschrijfbewerkingen uitgevoerd. Als er problemen zijn met het plannen van threads (bijvoorbeeld een hoge CPU-belasting), kunnen zowel de logboekschrijverthread als logflushing vertraging oplopen. In SQL Server 2016 zijn maximaal vier Log Writer-threads toegevoegd om de doorvoer voor het schrijven van logboeken te verhogen. Zie SQL 2016: het wordt gewoon sneller uitgevoerd: meerdere werkrollen voor logboekschrijvers. In SQL Server 2019 zijn maximaal acht Log Writer-threads toegevoegd, waardoor de doorvoer nog meer wordt verbeterd. In SQL Server 2019 kan elke reguliere werkrolthread ook rechtstreeks logboekschrijfbewerkingen uitvoeren in plaats van te plaatsen in de Logboekschrijver-thread. Met deze verbeteringen WRITELOG worden wachttijden zelden geactiveerd door planningsproblemen.

ASYNC_IO_COMPLETION

Treedt op wanneer enkele van de volgende I/O-activiteiten plaatsvinden:

  • De Provider voor bulksgewijs invoegen ('Bulk invoegen') gebruikt dit wachttype bij het uitvoeren van I/O.
  • Het lezen van het Undo-bestand in LogShipping en het aansturen van Async I/O voor LogShipping.
  • De werkelijke gegevens uit de gegevensbestanden lezen tijdens een gegevensback-up.

IO_COMPLETION

Treedt op terwijl wordt gewacht tot I/O-bewerkingen zijn voltooid. Dit wachttype omvat over het algemeen I/Os die niet zijn gerelateerd aan gegevenspagina's (buffers). Voorbeelden zijn:

  • Het lezen en schrijven van sorteer-/hashresultaten van/naar schijf tijdens een overloop (controleer de prestaties van tempdb-opslag ).
  • Het lezen en schrijven van proactieve spools naar schijf (controle tempdb-opslag).
  • Logboekblokken lezen uit het transactielogboek (tijdens een bewerking die ervoor zorgt dat het logboek wordt gelezen vanaf de schijf, bijvoorbeeld herstel).
  • Een pagina lezen vanaf schijf wanneer de database nog niet is ingesteld.
  • Pagina's kopiëren naar een momentopname van een database (Copy-on-Write).
  • De databasebestand sluiten en bestand decomprimeren.

BACKUPIO

Treedt op wanneer een back-uptaak wacht op gegevens of wacht tot een buffer gegevens opslaat. Dit type is niet gebruikelijk, behalve wanneer een taak wacht op een tapekoppeling.

Verwante onderwerpen

  • Last updated on