Share via

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

  • Artikel

Van toepassing op: SQL Server

Dit artikel bevat richtlijnen over welke I/O-problemen trage SQL Server prestaties veroorzaken en hoe u de problemen kunt oplossen.

Trage I/O-prestaties definiëren

Prestatiemeteritems worden gebruikt om trage I/O-prestaties te bepalen. Met deze tellers wordt gemeten hoe snel het I/O-subsysteem elke I/O-aanvraag gemiddeld in termen van kloktijd aanbiedt. 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 werkt alles op dezelfde manier. Meestal kijkt u of SQL Server I/O-knelpunten rapporteert die zijn gemeten in de kloktijd (milliseconden). SQL Server I/O-aanvragen naar het besturingssysteem doet door de Win32-functies aan te roepen, zoals WriteFile(), ReadFile(), WriteFileGather()en ReadFileScatter(). Wanneer er een I/O-aanvraag wordt geplaatst, SQL Server keer de aanvraag en wordt de duur van de aanvraag gerapporteerd met behulp van 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.

Opmerking

Om context en perspectief te bieden, heeft Microsoft CSS in de wereld van probleemoplossing SQL Server gevallen waargenomen waarbij een I/O-aanvraag meer dan één seconde en maximaal 15 seconden per overdracht duurde. Dergelijke I/O-systemen moeten worden geoptimaliseerd. Daarentegen heeft Microsoft CSS systemen gezien waarbij de doorvoer lager is dan één milliseconde/overdracht. Met de huidige SSD/NVMe-technologie variëren de geadverteerde doorvoersnelheden in tientallen microseconden per overdracht. Daarom is het cijfer van 10-15 milliseconden/overdracht een zeer geschatte drempelwaarde die we hebben geselecteerd op basis van de collectieve ervaring tussen Windows en SQL Server engineers in de loop der jaren. Wanneer getallen deze drempelwaarde overschrijden, zien SQL Server gebruikers meestal latentie in hun workloads en rapporteren ze deze. Uiteindelijk wordt de verwachte doorvoer van een I/O-subsysteem gedefinieerd door de fabrikant, het model, de configuratie, de workload en mogelijk meerdere andere factoren.

Methodologie

In een stroomdiagram aan het einde van dit artikel wordt de methodologie beschreven die Microsoft CSS gebruikt om trage I/O-problemen met SQL Server te benaderen. Het is geen volledige of exclusieve benadering, maar is nuttig gebleken bij het isoleren van het probleem en het oplossen ervan.

U kunt een van de volgende twee opties kiezen om het probleem op te lossen:

Optie 1: voer de stappen rechtstreeks uit in een notebook via Azure Data Studio

Opmerking

Voordat u dit notebook opent, moet u ervoor zorgen dat Azure Data Studio is geïnstalleerd op uw lokale computer. Als u deze wilt installeren, gaat u naar Meer informatie over het installeren van Azure Data Studio.

Notebook openen in Azure Data Studio

Optie 2: Volg de stappen handmatig

De methodologie wordt in deze stappen beschreven:

Stap 1: is SQL Server trage I/O-rapportage?

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

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

Bepaal of er I/O-latentie wordt gerapporteerd door SQL Server wachttypen. De waarden PAGEIOLATCH_*, WRITELOGen ASYNC_IO_COMPLETION en de waarden van verschillende andere minder voorkomende wachttypen moeten over het algemeen onder 10-15 milliseconden per I/O-aanvraag blijven. Als deze waarden consistent groter zijn, treedt er een I/O-prestatieprobleem op en moet er verder onderzoek worden uitgevoerd. De volgende query kan u helpen bij het verzamelen van deze diagnostische gegevens op uw systeem:

#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

Voer de volgende query uit om de latentie op databasebestandsniveau weer te geven zoals gerapporteerd in SQL Server:

#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 kolommen en LatencyAssessment om inzicht te verkrijgen 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 SQL Server foutenlogboeken 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 ook de sectie MSSQLSERVER_833 voor meer informatie over deze fout.

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

Als SQL Server I/O-latentie rapporteert, raadpleegt u Besturingssysteemitems. U kunt bepalen of er een I/O-probleem is door de latentiemeteritems te Avg Disk Sec/Transferbekijken. Het volgende codefragment geeft een manier aan om deze informatie te verzamelen via PowerShell. Het verzamelt tellers op alle schijfvolumes: '_total'. Ga naar een specifiek stationsvolume (bijvoorbeeld 'D:'). Voer de volgende query uit in uw SQL Server om te bepalen welke volumes uw databasebestanden hosten:

#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 het volume van uw keuze:

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 dit teller consistent hoger zijn dan 10-15 milliseconden, moet u het probleem verder bekijken. Incidentele pieken tellen in de meeste gevallen niet mee, maar controleer de duur van een piek. Als de piek een minuut of langer duurde, is het meer een plateau dan een piek.

Als de prestatiemeteritems geen latentie rapporteren, maar SQL Server wel, ligt het probleem tussen SQL Server en Partitiebeheer, dat wil weten filterstuurprogramma's. Partition Manager is een I/O-laag waar het besturingssysteem Perfmon-tellers verzamelt. Als u de latentie wilt aanpakken, moet u ervoor zorgen dat filterstuurprogramma's worden uitgesloten en problemen met filterstuurprogramma's worden opgelost. Filterstuurprogramma's worden gebruikt door programma's zoals antivirussoftware, back-upoplossingen, versleuteling, compressie, enzovoort. U kunt deze opdracht gebruiken om filterstuurprogramma's weer te geven op de systemen en de volumes waaraan ze zijn gekoppeld. Vervolgens kunt u de stuurprogrammanamen en softwareleveranciers opzoeken 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 EFS (Encrypting File System) en bestandssysteemcompressie, omdat deze ervoor zorgen dat asynchrone I/O synchroon wordt en daarom langzamer wordt. Zie het artikel Asynchrone schijf-I/O wordt als synchroon weergegeven in Windows voor meer informatie.

Stap 3: Wordt 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 de oorzaak is dat het systeem buiten de capaciteit wordt overspoeld. 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 niet meer dan 200 MB/sec I/O pushen 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 is gedefinieerd door een hardwarefabrikant, 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: is SQL Server de motor van de zware I/O-activiteit?

Als het I/O-subsysteem wordt overbelast boven de capaciteit, controleert u of SQL Server de boosdoener is door te kijken Buffer Manager: Page Reads/Sec naar (meest voorkomende boosdoener) en Page Writes/Sec (veel minder vaak voorkomende) voor het specifieke exemplaar. Als SQL Server het belangrijkste I/O-stuurprogramma is en het I/O-volume groter is dan wat het systeem kan verwerken, neemt u contact op met de teams voor toepassingsontwikkeling of de leverancier van de toepassing om het volgende te doen:

  • Query's afstemmen, bijvoorbeeld: betere indexen, statistieken bijwerken, query's herschrijven en de database opnieuw ontwerpen.
  • Verhoog het maximum aantal servergeheugen of voeg meer RAM toe aan het systeem. Meer RAM zal meer gegevens opslaan of pagina's indexeren zonder regelmatig opnieuw te lezen van de schijf, waardoor de I/O-activiteit wordt verminderd.

Oorzaken

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)

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

    • Problemen met stuurprogramma's of firmware

    Hardwareleveranciers en/of systeembeheerders moeten in deze fase worden ingeschakeld.

  • Queryproblemen: SQL Server de schijfvolumes met I/O-aanvragen verzadigt en het I/O-subsysteem buiten de capaciteit duwt, waardoor de I/O-overdrachtssnelheden hoog zijn. In dit geval is de oplossing om de query's te vinden die een groot aantal logische leesbewerkingen (of schrijfbewerkingen) veroorzaken en deze query's af te stemmen om schijf-I/O te minimaliseren met behulp van de juiste indexen. Houd statistieken ook bijgewerkt omdat ze de queryoptimalisatie voldoende informatie bieden om het beste plan te kiezen. Een onjuist databaseontwerp en queryontwerp kunnen ook leiden tot een toename van I/O-problemen. Daarom kan het opnieuw ontwerpen van query's en soms tabellen helpen met verbeterde I/O.

  • Stuurprogramma's filteren: Het SQL Server I/O-antwoord kan ernstig worden beïnvloed als bestandssysteemfilterstuurprogramma's veel I/O-verkeer verwerken. De juiste bestandsuitsluitingen van antivirusscans en het juiste ontwerp van filterstuurprogramma's door softwareleveranciers worden aanbevolen om invloed op I/O-prestaties te voorkomen.

  • Andere toepassingen: Een andere toepassing op dezelfde computer met SQL Server kan het I/O-pad verzadigen met overmatige lees- of schrijfaanvragen. Deze situatie kan ertoe leiden dat het I/O-subsysteem de capaciteitslimiet overschrijdt en I/O-traagheid veroorzaakt 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.

Hier volgen beschrijvingen van de veelvoorkomende wachttypen die worden waargenomen in SQL Server wanneer I/O-problemen met de schijf worden gerapporteerd.

PAGEIOLATCH_EX

Treedt op wanneer een taak op een vergrendeling wacht op een gegevens- of indexpagina (buffer) in een I/O-aanvraag. De aanvraag voor de vergrendeling bevindt zich in de modus Exclusief. Een exclusieve modus wordt 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 op een vergrendeling wacht op 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

Treedt op wanneer een taak op een vergrendeling wacht op een buffer in een I/O-aanvraag. 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. Er treedt een flush op wanneer logbeheer de tijdelijke inhoud naar de schijf schrijft. Veelvoorkomende bewerkingen die het leegmaken van logboeken veroorzaken, zijn transactiedoorvoeringen en controlepunten.

Veelvoorkomende redenen voor lange wachttijden zijn WRITELOG :

  • Latentie van transactielogboekschijf: dit is de meest voorkomende oorzaak van WRITELOG wachttijden. Over het algemeen wordt aanbevolen om de gegevens en logboekbestanden op afzonderlijke volumes te bewaren. Schrijfbewerkingen in transactielogboeken zijn opeenvolgende 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 beweging 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 lang herstel.

  • Te veel kleine transacties: hoewel grote transacties kunnen leiden tot blokkeringen, kunnen te veel kleine transacties leiden tot een andere reeks problemen. Als u niet expliciet een transactie start, resulteert een invoegen, verwijderen of bijwerken in een transactie (we noemen deze automatische transactie). Als u 1000 invoegingen in een lus uitvoert, worden er 1000 transacties gegenereerd. Elke transactie in dit voorbeeld moet worden doorgevoerd, wat resulteert in het leegmaken van het transactielogboek en 1000 transacties. Indien mogelijk groepeert u afzonderlijke updates, verwijdert u of voegt u 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.

  • Problemen met het plannen zorgen ervoor dat Logboekschrijver-threads niet snel genoeg worden gepland: vóór SQL Server 2016 heeft één Logboekschrijver-thread alle schrijfbewerkingen in logboeken uitgevoerd. Als er problemen zijn met het plannen van threads (bijvoorbeeld een hoog CPU-gebruik), kunnen zowel de Log Writer-thread als het leegmaken van logboeken worden vertraagd. In SQL Server 2016 zijn maximaal vier logboekschrijver-threads toegevoegd om de doorvoer voor het schrijven van logboeken te verhogen. Zie SQL 2016 - Het werkt alleen sneller: meerdere werkrollen voor logboekschrijvers. In SQL Server 2019 zijn er maximaal acht logboekschrijver-threads toegevoegd, waardoor de doorvoer nog verder wordt verbeterd. In SQL Server 2019 kan elke gewone werkrolthread ook schrijfbewerkingen in logboeken rechtstreeks uitvoeren in plaats van te posten in de logboekschrijver-thread. Met deze verbeteringen WRITELOG zouden wachttijden zelden worden geactiveerd door planningsproblemen.

ASYNC_IO_COMPLETION

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

  • De provider bulksgewijs invoegen ('Bulksgewijs invoegen') gebruikt dit wachttype bij het uitvoeren van I/O.
  • Het lezen van bestand ongedaan maken in LogShipping en het omsturen van Async I/O voor logboekverzending.
  • De werkelijke gegevens uit de gegevensbestanden lezen tijdens een gegevensback-up.

IO_COMPLETION

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

  • Het lezen en schrijven van sortering/hash-resultaten van/naar schijf tijdens een overloop (controleer de prestaties van tempdb-opslag ).
  • Lezen en schrijven van gretige spools naar schijf (controleer tempdb-opslag ).
  • Logboekblokken lezen uit het transactielogboek (tijdens een bewerking die ervoor zorgt dat het logboek wordt gelezen van 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).
  • Databasebestand en bestandscompressie sluiten.

BACKUPIO

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