Problembehandlung bei einer Abfrage, die einen erheblichen Leistungsunterschied zwischen zwei Servern zeigt
Gilt für: SQL Server
Dieser Artikel enthält Schritte zur Problembehandlung für ein Leistungsproblem, bei dem eine Abfrage langsamer auf einem Server als auf einem anderen Server ausgeführt wird.
Problembeschreibung
Gehen Sie davon aus, dass zwei Server mit SQL Server installiert sind. Eine der SQL Server-Instanzen enthält eine Kopie einer Datenbank in der anderen SQL Server-Instanz. Wenn Sie eine Abfrage für die Datenbanken auf beiden Servern ausführen, wird die Abfrage langsamer auf einem Server als auf dem anderen ausgeführt.
Die folgenden Schritte können ihnen bei der Problembehandlung helfen.
Schritt 1: Ermitteln, ob es sich um ein häufiges Problem mit mehreren Abfragen handelt
Verwenden Sie eine der folgenden beiden Methoden, um die Leistung für zwei oder mehr Abfragen auf den beiden Servern zu vergleichen:
Testen Sie die Abfragen manuell auf beiden Servern:
- Wählen Sie mehrere Abfragen für Tests mit prioritätsgeordneten Abfragen aus:
- Deutlich schneller auf einem Server als auf dem anderen.
- Wichtig für den Benutzer/die Anwendung.
- Häufig ausgeführt oder entwickelt, um das Problem bei Bedarf zu reproduzieren.
- Ausreichend lange zum Erfassen von Daten (z. B. anstelle einer 5-Millisekunden-Abfrage, wählen Sie eine 10-Sekunden-Abfrage aus).
- Führen Sie die Abfragen auf den beiden Servern aus.
- Vergleichen Sie die verstrichene Zeit (Dauer) auf zwei Servern für jede Abfrage.
- Wählen Sie mehrere Abfragen für Tests mit prioritätsgeordneten Abfragen aus:
Analysieren Sie Leistungsdaten mit SQL Nexus.
- Sammeln Sie PSSDiag/SQLdiag - oder SQL LogFinder-Daten für die Abfragen auf den beiden Servern.
- Importieren Sie die gesammelten Datendateien mit SQL Nexus, und vergleichen Sie die Abfragen von den beiden Servern. Weitere Informationen finden Sie unter Performance Comparison between two log collections (Slow and Fast for example).
Szenario 1: Nur eine einzelne Abfrage wird auf den beiden Servern unterschiedlich ausgeführt.
Wenn nur eine Abfrage anders ausgeführt wird, ist das Problem eher spezifisch für die einzelne Abfrage als für die Umgebung. Wechseln Sie in diesem Fall zu Schritt 2: Sammeln sie Daten, und bestimmen Sie den Typ des Leistungsproblems.
Szenario 2: Mehrere Abfragen werden auf den beiden Servern unterschiedlich ausgeführt
Wenn mehrere Abfragen auf einem Server als dem anderen langsamer ausgeführt werden, ist die wahrscheinlichste Ursache die Unterschiede in der Server- oder Datenumgebung. Wechseln Sie zu "Umgebungsunterschiede diagnostizieren", und überprüfen Sie, ob der Vergleich zwischen den beiden Servern gültig ist.
Schritt 2: Sammeln von Daten und Ermitteln des Typs des Leistungsproblems
Erfassen von verstrichener Zeit, CPU-Zeit und logischen Lesevorgängen
Um verstrichene Zeit und CPU-Zeit der Abfrage auf beiden Servern zu sammeln, verwenden Sie eine der folgenden Methoden, die am besten zu Ihrer Situation passen:
Überprüfen Sie für derzeit ausgeführte Anweisungen total_elapsed_time und cpu_time Spalten in sys.dm_exec_requests. Führen Sie die folgende Abfrage aus, um die Daten abzurufen:
SELECT req.session_id , req.total_elapsed_time AS duration_ms , req.cpu_time AS cpu_time_ms , req.total_elapsed_time - req.cpu_time AS wait_time , req.logical_reads , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE req.statement_end_offset END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text FROM sys.dm_exec_requests AS req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST ORDER BY total_elapsed_time DESC;Überprüfen Sie bei früheren Ausführungen der Abfrage last_elapsed_time und last_worker_time Spalten in sys.dm_exec_query_stats. Führen Sie die folgende Abfrage aus, um die Daten abzurufen:
SELECT t.text, (qs.total_elapsed_time/1000) / qs.execution_count AS avg_elapsed_time, (qs.total_worker_time/1000) / qs.execution_count AS avg_cpu_time, ((qs.total_elapsed_time/1000) / qs.execution_count ) - ((qs.total_worker_time/1000) / qs.execution_count) AS avg_wait_time, qs.total_logical_reads / qs.execution_count AS avg_logical_reads, qs.total_logical_writes / qs.execution_count AS avg_writes, (qs.total_elapsed_time/1000) AS cumulative_elapsed_time_all_executions FROM sys.dm_exec_query_stats qs CROSS apply sys.Dm_exec_sql_text (sql_handle) t WHERE t.text like '<Your Query>%' -- Replace <Your Query> with your query or the beginning part of your query. The special chars like '[','_','%','^' in the query should be escaped. ORDER BY (qs.total_elapsed_time / qs.execution_count) DESCNotiz
Wenn
avg_wait_timeein negativer Wert angezeigt wird, handelt es sich um eine parallele Abfrage.Wenn Sie die Abfrage bei Bedarf in SQL Server Management Studio (SSMS) oder Azure Data Studio ausführen können, führen Sie sie mit SET STATISTICS TIME
ONund SET STATISTICS IOONaus.SET STATISTICS TIME ON SET STATISTICS IO ON <YourQuery> SET STATISTICS IO OFF SET STATISTICS TIME OFFDann sehen Sie aus Nachrichten die CPU-Zeit, die verstrichene Zeit und logische Lesevorgänge wie folgt:
Table 'tblTest'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. SQL Server Execution Times: CPU time = 460 ms, elapsed time = 470 ms.Wenn Sie einen Abfrageplan sammeln können, überprüfen Sie die Daten aus den Eigenschaften des Ausführungsplans.
Führen Sie die Abfrage mit "Ist-Ausführungsplan einschließen" aus.
Wählen Sie den am weitesten links angeordneten Operator aus dem Ausführungsplan aus.
Erweitern Sie in "Properties" die QueryTimeStats-Eigenschaft.
Überprüfen Sie "ElapsedTime " und "CpuTime".
Vergleichen Sie die verstrichene Zeit und die CPU-Zeit der Abfrage, um den Problemtyp für beide Server zu ermitteln.
Typ 1: CPU-gebunden (Runner)
Wenn die CPU-Zeit geschlossen ist, gleich oder höher als die verstrichene Zeit ist, können Sie sie als CPU-gebundene Abfrage behandeln. Wenn die verstrichene Zeit beispielsweise 3000 Millisekunden (ms) beträgt und die CPU-Zeit 2900 ms beträgt, bedeutet dies, dass der großteil der verstrichenen Zeit für die CPU aufgewendet wird. Dann können wir sagen, es ist eine CPU-gebundene Abfrage.
Beispiele für die Ausführung von (CPU-gebundenen) Abfragen:
| Verstrichene Zeit (ms) | CPU-Zeit (ms) | Lesevorgänge (logisch) |
|---|---|---|
| 3200 | 3000 | 300000 |
| 1080 | 1.000 | 20 |
Logische Lesevorgänge – Lesen von Daten-/Indexseiten im Cache – sind am häufigsten die Treiber der CPU-Auslastung in SQL Server. Es kann Szenarien geben, in denen die CPU-Verwendung aus anderen Quellen stammt: eine While-Schleife (in T-SQL oder einem anderen Code wie XProcs oder SQL-CRL-Objekte). Das zweite Beispiel in der Tabelle veranschaulicht ein solches Szenario, bei dem der Großteil der CPU nicht aus Lesevorgängen stammt.
Notiz
Wenn die CPU-Zeit größer als die Dauer ist, gibt dies an, dass eine parallele Abfrage ausgeführt wird. Mehrere Threads verwenden die CPU gleichzeitig. Weitere Informationen finden Sie unter "Parallele Abfragen – Runner" oder "Waiter".
Typ 2: Warten auf einen Engpass (Waiter)
Eine Abfrage wartet auf einen Engpass, wenn die verstrichene Zeit deutlich größer als die CPU-Zeit ist. Die verstrichene Zeit umfasst die Ausführung der Abfrage auf der CPU (CPU-Zeit) und die Zeit, die auf die Freigabe einer Ressource (Wartezeit) wartet. Wenn die verstrichene Zeit beispielsweise 2000 ms beträgt und die CPU-Zeit 300 ms beträgt, beträgt die Wartezeit 1700 ms (2000 - 300 = 1700). Weitere Informationen finden Sie unter "Waits Types".
Beispiele für Warteabfragen:
| Verstrichene Zeit (ms) | CPU-Zeit (ms) | Lesevorgänge (logisch) |
|---|---|---|
| 2.000 | 300 | 28000 |
| 10080 | 700 | 80.000 |
Parallele Abfragen – Runner oder Waiter
Parallele Abfragen verwenden möglicherweise mehr CPU-Zeit als die Gesamtdauer. Das Ziel der Parallelität besteht darin, mehreren Threads gleichzeitig die Ausführung von Teilen einer Abfrage zu ermöglichen. In einer Sekunde der Taktzeit kann eine Abfrage acht Sekunden CPU-Zeit verwenden, indem sie acht parallele Threads ausführen. Daher wird es schwierig, eine CPU-gebundene oder eine Warteabfrage basierend auf der verstrichenen Zeit und CPU-Zeitdifferenz zu ermitteln. Beachten Sie jedoch in der Regel die in den obigen beiden Abschnitten aufgeführten Grundsätze. Die Zusammenfassung lautet:
- Wenn die verstrichene Zeit viel größer als die CPU-Zeit ist, sollten Sie es als Warteprogramm betrachten.
- Wenn die CPU-Zeit viel größer als die verstrichene Zeit ist, sollten Sie es als Läufer betrachten.
Beispiele für parallele Abfragen:
| Verstrichene Zeit (ms) | CPU-Zeit (ms) | Lesevorgänge (logisch) |
|---|---|---|
| 1200 | 8100 | 850000 |
| 3080 | 12300 | 1.500.000 |
Schritt 3: Vergleichen von Daten von beiden Servern, Ermitteln des Szenarios und Beheben des Problems
Angenommen, es gibt zwei Computer mit dem Namen "Server1" und "Server2". Und die Abfrage wird auf Server1 langsamer ausgeführt als auf Server2. Vergleichen Sie die Zeiten von beiden Servern, und folgen Sie dann den Aktionen des Szenarios, die am besten ihren aus den folgenden Abschnitten entsprechen.
Szenario 1: Die Abfrage auf Server1 verwendet mehr CPU-Zeit, und die logischen Lesevorgänge sind auf Server1 höher als auf Server2.
Wenn die CPU-Zeit auf Server1 viel größer als auf Server2 ist und die verstrichene Zeit mit der CPU-Zeit auf beiden Servern übereinstimmt, gibt es keine großen Wartezeiten oder Engpässe. Die Erhöhung der CPU-Zeit auf Server1 wird höchstwahrscheinlich durch eine Zunahme der logischen Lesevorgänge verursacht. Eine erhebliche Änderung der logischen Lesevorgänge weist in der Regel auf einen Unterschied in Abfrageplänen hin. Beispiel:
| Server | Verstrichene Zeit (ms) | CPU-Zeit (ms) | Lesevorgänge (logisch) |
|---|---|---|---|
| Server1 | 3100 | 3000 | 300000 |
| Server2 | 1100 | 1.000 | 90200 |
Aktion: Überprüfen von Ausführungsplänen und Umgebungen
- Vergleich der Ausführungspläne der Abfrage auf beiden Servern. Verwenden Sie dazu eine der beiden Methoden:
- Vergleichen Sie Ausführungspläne visuell. Weitere Informationen finden Sie unter Anzeigen eines tatsächlichen Ausführungsplans.
- Speichern Sie die Ausführungspläne, und vergleichen Sie sie mithilfe der Sql Server Management Studio Plan Comparison-Funktion.
- Vergleichen Sie Umgebungen. Verschiedene Umgebungen können zu Abfrageplanunterschieden oder direkten Unterschieden bei der CPU-Auslastung führen. Umgebungen umfassen Serverversionen, Datenbank- oder Serverkonfigurationseinstellungen, Ablaufverfolgungskennzeichnungen, CPU-Anzahl oder Taktgeschwindigkeit und virtuelle Computer im Vergleich zum physischen Computer. Details finden Sie unter Diagnose von Abfrageplanunterschieden .
Szenario 2: Die Abfrage ist ein Waiter auf Server1, aber nicht auf Server2
Wenn die CPU-Zeiten für die Abfrage auf beiden Servern ähnlich sind, aber die verstrichene Zeit auf Server1 viel größer ist als auf Server2, verbringt die Abfrage auf Server1 viel länger , bis auf einen Engpass gewartet wird. Beispiel:
| Server | Verstrichene Zeit (ms) | CPU-Zeit (ms) | Lesevorgänge (logisch) |
|---|---|---|---|
| Server1 | 4500 | 1.000 | 90200 |
| Server2 | 1100 | 1.000 | 90200 |
- Wartezeit auf Server1: 4500 - 1000 = 3500 ms
- Wartezeit auf Server2: 1100 - 1000 = 100 ms
Aktion: Überprüfen von Wartetypen auf Server1
Identifizieren und beseitigen Sie den Engpass auf Server1. Beispiele für Wartezeiten sind Blockierung (Sperrwartevorgänge), Latch-Wartezeiten, E/A-Wartedatenträger, Netzwerkwartevorgänge und Speicherwartevorgänge. Um häufige Engpassprobleme zu beheben, fahren Sie mit der Diagnose von Wartezeiten oder Engpässen fort.
Szenario 3: Die Abfragen auf beiden Servern sind Waiters, die Wartezeittypen oder -zeiten unterscheiden sich jedoch.
Beispiel:
| Server | Verstrichene Zeit (ms) | CPU-Zeit (ms) | Lesevorgänge (logisch) |
|---|---|---|---|
| Server1 | 8.000 | 1.000 | 90200 |
| Server2 | 3000 | 1.000 | 90200 |
- Wartezeit auf Server1: 8000 - 1000 = 7000 ms
- Wartezeit auf Server2: 3000 - 1000 = 2000 ms
In diesem Fall sind die CPU-Zeiten auf beiden Servern ähnlich, was bedeutet, dass Abfragepläne wahrscheinlich identisch sind. Die Abfragen würden auf beiden Servern gleichermaßen ausgeführt, wenn sie nicht auf die Engpässe warten. Die Dauerunterschiede ergeben sich also aus den unterschiedlichen Wartezeiten. Beispielsweise wartet die Abfrage auf Sperren auf Server1 auf 7000 ms, während sie auf E/A auf Server2 für 2000 ms wartet.
Aktion: Überprüfen von Wartetypen auf beiden Servern
Beheben Sie jeden Engpass einzeln auf jedem Server, und beschleunigen Sie die Ausführung auf beiden Servern. Die Problembehandlung ist arbeitsintensiv, da Sie Engpässe auf beiden Servern beseitigen und die Leistung vergleichbar machen müssen. Um häufige Engpassprobleme zu beheben, fahren Sie mit der Diagnose von Wartezeiten oder Engpässen fort.
Szenario 4: Die Abfrage auf Server1 verwendet mehr CPU-Zeit als auf Server2, aber die logischen Lesevorgänge werden geschlossen.
Beispiel:
| Server | Verstrichene Zeit (ms) | CPU-Zeit (ms) | Lesevorgänge (logisch) |
|---|---|---|---|
| Server1 | 3000 | 3000 | 90200 |
| Server2 | 1000 | 1000 | 90200 |
Wenn die Daten den folgenden Bedingungen entsprechen:
- Die CPU-Zeit auf Server1 ist viel größer als auf Server2.
- Die verstrichene Zeit stimmt eng mit der CPU-Zeit auf jedem Server überein, was keine Wartezeiten angibt.
- Die logischen Lesevorgänge, in der Regel der höchste Treiber der CPU-Zeit, sind auf beiden Servern ähnlich.
Dann kommt die zusätzliche CPU-Zeit aus einigen anderen CPU-gebundenen Aktivitäten. Dieses Szenario ist die seltenste aller Szenarien.
Ursachen: Ablaufverfolgung, UDFs und CLR-Integration
Dieses Problem kann durch Folgendes verursacht werden:
- XEvents/SQL Server-Ablaufverfolgung, insbesondere beim Filtern nach Textspalten (Datenbankname, Anmeldename, Abfragetext usw.). Wenn die Ablaufverfolgung auf einem Server, aber nicht auf dem anderen Server aktiviert ist, kann dies der Grund für den Unterschied sein.
- Benutzerdefinierte Funktionen (UDFs) oder anderer T-SQL-Code, der CPU-gebundene Vorgänge ausführt. Dies wäre in der Regel die Ursache, wenn andere Bedingungen auf Server1 und Server2 unterschiedlich sind, z. B. Datengröße, CPU-Taktgeschwindigkeit oder Powerplan.
- SQL Server CLR-Integration oder erweiterte gespeicherte Prozeduren (Extended Stored Procedures, XPs), die die CPU steuern, aber keine logischen Lesevorgänge ausführen. Unterschiede in den DLLs können zu unterschiedlichen CPU-Zeiten führen.
- Unterschied in der SQL Server-Funktionalität, die CPU-gebunden ist (z. B. Zeichenfolgenmanipulationscode).
Aktion: Überprüfen von Ablaufverfolgungen und Abfragen
Überprüfen Sie Ablaufverfolgungen auf beiden Servern für Folgendes:
- Wenn eine Ablaufverfolgung auf Server1, aber nicht auf Server2 aktiviert ist.
- Wenn eine Ablaufverfolgung aktiviert ist, deaktivieren Sie die Ablaufverfolgung, und führen Sie die Abfrage erneut auf Server1 aus.
- Wenn die Abfrage dieses Mal schneller ausgeführt wird, aktivieren Sie die Ablaufverfolgung, entfernen Sie aber Textfilter daraus, falls vorhanden.
Überprüfen Sie, ob die Abfrage UDFs verwendet, die Zeichenfolgenmanipulationen ausführen oder umfangreiche Verarbeitungen für Datenspalten in der
SELECTListe durchführen.Überprüfen Sie, ob die Abfrage Schleifen, Funktionsrezkursionen oder Schachtelungen enthält.
Diagnostizieren von Umgebungsunterschieden
Überprüfen Sie die folgenden Fragen, und ermitteln Sie, ob der Vergleich zwischen den beiden Servern gültig ist.
Sind die beiden SQL Server-Instanzen dieselbe Version oder build?
Wenn nicht, könnte es einige Korrekturen geben, die die Unterschiede verursachten. Führen Sie die folgende Abfrage aus, um Versionsinformationen auf beiden Servern abzurufen:
SELECT @@VERSIONIst die Menge des physischen Arbeitsspeichers auf beiden Servern ähnlich?
Wenn ein Server über 64 GB Arbeitsspeicher verfügt, während der andere über 256 GB Arbeitsspeicher verfügt, wäre dies ein erheblicher Unterschied. Da mehr Arbeitsspeicher zum Zwischenspeichern von Daten-/Indexseiten und Abfrageplänen verfügbar ist, könnte die Abfrage je nach Verfügbarkeit von Hardwareressourcen unterschiedlich optimiert werden.
Sind CPU-bezogene Hardwarekonfigurationen auf beiden Servern ähnlich? Beispiel:
Die Anzahl der CPUs variiert zwischen Computern (24 CPUs auf einem Computer und 96 CPUs auf der anderen Seite).
Energiepläne – ausgeglichen im Vergleich zu hoher Leistung.
Virtueller Computer (VM) im Vergleich zu physischen (Bare-Metal)-Computern.
Hyper-V im Vergleich zu VMware – Unterschied in der Konfiguration.
Taktfrequenzunterschied (niedrigere Taktgeschwindigkeit im Vergleich zur höheren Taktgeschwindigkeit). Beispielsweise können 2 GHz im Vergleich zu 3,5 GHz einen Unterschied machen. Um die Taktgeschwindigkeit auf einem Server zu erhalten, führen Sie den folgenden PowerShell-Befehl aus:
Get-CimInstance Win32_Processor | Select-Object -Expand MaxClockSpeed
Verwenden Sie eine der folgenden beiden Methoden, um die CPU-Geschwindigkeit der Server zu testen. Wenn sie keine vergleichbaren Ergebnisse erzielen, liegt das Problem außerhalb von SQL Server. Es kann sich um einen Leistungsplanunterschied, weniger CPUs, VM-Softwareproblem oder Taktgeschwindigkeitsdifferenz handeln.
Führen Sie das folgende PowerShell-Skript auf beiden Servern aus, und vergleichen Sie die Ausgaben.
$bf = [System.DateTime]::Now for ($i = 0; $i -le 20000000; $i++) {} $af = [System.DateTime]::Now Write-Host ($af - $bf).Milliseconds " milliseconds" Write-Host ($af - $bf).Seconds " Seconds"Führen Sie den folgenden Transact-SQL-Code auf beiden Servern aus, und vergleichen Sie die Ausgaben.
SET NOCOUNT ON DECLARE @spins INT = 0 DECLARE @start_time DATETIME = GETDATE(), @time_millisecond INT WHILE (@spins < 20000000) BEGIN SET @spins = @spins +1 END SELECT @time_millisecond = DATEDIFF(millisecond, @start_time, getdate()) SELECT @spins Spins, @time_millisecond Time_ms, @spins / @time_millisecond Spins_Per_ms
Diagnostizieren von Wartezeiten oder Engpässen
Um eine Abfrage zu optimieren, die auf Engpässe wartet, identifizieren Sie, wie lange die Wartezeit ist und wo der Engpass liegt (der Wartetyp). Nachdem der Wartetyp bestätigt wurde, reduzieren Sie die Wartezeit, oder beseitigen Sie die Wartezeit vollständig.
Um die ungefähre Wartezeit zu berechnen, subtrahieren Sie die CPU-Zeit (Arbeitszeit) von der verstrichenen Zeit einer Abfrage. In der Regel ist die CPU-Zeit die tatsächliche Ausführungszeit, und der verbleibende Teil der Lebensdauer der Abfrage wartet.
Beispiele für die Berechnung der ungefähren Wartezeit:
| Verstrichene Zeit (ms) | CPU-Zeit (ms) | Wartezeit (ms) |
|---|---|---|
| 3200 | 3000 | 200 |
| 7080 | 1.000 | 6080 |
Identifizieren des Engpasses oder Wartens
Führen Sie die folgende Abfrage aus, >um historische Langewarteabfragen zu identifizieren (z. B. 20 % der gesamt verstrichenen Wartezeit). Diese Abfrage verwendet Seit dem Start von SQL Server Leistungsstatistiken für zwischengespeicherte Abfragepläne.
SELECT t.text, qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time, qs.total_worker_time / qs.execution_count AS avg_cpu_time, (qs.total_elapsed_time - qs.total_worker_time) / qs.execution_count AS avg_wait_time, qs.total_logical_reads / qs.execution_count AS avg_logical_reads, qs.total_logical_writes / qs.execution_count AS avg_writes, qs.total_elapsed_time AS cumulative_elapsed_time FROM sys.dm_exec_query_stats qs CROSS apply sys.Dm_exec_sql_text (sql_handle) t WHERE (qs.total_elapsed_time - qs.total_worker_time) / qs.total_elapsed_time > 0.2 ORDER BY qs.total_elapsed_time / qs.execution_count DESCFühren Sie die folgende Abfrage aus, um derzeit ausgeführte Abfragen mit einer Wartezeit von mehr als 500 ms zu identifizieren:
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_time > 500 AND is_user_process = 1Wenn Sie einen Abfrageplan sammeln können, überprüfen Sie die WaitStats aus den Ausführungsplaneigenschaften in SSMS:
- Führen Sie die Abfrage mit "Ist-Ausführungsplan einschließen" aus.
- Klicken Sie mit der rechten Maustaste auf den operator ganz links auf der Registerkarte "Ausführungsplan "
- Wählen Sie "Eigenschaften" und dann "WaitStats "-Eigenschaft aus.
- Überprüfen Sie die WaitTimeMs und WaitType.
Wenn Sie mit PSSDiag/SQLdiag oder SQL LogFinder LightPerf/GeneralPerf-Szenarien vertraut sind, sollten Sie eine dieser Szenarien verwenden, um Leistungsstatistiken zu sammeln und Wartende Abfragen auf Ihrer SQL Server-Instanz zu identifizieren. Sie können die gesammelten Datendateien importieren und die Leistungsdaten mit SQL Nexus analysieren.
Verweise zur Beseitigung oder Reduzierung von Wartezeiten
Die Ursachen und Auflösungen für jeden Wartetyp variieren. Es gibt keine allgemeine Methode zum Auflösen aller Wartetypen. Im Folgenden finden Sie Artikel zur Problembehandlung und Behebung häufig auftretender Wartetypprobleme:
- Verstehen und Beheben von Blockierungsproblemen (LCK_M_*)
- Verstehen und Beheben von Problemen durch Blockierungen in Azure SQL-Datenbank
- Behandeln von langsamen SQL Server-Leistung durch E/A-Probleme (PAGEIOLATCH_*, WRITELOG, IO_COMPLETION, BACKUPIO)
- Konflikt beim Einfügen von PAGELATCH_EX auf letzter Seite in SQL Server auflösen
- Arbeitsspeicher gewährt Erläuterungen und Lösungen (RESOURCE_SEMAPHORE)
- Problembehandlung für langsame Abfragen, die aus ASYNC_NETWORK_IO Wartetyp resultieren
- Problembehandlung bei HADR_SYNC_COMMIT Wartetyp mit AlwaysOn-Verfügbarkeitsgruppen
- Funktionsweise: CMEMTHREAD und Debuggen
- Parallelität wird mit Aktionen gewartet (CXPACKET und CXCONSUMER)
- THREADPOOL-Wartezeit
Beschreibungen vieler Wait-Typen und deren Angabe finden Sie in der Tabelle unter "Waits".
Diagnostizieren von Abfrageplanunterschieden
Im Folgenden finden Sie einige häufige Ursachen für Unterschiede in Abfrageplänen:
Unterschiede bei der Datengröße oder Datenwerten
Wird dieselbe Datenbank auf beiden Servern verwendet – mit derselben Datenbanksicherung? Wurden die Daten auf einem Server im Vergleich zum anderen geändert? Datenunterschiede können zu verschiedenen Abfrageplänen führen. Das Verknüpfen von Tabelle T1 (1000 Zeilen) mit Tabelle T2 (2.000.000 Zeilen) unterscheidet sich beispielsweise vom Verknüpfen von Tabelle T1 (100 Zeilen) mit Tabelle T2 (2.000.000 Zeilen). Der Typ und die Geschwindigkeit des
JOINVorgangs können erheblich unterschiedlich sein.Statistische Unterschiede
Wurden Statistiken für eine Datenbank und nicht für die andere aktualisiert? Wurden Statistiken mit einer anderen Stichprobenrate aktualisiert (z. B. 30 % im Vergleich zu 100 % vollständiger Überprüfung)? Stellen Sie sicher, dass Sie Statistiken auf beiden Seiten mit derselben Stichprobenrate aktualisieren.
Unterschiede auf Datenbankkompatibilitätsstufe
Überprüfen Sie, ob sich die Kompatibilitätsebenen der Datenbanken zwischen den beiden Servern unterscheiden. Führen Sie die folgende Abfrage aus, um die Datenbankkompatibilitätsstufe abzurufen:
SELECT name, compatibility_level FROM sys.databases WHERE name = '<YourDatabase>'Unterschiede zwischen Serverversion und Build
Unterscheiden sich die Versionen oder Builds von SQL Server zwischen den beiden Servern? Ist beispielsweise eine Server-SQL Server-Version 2014 und die andere SQL Server-Version 2016? Es könnte Produktänderungen geben, die zu Änderungen bei der Auswahl eines Abfrageplans führen können. Stellen Sie sicher, dass Sie die gleiche Version und den gleichen Build von SQL Server vergleichen.
SELECT ServerProperty('ProductVersion')Unterschiede bei der Kardinalität estimator (CE)
Überprüfen Sie, ob die Legacy-Kardinalitätsschätzung auf Datenbankebene aktiviert ist. Weitere Informationen zu CE finden Sie unter Kardinalitätsschätzung (SQL Server).For more information about CE, see Cardinality Estimation (SQL Server).
SELECT name, value, is_value_default FROM sys.database_scoped_configurations WHERE name = 'LEGACY_CARDINALITY_ESTIMATION'Optimierer-Hotfixes aktiviert/deaktiviert
Wenn die Abfrageoptimierer-Hotfixes auf einem Server aktiviert, aber auf der anderen Seite deaktiviert sind, können verschiedene Abfragepläne generiert werden. Weitere Informationen finden Sie unter SQL Server-Abfrageoptimierer-Hotfixablaufverfolgungskennzeichnung 4199-Wartungsmodell.
Führen Sie die folgende Abfrage aus, um den Status von Abfrageoptimierer-Hotfixes abzurufen:
-- Check at server level for TF 4199 DBCC TRACESTATUS (-1) -- Check at database level USE <YourDatabase> SELECT name, value, is_value_default FROM sys.database_scoped_configurations WHERE name = 'QUERY_OPTIMIZER_HOTFIXES'Ablaufverfolgung kennzeichnet Unterschiede
Einige Ablaufverfolgungskennzeichnungen wirken sich auf die Auswahl des Abfrageplans aus. Überprüfen Sie, ob auf einem Server Ablaufverfolgungskennzeichnungen aktiviert sind, die auf dem anderen nicht aktiviert sind. Führen Sie die folgende Abfrage auf beiden Servern aus, und vergleichen Sie die Ergebnisse:
-- Check at server level for trace flags DBCC TRACESTATUS (-1)Hardwareunterschiede (CPU-Anzahl, Arbeitsspeichergröße)
Führen Sie die folgende Abfrage aus, um die Hardwareinformationen abzurufen:
SELECT cpu_count, physical_memory_kb/1024/1024 PhysicalMemory_GB FROM sys.dm_os_sys_infoHardwareunterschiede gemäß dem Abfrageoptimierer
Überprüfen Sie den
OptimizerHardwareDependentPropertiesAbfrageplan, und überprüfen Sie, ob Hardwareunterschiede für unterschiedliche Pläne als signifikant angesehen werden.WITH xmlnamespaces(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT txt.text, t.OptHardw.value('@EstimatedAvailableMemoryGrant', 'INT') AS EstimatedAvailableMemoryGrant , t.OptHardw.value('@EstimatedPagesCached', 'INT') AS EstimatedPagesCached, t.OptHardw.value('@EstimatedAvailableDegreeOfParallelism', 'INT') AS EstimatedAvailDegreeOfParallelism, t.OptHardw.value('@MaxCompileMemory', 'INT') AS MaxCompileMemory FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS APPLY qp.query_plan.nodes('//OptimizerHardwareDependentProperties') AS t(OptHardw) CROSS APPLY sys.dm_exec_sql_text (CP.plan_handle) txt WHERE text Like '%<Part of Your Query>%'Optimierertimeout
Gibt es ein Timeoutproblem für Optimierer? Der Abfrageoptimierer kann die Auswertung von Planoptionen beenden, wenn die ausgeführte Abfrage zu komplex ist. Wenn er anhält, wählt er den Plan mit den niedrigsten verfügbaren Kosten zum Zeitpunkt aus. Dies kann dazu führen, was wie eine willkürliche Planauswahl auf einem Server im Vergleich zu einem anderen aussieht.
SET-Optionen
Einige SET-Optionen wirken sich auf den Plan aus, z . B. SET ARITHABORT. Weitere Informationen finden Sie unter SET-Optionen.
Unterschiede bei Abfragehinweisen
Verwendet eine Abfrage Abfragehinweise und die andere nicht? Überprüfen Sie den Abfragetext manuell, um das Vorhandensein von Abfragehinweisen festzulegen.
Parametersensitive Pläne (Problem mit Parameterniffing)
Testen Sie die Abfrage mit genau denselben Parameterwerten? Wenn nicht, können Sie dort beginnen. Wurde der Plan früher auf einem Server basierend auf einem anderen Parameterwert kompiliert? Testen Sie die beiden Abfragen mithilfe des RECOMPILE-Abfragehinweiss, um sicherzustellen, dass keine Planwiederverwendung stattfindet. Weitere Informationen finden Sie unter Untersuchen und Beheben von Problemen mit der Parametersensitivität.
Verschiedene Datenbankoptionen/Bereichskonfigurationseinstellungen
Werden auf beiden Servern dieselben Datenbankoptionen oder Einstellungen für die Bereichskonfiguration verwendet? Einige Datenbankoptionen können die Planauswahl beeinflussen. Beispiel: Datenbankkompatibilität, Legacy-CE im Vergleich zum Standard-CE und Parameterniffing. Führen Sie die folgende Abfrage von einem Server aus, um die auf den beiden Servern verwendeten Datenbankoptionen zu vergleichen:
-- On Server1 add a linked server to Server2 EXEC master.dbo.sp_addlinkedserver @server = N'Server2', @srvproduct=N'SQL Server' -- Run a join between the two servers to compare settings side by side SELECT s1.name AS srv1_config_name, s2.name AS srv2_config_name, s1.value_in_use AS srv1_value_in_use, s2.value_in_use AS srv2_value_in_use, Variance = CASE WHEN ISNULL(s1.value_in_use, '##') != ISNULL(s2.value_in_use,'##') THEN 'Different' ELSE '' END FROM sys.configurations s1 FULL OUTER JOIN [server2].master.sys.configurations s2 ON s1.name = s2.name SELECT s1.name AS srv1_config_name, s2.name AS srv2_config_name, s1.value srv1_value_in_use, s2.value srv2_value_in_use, s1.is_value_default, s2.is_value_default, Variance = CASE WHEN ISNULL(s1.value, '##') != ISNULL(s2.value, '##') THEN 'Different' ELSE '' END FROM sys.database_scoped_configurations s1 FULL OUTER JOIN [server2].master.sys.database_scoped_configurations s2 ON s1.name = s2.namePlanhinweislisten
Werden Planhandbücher für Ihre Abfragen auf einem Server, aber nicht auf dem anderen server verwendet? Führen Sie die folgende Abfrage aus, um Unterschiede festzulegen:
SELECT * FROM sys.plan_guides