Felsöka långsam SQL Server-prestanda som orsakas av I/O-problem

Gäller för: SQL Server

Sammanfattning

Den här artikeln innehåller en stegvis metod för att diagnostisera och lösa långsamma SQL Server prestanda som orsakas av disk-I/O-flaskhalsar. Den förklarar hur du identifierar I/O-svarstid med hjälp av SQL Server väntetyper, den sys.dm_io_virtual_file_stats dynamiska hanteringsvyn och Windows Performance Monitor räknare. Du får lära dig hur du avgör om I/O-undersystemet är överbelastat, om SQL Server är den primära drivrutinen för I/O och vilken maskinvara, fråga, filterdrivrutin eller programnivå som ska undersökas. Använd den här vägledningen för att isolera källan till I/O-svarstid och tillämpa rätt korrigering för lokala SQL Server distributioner.

Definiera långsamma I/O-prestanda

Prestandaövervakningsräknare används för att fastställa långsamma I/O-prestanda. Dessa räknare mäter hur snabbt I/O-undersystemet betjänar varje I/O-begäran i genomsnitt när det gäller klocktid. De specifika prestandaövervakarräknare som mäter I/O-svarstid i Windows är Avg Disk sec/ Read, Avg. Disk sec/Writeoch Avg. Disk sec/Transfer (kumulativa för både läsningar och skrivningar).

I SQL Server fungerar saker på samma sätt. Vanligtvis tittar du på om SQL Server rapporterar några I/O-flaskhalsar mätt i klocktid (millisekunder). SQL Server gör I/O-begäranden till operativsystemet genom att anropa Win32-funktionerna, till exempel WriteFile(), ReadFile(), WriteFileGather()och ReadFileScatter(). När den publicerar en I/O-begäran, mäter SQL Server tiden för begäran och rapporterar varaktigheten för begäran med hjälp av väntetyper. SQL Server använder väntetyper för att ange I/O-väntetider på olika platser i produkten. I/O-relaterade väntetider är:

• / PAGEIOLATCH_SHPAGEIOLATCH_EX
• WRITELOG
• IO_COMPLETION
• ASYNC_IO_COMPLETION
• BACKUPIO

Om dessa väntetider överskrider 10–15 millisekunder konsekvent anses I/O vara en flaskhals.

Anmärkning

För att ge kontext har Microsoft observerat SQL Server system där en I/O-begäran tog över en sekund och så högt som 15 sekunder per överföring. Sådana I/O-system kräver optimering. Omvänt har Microsoft också sett system där dataflödet ligger under en millisekunder per överföring. Med dagens SSD- och NVMe-teknik varierar de annonserade dataflödena i tiotals mikrosekunder per överföring.

Siffran 10–15 millisekunder per överföring är ett ungefärligt tröskelvärde som väljs baserat på kollektiv erfarenhet mellan Windows och SQL Server ingenjörer. När talen överskrider det här tröskelvärdet SQL Server användare börjar se svarstider i sina arbetsbelastningar. I slutändan definieras det förväntade dataflödet för ett I/O-undersystem av tillverkare, modell, konfiguration, arbetsbelastning och andra faktorer.

Metodik för att isolera I/O-flaskhalsar

Ett flödesdiagram i slutet av den här artikeln beskriver den metod som ofta används för att hantera långsamma I/O-problem med SQL Server. Den här metoden är inte fullständig eller exklusiv, men är användbar för att isolera och lösa problemet.

Metoden beskrivs i följande steg:

Steg 1: Rapporterar SQL Server långsamt I/O?

SQL Server kan rapportera I/O-svarstid på flera sätt:

• I/O-väntetyper
• DMV sys.dm_io_virtual_file_stats
• Fellogg eller programhändelselogg

I/O-väntetyper

Kontrollera om SQL Server väntetyper rapporterar I/O-svarstid. Värdena PAGEIOLATCH_*, WRITELOG, och ASYNC_IO_COMPLETION, tillsammans med värdena för flera andra mindre vanliga väntetyper bör vanligtvis ligga under 10–15 millisekunder per I/O-begäran. Om dessa värden konsekvent överskrider det här intervallet finns det ett I/O-prestandaproblem och kräver ytterligare undersökning. Följande fråga kan hjälpa dig att samla in den här diagnostikinformationen i systemet:

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

Filstatistik i sys.dm_io_virtual_file_stats

Kör följande fråga för att visa svarstiden på databasfilnivå som rapporterats i 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"

Titta på kolumnerna AvgLatency och LatencyAssessment för att förstå svarstidsinformationen.

Fel 833 rapporterades i felloggen eller programhändelseloggen

I vissa fall kan du observera fel 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) i felloggen. Du kan kontrollera SQL Server-felloggarna i systemet genom att köra följande PowerShell-kommando:

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"

Mer information om det här felet finns i avsnittet MSSQLSERVER_833 .

Steg 2: Anger Perfmon-räknare I/O-svarstid?

Om SQL Server rapporterar I/O-svarstidsfördröjning bör du kontrollera OS-räknarna. Du kan avgöra om det finns ett I/O-problem genom att undersöka svarstidsräknaren Avg Disk Sec/Transfer. Följande kodfragment anger ett sätt att samla in den här informationen via PowerShell. Den samlar in räknare på alla diskvolymer: "_total". Ändra till en viss enhetsvolym (till exempel "D:"). Kör följande fråga i SQL Server för att ta reda på vilka volymer som är värdar för dina databasfiler:

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

Samla in Avg Disk Sec/Transfer mått på valfri volym:

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

Om värdena för den här räknaren konsekvent överstiger 10–15 millisekunder måste du undersöka ytterligare. Tillfälliga toppar räknas inte i de flesta fall, men se till att dubbelkolla varaktigheten för en topp. Om toppen varade i en minut eller mer, är det mer en platå än en spik.

Om Performance Monitor-räknarna inte rapporterar svarstid, men SQL Server gör det, är problemet mellan SQL Server och Partitionshanteraren, det vill s. v.s. filterdrivrutiner. Partitionshanteraren är ett I/O-lager där operativsystemet samlar in Perfmon-räknare. Åtgärda svarstiden genom att säkerställa rätt undantag för filterdrivrutiner och lösa problem med filterdrivrutiner. Program som antivirusprogram, säkerhetskopieringslösningar, kryptering, komprimering och liknande programvara använder filterdrivrutiner. Använd det här kommandot för att lista filterdrivrutiner på systemen och de volymer som de ansluter till. Leta sedan upp drivrutinsnamnen och programvaruleverantörerna i artikeln Allokerade filterhöjder .

fltmc instances

Mer information finns i Så här väljer du antivirusprogram som ska köras på datorer som kör SQL Server.

Undvik att använda EFS (Encrypting File System) och filsystemkomprimering eftersom de gör att asynkron I/O blir synkront och därför långsammare. Mer information finns i artikeln Asynkron disk-I/O visas som synkron i Windows .

Steg 3: Överbelastas I/O-undersystemet bortom kapaciteten?

Om SQL Server och operativsystemet anger att I/O-undersystemet är långsamt kontrollerar du om orsaken är att systemet överbelastas bortom kapaciteten. Du kan kontrollera kapaciteten genom att titta på I/O-räknare Disk Bytes/Sec, Disk Read Bytes/Seceller Disk Write Bytes/Sec. Kontakta systemadministratören eller maskinvaruleverantören om du vill ha de förväntade dataflödesspecifikationerna för ditt SAN (eller något annat I/O-undersystem). Du kan till exempel överföra högst 200 MB/s av I/O via ett 2 GB/sek HBA-kort eller en dedikerad port på 2 GB/sek på en SAN-switch. Den förväntade dataflödeskapaciteten som definieras av en maskinvarutillverkare definierar hur du går vidare härifrån.

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

Steg 4: Ligger SQL Server bakom den tunga I/O-aktiviteten?

Om I/O-undersystemet är överbelastat bortom kapaciteten kan du ta reda på om SQL Server är den skyldige genom att titta på Buffer Manager: Page Reads/Sec (den vanligaste boven) och Page Writes/Sec (mycket mindre vanligt) för den specifika instansen. Om SQL Server är den huvudsakliga I/O-drivrutinen och I/O-volymen är utöver vad systemet kan hantera, kan du arbeta med programutvecklingsteamen eller programleverantören för att:

• Finjustera frågor, till exempel: bättre index, uppdatera statistik, skriva om frågor och göra om databasen.
• Öka maximalt serverminne eller lägg till mer RAM-minne i systemet. Mer RAM kommer att cacha fler data eller indexerade sidor utan att behöva läsa om från disken ofta, vilket minskar I/O-aktiviteten. Ökat minne kan också minska Lazy Writes/sec, vilket drivs av Lazy Writer-tömningar när det ofta finns ett behov av att lagra fler databassidor i det begränsade tillgängliga minnet.
• Om du upptäcker att sidskrivningar är källan till tung I/O-aktivitet, undersök Buffer Manager: Checkpoint pages/sec för att se om det beror på massiva sidspolningar som krävs för att uppfylla kraven i konfigurationen för återställningsintervall. Du kan antingen använda indirekta kontrollpunkter för att jämna ut I/O över tid eller öka maskinvaru-I/O-dataflödet.

Vanliga grundorsaker till SQL Server I/O-svarstid

I allmänhet är följande problem de övergripande orsakerna till att SQL Server-frågor lider av I/O-svarstid:

• Maskinvaruproblem:

  • En SAN-felkonfiguration (växel, kablar, HBA, lagring)

  • Överskred I/O-kapaciteten (obalanserad i hela SAN-nätverket, inte bara serverdelslagring)

  • Problem med drivrutiner eller inbyggd programvara

  Kontakta maskinvaruleverantörer och systemadministratörer i det här skedet.

• Frågeproblem: SQL Server mättar diskvolymer med I/O-begäranden och push-överför I/O-undersystemet bortom kapaciteten, vilket gör att I/O-överföringshastigheten blir hög. I det här fallet letar du upp de frågor som orsakar ett stort antal logiska läsningar (eller skrivningar) och justerar dessa frågor för att minimera disk-I/O. Att använda lämpliga index är det första steget för att göra det. Håll också statistiken uppdaterad eftersom de ger frågeoptimeraren tillräckligt med information för att välja den bästa planen. Felaktig databasdesign och frågedesign kan leda till en ökning av I/O-problem. Därför kan omdesign av frågor och ibland tabeller hjälpa till att förbättra I/O.

• Filtrera drivrutiner: Om filsystemfilterdrivrutiner bearbetar tung I/O-trafik kan de allvarligt påverka SQL Server I/O-svar. Förhindra påverkan på I/O-prestanda genom att korrekt exkludera filer från antivirusgenomsökning och säkerställa korrekt design av filterdrivrutiner av programvaruleverantörer.

• Andra program: Ett annat program på samma dator med SQL Server kan mätta I/O-sökvägen med överdrivna läs- eller skrivbegäranden. Den här situationen kan göra att I/O-undersystemet överskrider kapacitetsgränserna och orsakar I/O-långsamhet för SQL Server. Identifiera programmet och justera det eller flytta det någon annanstans för att eliminera dess inverkan på I/O-stacken.

Grafisk representation av metoden

Information om I/O-relaterade väntetyper

Följande beskrivningar beskriver vanliga väntetyper som du ser i SQL Server när disk-I/O-problem rapporteras.

PAGEIOLATCH_EX

Inträffar när en aktivitet väntar på en spärr för en data- eller indexsida (buffert) i en I/O-begäran. Spärrbegäran är i exklusivt läge. Ett exklusivt läge används när bufferten skrivs till disk. Långa väntetider kan tyda på problem med diskundersystemet.

PAGEIOLATCH_SH

Inträffar när en aktivitet väntar på en spärr för en data- eller indexsida (buffert) i en I/O-begäran. Spärrbegäran är i delat läge. Det delade läget används när bufferten läses från disken. Långa väntetider kan tyda på problem med diskundersystemet.

PAGEIOLATCH_UP

Inträffar när en aktivitet väntar på en spärr för en buffert i en I/O-begäran. Spärrbegäran är i uppdateringsläge. Långa väntetider kan tyda på problem med diskundersystemet.

WRITELOG

Inträffar när en aktivitet väntar på att en transaktionslogg ska tömmas. En tömning inträffar när Log Manager skriver sitt tillfälliga innehåll till disken. Vanliga åtgärder som orsakar loggflöden är transaktionskompletteringar och kontrollpunkter.

Vanliga orsaker till långa väntetider WRITELOG är:

• Svarstid för transaktionsloggdiskar: Det här är den vanligaste orsaken till WRITELOG väntetider. I allmänhet rekommenderar vi att du behåller data och loggfiler på separata volymer. Transaktionsloggskrivningar är sekventiella skrivningar medan läsning eller skrivning av data från en datafil är slumpmässig. Om du blandar data och loggfiler på en enhetsvolym (särskilt konventionella snurrande diskenheter) orsakas för hög diskhuvudförflyttning.

• För många VLFs: För många virtuella loggfiler (VLFs) kan orsaka WRITELOG väntetider. För många VLF:er kan orsaka andra typer av problem, till exempel en lång återhämtningsprocess.

• För många små transaktioner: Även om stora transaktioner kan leda till blockering kan för många små transaktioner leda till en annan uppsättning problem. Om du inte uttryckligen påbörjar en transaktion resulterar alla infognings-, borttagnings- eller uppdateringstransaktioner i en transaktion (vi anropar den här automatiska transaktionen). Om du gör 1 000 infogningar i en loop genereras 1 000 transaktioner. Varje transaktion i det här exemplet måste committeras, vilket resulterar i en tömning av transaktionsloggen och 1 000 transaktionstömningar. Om möjligt kan du gruppera enskilda uppdateringar, ta bort eller infoga i en större transaktion för att minska transaktionsloggens tömningar och öka prestandan. Den här åtgärden kan leda till färre WRITELOG väntetider.

• Schemaläggningsproblem gör att Log Writer-trådar inte schemaläggs tillräckligt snabbt: Före SQL Server 2016 utförde en enda Log Writer-tråd alla loggskrivningar. Om det uppstod problem med trådschemaläggning (till exempel hög CPU) kan både Loggskrivare-tråden och loggspolningar fördröjas. I SQL Server 2016 lades upp till fyra Log Writer-trådar till för att öka loggskrivningsdataflödet. Se SQL 2016 – det kör bara snabbare: Flera loggskrivningsarbetare. I SQL Server 2019 har upp till åtta Log Writer-trådar lagts till, vilket förbättrar dataflödet ännu mer. I SQL Server 2019 kan varje vanlig arbetstråd också göra loggskrivningar direkt i stället för att publicera till Loggskrivare-tråden. Med dessa förbättringar WRITELOG skulle väntetider sällan utlösas av schemaläggningsproblem.

ASYNC_IO_COMPLETION

Inträffar när några av följande I/O-aktiviteter inträffar:

• Massinfogningsleverantören ("Insert Bulk") använder den här väntetypen när man utför I/O.
• Läser Ångra fil i LogShipping och dirigerar Async I/O för loggleverans.
• Läsa verklig data från datafilerna under en säkerhetskopiering.

IO_COMPLETION

Inträffar i väntan på att I/O-åtgärder ska slutföras. Den här väntetypen omfattar vanligtvis I/Os som inte är relaterade till datasidor (buffertar). Exempel:

• Läsning och skrivning av sort/hash-resultat från/till disk under ett spill (kontrollera prestanda för tempdb-lagring ).
• Läser och skriver eager spools till disk (kontrollera tempdb-lagringen).
• Läsa loggblock från transaktionsloggen (under alla åtgärder som gör att loggen läses från disken, till exempel återställning).
• Läser en sida från disken när databasen inte har konfigurerats än.
• Kopiera sidor till en ögonblicksbild av databasen (Copy-on-Write).
• Stänger databasfil och filavkomprimering.

BACKUPIO

Inträffar när en säkerhetskopieringsaktivitet väntar på data eller väntar på att en buffert ska lagra data. Den här typen är inte typisk, förutom när en uppgift väntar på montering av magnetband.

Relaterat innehåll

• SQL Server väntetyper (sys.dm_os_wait_stats)
• Transaktionsloggen (SQL Server)
• tempdb-databas
• MSSQLSERVER_833 Database Engine fel