Problembehandlung für langsame SQL Server Leistung, die durch E/A-Probleme verursacht wird

Gilt für: SQL Server

Dieser Artikel enthält Anleitungen dazu, welche E/A-Probleme eine langsame SQL Server Leistung verursachen und wie Sie die Probleme beheben können.

Definieren einer langsamen E/A-Leistung

Leistungsindikatoren für die Leistungsüberwachung werden verwendet, um die langsame E/A-Leistung zu bestimmen. Diese Leistungsindikatoren messen, wie schnell das E/A-Subsystem jede E/A-Anforderung im Durchschnitt in Bezug auf die Uhrzeit verarbeitet. Die spezifischen Leistungsindikatoren der Leistungsüberwachung , die die E/A-Latenz in Windows messen, sind Avg Disk sec/ Read, Avg. Disk sec/Writeund Avg. Disk sec/Transfer (kumulativ aus Lese- und Schreibvorgängen).

In SQL Server funktioniert dies genauso. In der Regel überprüfen Sie, ob SQL Server E/A-Engpässe meldet, gemessen in Der Uhrzeit (Millisekunden). SQL Server stellt E/A-Anforderungen an das Betriebssystem, indem die Win32-Funktionen wie WriteFile(), ReadFile(), WriteFileGather()und ReadFileScatter()aufgerufen werden. Wenn eine E/A-Anforderung bereitgestellt wird, SQL Server mal die Anforderung und meldet die Dauer der Anforderung mithilfe von Wartetypen. SQL Server verwendet Wartetypen, um E/A-Wartevorgänge an verschiedenen Stellen im Produkt anzugeben. Die E/A-bezogenen Wartevorgänge sind:

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

Hinweis

Um Kontext und Perspektive bereitzustellen, hat Microsoft CSS in der Welt der Problembehandlung SQL Server Fälle beobachtet, in denen eine E/A-Anforderung mehr als eine Sekunde dauerte und bis zu 15 Sekunden pro Übertragung solche E/A-Systeme optimiert werden müssen. Im Gegensatz dazu hat Microsoft CSS Systeme gesehen, bei denen der Durchsatz unter einer Millisekunde/Übertragung liegt. Mit der heutigen SSD/NVMe-Technologie reichen die angekündigten Durchsatzraten in Zehn mikrosekunden pro Übertragung. Daher ist die Zahl von 10-15 Millisekunden/Übertragung ein sehr ungefährer Schwellenwert, den wir basierend auf der gemeinsamen Erfahrung zwischen Windows und SQL Server Ingenieuren im Laufe der Jahre ausgewählt haben. Wenn die Zahlen diesen ungefähren Schwellenwert überschreiten, sehen SQL Server Benutzer in der Regel Latenz in ihren Workloads und melden sie. Letztendlich wird der erwartete Durchsatz eines E/A-Subsystems durch den Hersteller, das Modell, die Konfiguration, die Workload und möglicherweise mehrere andere Faktoren definiert.

Methodik

Ein Flussdiagramm am Ende dieses Artikels beschreibt die Methodik, die Microsoft CSS verwendet, um probleme mit langsamen E/A-Vorgängen mit SQL Server anzugehen. Dies ist kein vollständiger oder exklusiver Ansatz, hat sich aber als nützlich erwiesen, um das Problem zu isolieren und es zu beheben.

Sie können eine der folgenden beiden Optionen auswählen, um das Problem zu beheben:

Option 1: Direktes Ausführen der Schritte in einem Notebook über Azure Data Studio

Hinweis

Bevor Sie versuchen, dieses Notebook zu öffnen, stellen Sie sicher, dass Azure Data Studio auf Ihrem lokalen Computer installiert ist. Informationen zur Installation finden Sie unter Installieren von Azure Data Studio.

Option 2: Führen Sie die Schritte manuell aus.

Die Methodik wird in den folgenden Schritten beschrieben:

Schritt 1: Meldet SQL Server langsame E/A-Vorgänge?

SQL Server können E/A-Latenzen auf verschiedene Arten melden:

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

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

#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 in SQL Server gemeldete Latenz auf Datenbankdateiebene anzuzeigen:

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

Sehen Sie sich die AvgLatency Spalten und LatencyAssessment an, um die Latenzdetails zu verstehen.

Fehler 833 im Fehlerprotokoll oder Anwendungsereignisprotokoll gemeldet

In einigen Fällen kann 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 auftreten. 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 im Abschnitt MSSQLSERVER_833 .

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

Wenn SQL Server E/A-Latenz meldet, finden Sie weitere Informationen unter Betriebssystemindikatoren. Sie können ermitteln, ob ein E/A-Problem vorliegt, indem Sie den Latenzindikator Avg Disk Sec/Transferuntersuchen. Der folgende Codeausschnitt zeigt eine Möglichkeit zum Sammeln dieser Informationen über PowerShell an. Es erfasst Leistungsindikatoren auf allen Datenträgervolumes: "_total". Wechseln Sie zu einem bestimmten Laufwerkvolume (z. B. "D:"). Führen Sie die folgende Abfrage in Ihrem SQL Server aus, um zu ermitteln, auf welchen Volumes Ihre Datenbankdateien gehostet werden:

#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 das Volumen Ihrer Wahl:

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 Zählers konsistent über 10 bis 15 Millisekunden liegen, müssen Sie das Problem weiter untersuchen. Gelegentliche Spitzen zählen in den meisten Fällen nicht, aber überprüfen Sie unbedingt die Dauer einer Spitze. Wenn die Spitze eine Minute oder länger dauerte, handelt es sich eher um ein Plateau als um eine Spitze.

Wenn die Leistungsindikatoren der Leistungsüberwachung 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-Indikatoren sammelt. Um die Latenz zu beheben, stellen Sie sicher, dass Filtertreiber ordnungsgemäß ausgeschlossen werden, und beheben Sie Probleme mit dem Filtertreiber. Filtertreiber werden von Programmen wie Antivirensoftware, Sicherungslösungen, Verschlüsselung, Komprimierung usw. verwendet. Sie können diesen Befehl verwenden, um Filtertreiber für die Systeme und die Volumes aufzulisten, an die sie angefügt sind. Anschließend können Sie die Treibernamen und Softwareanbieter im Artikel Zugeordnete Filterhöhen nachschlagen.

fltmc instances

Weitere Informationen finden Sie unter Auswählen von Antivirensoftware für die Ausführung auf Computern, auf denen SQL Server ausgeführt wird.

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

Schritt 3: Ist das E/A-Subsystem überlastet?

Wenn SQL Server und das Betriebssystem angeben, dass das E/A-Subsystem langsam ist, überprüfen Sie, ob die Ursache darin besteht, dass das System überlastet ist, das über die Kapazität hinausgeht. Sie können die Kapazität überprüfen, indem Sie sich die E/A-Indikatoren Disk Bytes/Sec, Disk Read Bytes/Secoder Disk Write Bytes/Secansehen. Wenden Sie sich an Ihren Systemadministrator oder Hardwareanbieter, um die erwarteten Durchsatzspezifikationen für Ihr SAN (oder ein anderes E/A-Subsystem) zu erhalten. Beispielsweise können Sie nicht mehr als 200 MB/s E/A über einen HBA mit 2 GB/s Karte oder einen dedizierten Port mit 2 GB/s an einem SAN-Switch übertragen. Die erwartete Durchsatzkapazität, die von einem Hardwarehersteller definiert wird, definiert, wie Sie von hier aus vorgehen.

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 SQL Server die starke E/A-Aktivität steuern?

Wenn das E/A-Subsystem überlastet ist, finden Sie heraus, ob SQL Server der Schuldige ist, indem Sie (Buffer Manager: Page Reads/Secdie häufigsten Schuldigen) und Page Writes/Sec (viel seltener) für die spezifische instance. Wenn SQL Server der Standard E/A-Treiber ist und das E/A-Volume über das System hinausgeht, arbeiten Sie mit den Anwendungsentwicklungsteams oder dem Anwendungsanbieter zusammen, um Folgendes zu erreichen:

  • Optimieren Sie Abfragen, z. B. bessere Indizes, aktualisieren Sie Statistiken, schreiben Sie Abfragen neu, und gestalten Sie die Datenbank neu.
  • Erhöhen Sie den maximalen Serverarbeitsspeicher , oder fügen Sie mehr RAM auf dem System hinzu. Mehr RAM speichert mehr Daten oder Indexseiten zwischen, ohne dass häufig erneut vom Datenträger gelesen wird, wodurch die E/A-Aktivität reduziert wird.

Ursachen

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

  • Hardwareprobleme:

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

    • E/A-Kapazität überschritten (unausgeglichen im gesamten SAN-Netzwerk, nicht nur im Back-End-Speicher)

    • Treiber- oder Firmwareprobleme

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

  • Abfrageprobleme: SQL Server Datenträgervolumes mit E/A-Anforderungen überlastet und das E/A-Subsystem über die Kapazität hinaus pusht, was zu hohen E/A-Übertragungsraten führt. 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 zu optimieren, um die Datenträger-E/A mithilfe geeigneter Indizes zu minimieren. Halten Sie statistiken außerdem auf dem neuesten Stand, da sie dem Abfrageoptimierer ausreichende Informationen zur Auswahl des besten Plans bereitstellen. Außerdem kann ein falscher Datenbankentwurf und Abfrageentwurf zu einer Zunahme von E/A-Problemen führen. Daher kann die Neugestaltung von Abfragen und manchmal Tabellen bei verbesserten E/A-Vorgängen hilfreich sein.

  • Filtertreiber: Die SQL Server E/A-Antwort kann stark beeinträchtigt werden, wenn Dateisystemfiltertreiber einen hohen E/A-Datenverkehr verarbeiten. Um Auswirkungen auf die E/A-Leistung zu vermeiden, werden geeignete Dateiausschlüsse von Antivirenscans und korrektes Filtertreiberdesign von Softwareanbietern empfohlen.

  • Andere Anwendungen: Eine andere Anwendung auf demselben Computer mit SQL Server kann den E/A-Pfad mit übermäßigen Lese- oder Schreibanforderungen überlasten. Diese Situation kann dazu führen, dass das E/A-Subsystem die Kapazitätsgrenzen überschreitet und die E/A-Geschwindigkeit für SQL Server. Identifizieren Sie die Anwendung, und optimieren Sie sie, oder verschieben Sie sie an einen anderen Ort, um ihre Auswirkungen auf den E/A-Stapel zu vermeiden.

Grafische Darstellung der Methodik

Visuelle Darstellung der Methodik zum Beheben langsamer E/A-Probleme mit SQL Server.

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 ein Task auf einen Latch für eine Daten- oder Indexseite (Puffer) in einer E/A-Anforderung wartet. Die Latchanforderung befindet sich im exklusiven Modus. Ein exklusiver Modus wird verwendet, wenn der Puffer auf den Datenträger geschrieben wird. Lange Wartezeiten können auf Probleme mit dem Datenträgersubsystem hinweisen.

PAGEIOLATCH_SH

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

PAGEIOLATCH_UP

Tritt auf, wenn ein Task auf einen Latch für einen Puffer in einer E/A-Anforderung wartet. Die Latchanforderung befindet sich im Updatemodus. Lange Wartezeiten können auf Probleme mit dem Datenträgersubsystem hinweisen.

WRITELOG

Tritt auf, wenn ein Task auf den Abschluss einer Transaktionsprotokollleerung wartet. Eine Leerung tritt auf, wenn der Protokoll-Manager seinen temporären Inhalt auf den Datenträger schreibt. Häufige Vorgänge, die Protokollleerungen verursachen, sind Transaktionscommits und Prüfpunkte.

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

  • Transaktionsprotokolldatenträgerlatenz: Dies ist die häufigste Ursache für WRITELOG Wartezeiten. Im Allgemeinen wird empfohlen, die Daten- und Protokolldateien auf separaten Volumes zu speichern. Transaktionsprotokollschreibvorgänge sind sequenzielle Schreibvorgänge, während das Lesen oder Schreiben von Daten aus einer Datendatei zufällig erfolgt. Das Mischen von Daten- und Protokolldateien auf einem Laufwerkvolume (insbesondere bei herkömmlichen rotierenden Laufwerken) führt zu übermäßigen Datenträgerkopfbewegungen.

  • 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. eine lange Wiederherstellung.

  • Zu viele kleine Transaktionen: Während große Transaktionen zu Blockierungen führen können, können zu viele kleine Transaktionen zu anderen Problemen führen. Wenn Sie eine Transaktion nicht explizit starten, führt jedes Einfügen, Löschen oder Aktualisieren zu einer Transaktion (wir nennen diese automatische Transaktion). Wenn Sie 1.000 Einfügungen in einer Schleife ausführen, werden 1.000 Transaktionen generiert. Jede Transaktion in diesem Beispiel muss committet werden, was zu einer Leerung des Transaktionsprotokolls und 1.000 Transaktionsleerungen führt. Gruppieren Sie nach Möglichkeit einzelne Aktualisierungen, Löschungen oder Einfügen in eine größere Transaktion, um Transaktionsprotokollleerungen zu reduzieren und die Leistung zu steigern. 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-Auslastung) auftreten, können sowohl der Log Writer-Thread als auch die Protokollleerungen verzögert werden. In SQL Server 2016 wurden bis zu vier Log Writer-Threads hinzugefügt, um den Protokollschreibdurchsatz zu erhöhen. Weitere Informationen finden Sie unter SQL 2016 – Es wird einfach schneller ausgeführt: Mehrere Protokollwriter-Worker. In SQL Server 2019 wurden bis zu acht Log Writer-Threads hinzugefügt, was den Durchsatz noch mehr verbessert. Darüber hinaus kann in SQL Server 2019 jeder reguläre Arbeitsthread Schreibvorgänge direkt protokollieren, anstatt im Log Writer-Thread zu veröffentlichen. Mit diesen Verbesserungen WRITELOG werden Wartezeiten nur selten durch Zeitplanungsprobleme ausgelöst.

ASYNC_IO_COMPLETION

Tritt auf, wenn einige der folgenden E/A-Aktivitäten ausgeführt werden:

  • Der Masseneinfügungsanbieter ("Bulk einfügen") verwendet diesen Wartetyp bei der E/A-Ausführung.
  • Lesen der Rückgängig-Datei in LogShipping und Weiterleiten von Asynchronen E/A-Vorgängen 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 E/A-Vorgänge, die nicht mit Datenseiten (Puffern) verknüpft sind. Dazu gehören:

  • Lesen und Schreiben von Sortier-/Hashergebnissen vom/auf den Datenträger während eines Überlaufs (Überprüfen der Leistung des tempdb-Speichers ).
  • Lesen und Schreiben von Eager-Spools auf dem Datenträger (überprüfen Sie den tempdb-Speicher ).
  • Lesen von Protokollblöcken aus dem Transaktionsprotokoll (während eines Vorgangs, 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 eine Datenbank Momentaufnahme (Copy-On-Write)
  • Schließen der Datenbankdatei und dekomprimierung der Datei.

BACKUPIO

Tritt auf, wenn ein Sicherungstask auf Daten wartet oder auf einen Puffer zum Speichern von Daten wartet. Dieser Typ ist nicht typisch, außer wenn ein Task auf eine Bandeinbindung wartet.