Behebung von langsamen SQL Server-Leistungsproblemen aufgrund von E/A-Problemen

Gilt für: SQL Server

Dieser Artikel enthält Anleitungen dazu, was E/A-Probleme zu langsamer SQL Server-Leistung und zur Behandlung der Probleme führen.

Langsame E/A-Leistung definieren

Leistungsmonitorkzähler werden verwendet, um eine langsame E/A-Leistung zu ermitteln. Diese Zähler messen, wie schnell das E/A-Subsystem jede E/A-Anforderung im Durchschnitt in Bezug auf die Taktzeit bedient. Die spezifischen Leistungsüberwachungsindikatoren , die die E/A-Latenz in Windows messen, sind Avg Disk sec/ Read, Avg. Disk sec/Writeund Avg. Disk sec/Transfer (kumuliert von Lese- und Schreibvorgängen).

In SQL Server funktionieren die Dinge auf die gleiche Weise. Häufig prüfen Sie, ob SQL Server E/A-Engpässe meldet, die in Echtzeit gemessen werden (in Millisekunden). SQL Server stellt E/A-Anforderungen an das Betriebssystem durch Aufrufen der Win32-Funktionen wie WriteFile(), , ReadFile(), WriteFileGather()und ReadFileScatter(). Wenn SQL Server eine E/A-Anforderung sendet, misst er die Dauer der Anforderung und meldet diese mithilfe von Wartetypen. SQL Server verwendet Wartearten, um E/A-Wartezeiten an verschiedenen Stellen im Produkt anzuzeigen. Die E/A-bezogenen Wartezeiten sind:

• PAGEIOLATCH_SH / PAGEIOLATCH_EX
• WRITELOG
• IO_COMPLETION
• ASYNC_IO_COMPLETION
• BACKUPIO

Wenn diese Wartezeiten 10-15 Millisekunden konsistent überschreiten, wird E/A als Engpass betrachtet.

Methodik

Ein Flussdiagramm am Ende dieses Artikels beschreibt die Methodik, die Microsoft CSS verwendet, um langsame E/A-Probleme mit SQL Server zu behandeln. Es ist kein erschöpfender oder exklusiver Ansatz, hat sich jedoch als nützlich erwiesen, um das Problem zu isolieren und zu beheben.

Die Methodik wird in den folgenden Schritten beschrieben:

Schritt 1: Meldet SQL Server langsame I/O?

SQL Server kann E/A-Latenz auf verschiedene Arten melden:

• E/A-Wartetypen
• DMV sys.dm_io_virtual_file_stats
• Fehlerprotokoll oder Anwendungsereignisprotokoll

E/A-Wartetypen

Ermitteln Sie, ob die E/A-Latenz durch SQL Server-Wartetypen gemeldet wird. Die Werte PAGEIOLATCH_*, WRITELOG und die Werte ASYNC_IO_COMPLETION sowie die Werte mehrerer weniger gängiger Wartetypen sollten in der Regel unter 10-15 Millisekunden pro E/A-Anforderung bleiben. Wenn diese Werte konsequent höher sind, besteht ein E/A-Leistungsproblem und es erfordert eine weitere Untersuchung. Die folgende Abfrage kann Ihnen helfen, diese Diagnoseinformationen auf Ihrem System zu sammeln:

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

Dateistatistiken in sys.dm_io_virtual_file_stats

Führen Sie die folgende Abfrage aus, um die Latenz auf Datenbankdateiebene anzuzeigen, wie in SQL Server angegeben:

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

Betrachten Sie die AvgLatency Und LatencyAssessment Spalten, um die Latenzdetails zu verstehen.

Fehler 833, der im Fehlerprotokoll oder Anwendungsereignisprotokoll gemeldet wurde

In einigen Fällen können Sie fehler 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) im Fehlerprotokoll beobachten. Sie können SQL Server-Fehlerprotokolle auf Ihrem System überprüfen, indem Sie den folgenden PowerShell-Befehl ausführen:

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"

Weitere Informationen zu diesem Fehler finden Sie auch im Abschnitt MSSQLSERVER_833 .

Schritt 2: Geben Perfmon-Leistungsindikatoren die E/A-Latenz an?

Wenn SQL Server eine E/A-Latenz meldet, beziehen Sie sich auf die Betriebssystemzähler. Sie können ermitteln, ob ein E/A-Problem vorliegt, indem Sie den Latenzindikator Avg Disk Sec/Transferuntersuchen. Der folgende Codeausschnitt gibt eine Möglichkeit zum Sammeln dieser Informationen über PowerShell an. Es sammelt Zähler für alle Datenträgervolumes: "_total". Wechseln Sie zu einem bestimmten Laufwerkvolume (z. B. "D:"). Um zu ermitteln, welche Volumes Ihre Datenbankdateien hosten, führen Sie die folgende Abfrage in Ihrem SQL Server aus:

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

Sammeln Sie Avg Disk Sec/Transfer Metriken für Ihr Wahlvolumen:

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

Wenn die Werte dieses Indikators konsistent über 10-15 Millisekunden liegen, müssen Sie sich das Problem weiter ansehen. Gelegentliche Spitzen zählen in den meisten Fällen nicht, aber achten Sie darauf, die Dauer einer Spitze zu überprüfen. Wenn der Anstieg mindestens eine Minute dauerte, handelt es sich eher um ein Plateau als um einen Anstieg.

Wenn die Leistungsüberwachungsindikatoren keine Latenz melden, aber SQL Server dies tut, liegt das Problem zwischen SQL Server und dem Partitions-Manager, d. h. Filtertreibern. Der Partitions-Manager ist eine E/A-Ebene, auf der das Betriebssystem Perfmon-Leistungsindikatoren sammelt. Um die Latenz zu beheben, stellen Sie die richtigen Ausschlüsse von Filtertreibern sicher, und beheben Sie Filtertreiberprobleme. Filtertreiber werden von Programmen wie Antivirensoftware, Sicherungslösungen, Verschlüsselung, Komprimierung usw. verwendet. Mit diesem Befehl können Sie Filtertreiber auf den Systemen und den Volumes auflisten, an die sie angeschlossen sind. Anschließend können Sie die Treibernamen und Softwareanbieter im Artikel "Zugewiesene Filterhöhen " nachschlagen.

fltmc instances

Weitere Informationen finden Sie unter Auswählen von Antivirensoftware, die auf Computern ausgeführt werden soll, auf denen SQL Server ausgeführt wird.

Vermeiden Sie die Verwendung von Encrypting File System (EFS) und Dateisystemkomprimierung, da sie dazu führen, dass asynchrone E/A synchron und daher langsamer wird. Weitere Informationen finden Sie im Artikel "Asynchrone Datenträger-E/A erscheint als synchron unter Windows".

Schritt 3: Ist das E/A-Subsystem überlastet über die Kapazität hinaus?

Wenn SQL Server und das Betriebssystem angeben, dass das E/A-Subsystem langsam ist, überprüfen Sie, ob die Ursache dafür ist, dass das System über die Kapazität hinaus überfordert wird. Sie können die Kapazität überprüfen, indem Sie sich die E/A-Leistungsindikatoren Disk Bytes/Sec, Disk Read Bytes/Sec oder Disk Write Bytes/Sec ansehen. Erkundigen Sie sich bei Ihrem Systemadministrator oder Hardwareanbieter nach den erwarteten Durchsatzspezifikationen für Ihr SAN (oder ein anderes E/A-Subsystem). Sie können z. B. nicht mehr als 200 MB/s E/A über eine HBA-Karte mit 2 GB/s oder einen dedizierten 2-GB/s-Port auf einem SAN-Switch übertragen. Die erwartete Durchsatzkapazität, die von einem Hardwarehersteller definiert wird, definiert, wie Sie von hier fortfahren.

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

Schritt 4: Wird die starke E/A-Aktivität von SQL Server verursacht?

Finden Sie heraus, ob SQL Server der Schuldige ist, wenn das E/A-Subsystem überlastet ist, indem Sie die jeweilige Instanz bei Buffer Manager: Page Reads/Sec (am häufigsten) und Page Writes/Sec (viel weniger häufig) überprüfen. Wenn SQL Server der Haupt-E/A-Treiber ist und das E/A-Volumen über das hinausgeht, was das System verarbeiten kann, arbeiten Sie mit den Anwendungsentwicklungsteams oder Anwendungsanbietern zusammen, um:

• Optimieren Sie Abfragen, z. B. bessere Indizes, aktualisieren Sie Statistiken, schreiben Sie Abfragen neu, und entwerfen Sie die Datenbank neu.
• Erhöhen Sie den maximalen Serverspeicher , oder fügen Sie mehr RAM auf dem System hinzu. Mehr RAM speichert mehr Daten- oder Indexseiten im Cache, ohne häufig von der Festplatte erneut zu lesen, wodurch die E/A-Aktivität reduziert wird. Ein erhöhter Arbeitsspeicher kann auch reduziert Lazy Writes/secwerden, was durch Lazy Writer-Leerungen gesteuert wird, wenn es häufig erforderlich ist, mehr Datenbankseiten im begrenzten Arbeitsspeicher zu speichern.
• Wenn Sie feststellen, dass Seitenschreibvorgänge die Quelle für schwere E/A-Aktivitäten sind, überprüfen Buffer Manager: Checkpoint pages/sec Sie, ob dies auf massive Seitenspülungen zurückzuführen ist, die erforderlich sind, um die Konfigurationsanforderungen des Wiederherstellungsintervalls zu erfüllen. Sie können entweder indirekte Prüfpunkte verwenden, um den E/A-Verkehr im Laufe der Zeit auszugleichen, oder den Hardware-E/A-Durchsatz erhöhen.

Ursachen

Im Allgemeinen sind die folgenden Probleme die Gründe, warum SQL Server-Abfragen von E/A-Latenz betroffen sind:

• Hardwareprobleme:

  • SAN-Fehlkonfiguration (Switch, Kabel, HBA, Speicher)

  • Überschrittene E/A-Kapazität (nicht ausgeglichen im gesamten SAN-Netzwerk, nicht nur Back-End-Speicher)

  • Treiber- oder Firmwareprobleme

  Hardwareanbieter und/oder Systemadministratoren müssen in dieser Phase eingebunden werden.

• Abfrageprobleme: SQL Server sättigungt Datenträgervolumes mit E/A-Anforderungen und verschiebt das E/A-Subsystem über die Kapazität hinaus, wodurch die E/A-Übertragungsraten hoch sind. In diesem Fall besteht die Lösung darin, die Abfragen zu finden, die eine hohe Anzahl logischer Lesevorgänge (oder Schreibvorgänge) verursachen, und diese Abfragen so zu optimieren, dass Datenträger-E/A mit entsprechenden Indizes minimiert wird, ist der erste Schritt, um dies zu tun. Halten Sie außerdem Statistiken auf dem neuesten Stand, da sie den Abfrageoptimierer mit ausreichenden Informationen bereitstellen, um den besten Plan auszuwählen. Außerdem kann ein falscher Datenbankentwurf und abfrageentwurf zu einer Zunahme der E/A-Probleme führen. Daher kann die Neugestaltung von Abfragen und manchmal Tabellen zu einer verbesserten E/A-Leistung beitragen.

• Filtertreiber: Die SQL Server-E/A-Antwort kann stark beeinträchtigt werden, wenn Dateisystemfiltertreiber schwere E/A-Datenverkehr verarbeiten. Es wird empfohlen, geeignete Dateiausschlüsse von Antivirenscans vorzunehmen und ein korrektes Filtertreiberdesign durch Softwareanbieter zu verwenden, um negative Auswirkungen auf die E/A-Leistung zu vermeiden.

• Andere Anwendungen: Eine andere Anwendung auf demselben Computer mit SQL Server kann den E/A-Pfad mit übermäßigen Lese- oder Schreibanforderungen sättigungen. Diese Situation kann das E/A-Subsystem über Kapazitätsgrenzen hinausschieben und die E/A-Langsamkeit für SQL Server verursachen. Identifizieren Sie die Anwendung, und optimieren Sie sie, oder verschieben Sie sie an anderer Stelle, um ihre Auswirkungen auf den E/A-Stapel zu beseitigen.

Grafische Darstellung der Methodik

Informationen zu E/A-bezogenen Wartetypen

Im Folgenden finden Sie Beschreibungen der gängigen Wartetypen, die in SQL Server beobachtet werden, wenn Datenträger-E/A-Probleme gemeldet werden.

PAGEIOLATCH_EX

Tritt auf, wenn eine Aufgabe auf einen "Latch" für eine Daten- oder Indexseite (Puffer) in einem E/A-Auftrag wartet. Die Latch-Anforderung befindet sich im Exklusiv-Modus. Ein Exklusivmodus wird verwendet, wenn der Puffer auf den Datenträger geschrieben wird. Lange Wartezeiten können Probleme mit dem Datenträgersubsystem anzeigen.

PAGEIOLATCH_SH

Tritt auf, wenn eine Aufgabe auf einen Riegel für eine Daten- oder Indexseite (Puffer) in einer E/A-Anforderung wartet. Die Latch-Anforderung befindet sich im Modus "Freigegeben". Der Freigegebene Modus wird verwendet, wenn der Puffer vom Datenträger gelesen wird. Lange Wartezeiten können Probleme mit dem Datenträgersubsystem anzeigen.

PAGEIOLATCH_UP

Tritt auf, wenn eine Aufgabe auf einen Riegel für einen Puffer in einer E/A-Anforderung wartet. Die Latch-Anforderung befindet sich im Update-Modus. Lange Wartezeiten können Probleme mit dem Datenträgersubsystem anzeigen.

WRITELOG

Tritt auf, wenn eine Aufgabe auf den Abschluss eines Transaktionsprotokollflushs wartet. Ein Flushvorgang tritt auf, wenn der Protokoll-Manager seine temporären Inhalte auf den Datenträger schreibt. Häufige Vorgänge, die Protokolllöschvorgänge verursachen, sind Transaktions-Commits und Prüfpunkte.

Häufige Gründe für lange Wartezeiten WRITELOG sind:

• Latenz des Transaktionsprotokolldatenträgers: Dies ist die häufigste Ursache für WRITELOG Wartezeiten. Im Allgemeinen empfiehlt es sich, die Daten und Protokolldateien auf separaten Volumes zu speichern. Transaktionsprotokoll-Schreibvorgänge sind sequenzielle Schreibvorgänge, während Lese- oder Schreibvorgänge von einer Datendatei zufällig sind. Das Mischen von Daten und Protokolldateien auf einem Laufwerkvolume (insbesondere herkömmliche spinnende Festplattenlaufwerke) führt zu übermäßiger Datenträgerkopfbewegung.

• Zu viele VLFs: Zu viele virtuelle Protokolldateien (VLFs) können Wartezeiten verursachen WRITELOG . Zu viele VLFs können andere Arten von Problemen verursachen, z. B. einen langen Wiederherstellungsprozess.

• Zu viele kleine Transaktionen: Während große Transaktionen zu Blockierungen führen können, können zu viele kleine Transaktionen zu einer anderen Reihe von Problemen führen. Wenn Sie eine Transaktion nicht explizit beginnen, führt jedes Einfügen, Löschen oder Aktualisieren zu einer Transaktion (wir rufen diese automatische Transaktion auf). Wenn Sie 1.000 Einfügungen in einer Schleife ausführen, werden 1.000 Transaktionen generiert. Jede Transaktion in diesem Beispiel muss einen Commit ausführen, was zu einem Leeren des Transaktionsprotokolls und 1.000 Transaktionsleerungen führt. Wenn möglich, gruppieren Sie einzelne Aktualisierungen, Löschen oder Einfügen in eine größere Transaktion, um die Leerungen des Transaktionsprotokolls zu reduzieren und die Leistung zu erhöhen. Dieser Vorgang kann zu weniger WRITELOG Wartezeiten führen.

• Planungsprobleme führen dazu, dass Log Writer-Threads nicht schnell genug geplant werden: Vor SQL Server 2016 hat ein einzelner Log Writer-Thread alle Protokollschreibvorgänge ausgeführt. Wenn Probleme mit der Threadplanung (z. B. hohe CPU) aufgetreten sind, konnten sowohl der Log Writer-Thread als auch die Protokolllöschungen verzögert werden. In SQL Server 2016 wurden bis zu vier Log Writer-Threads hinzugefügt, um den Protokollschreibdurchsatz zu erhöhen. Siehe SQL 2016 – Es wird einfach schneller ausgeführt: Mehrere Log Writer-Worker. In SQL Server 2019 wurden bis zu acht Log Writer-Threads hinzugefügt, wodurch der Durchsatz noch verbessert wird. Außerdem kann jeder reguläre Workerthread in SQL Server 2019 Schreibvorgänge direkt protokollieren, anstatt in den Log Writer-Thread zu posten. Mit diesen Verbesserungen WRITELOG würden Wartezeiten selten durch Terminplanungsprobleme ausgelöst.

ASYNC_IO_COMPLETION

Tritt auf, wenn einige der folgenden E/A-Aktivitäten auftreten:

• Der Bulk Insert Provider ("Insert Bulk") verwendet diesen Wartetyp beim Ausführen von E/A.
• Lesen der Undo-Datei in Log Shipping und Weiterleiten von asynchroner E/A für den Protokollversand.
• Lesen der tatsächlichen Daten aus den Datendateien während einer Datensicherung.

IO_COMPLETION

Tritt auf, während auf den Abschluss von E/A-Vorgängen gewartet wird. Dieser Wartetyp umfasst im Allgemeinen I/Os, die nicht mit Datenseiten (Puffern) verknüpft sind. Beispiele:

• Lesen und Schreiben von Sortier-/Hashergebnissen von/auf den Datenträger während eines Überlaufs (Überprüfen der Leistung des tempdb-Speichers ).
• Lesen und Schreiben von eager Spools auf die Festplatte (prüfen Sie den tempdb-Speicher).
• Leseprotokollblöcke aus dem Transaktionsprotokoll (bei jedem Vorgang, der bewirkt, dass das Protokoll vom Datenträger gelesen wird , z. B. Wiederherstellung).
• Lesen einer Seite vom Datenträger, wenn die Datenbank noch nicht eingerichtet ist.
• Kopieren von Seiten in einen Datenbanksnapshot (Copy-on-Write).
• Schließen von Datenbankdateien und Dateientkomprimierung.

BACKUPIO

Tritt auf, wenn eine Sicherungsaufgabe auf Daten wartet oder auf einen Puffer zum Speichern von Daten wartet. Dieser Typ ist nicht typisch, außer wenn eine Aufgabe auf eine Bandmontage wartet.