Problembehandlung bei einer Abfrage, die einen erheblichen Leistungsunterschied zwischen zwei Servern anzeigt
Gilt für: SQL Server
Dieser Artikel enthält Schritte zur Problembehandlung für ein Leistungsproblem, bei dem eine Abfrage auf einem Server langsamer ausgeführt wird als auf einem anderen Server.
Symptome
Angenommen, es gibt zwei Server, auf denen SQL Server installiert sind. Eine der SQL Server-Instanzen enthält eine Kopie einer Datenbank in der anderen SQL Server instance. Wenn Sie eine Abfrage für die Datenbanken auf beiden Servern ausführen, wird die Abfrage auf einem Server langsamer ausgeführt als auf dem anderen.
Mit den folgenden Schritten können Sie dieses Problem beheben.
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ät für Abfragen aus, die folgendes sind:
- 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 lang, um Daten darauf zu erfassen (z. B. wählen Sie anstelle einer 5-Millisekunden-Abfrage eine Abfrage mit 10 Sekunden 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ät für Abfragen aus, die folgendes sind:
Analysieren von Leistungsdaten mit SQL Nexus.
- Sammeln Sie PSSDiag/SQLdiag - oder SQL LogScout-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 Leistungsvergleich zwischen zwei Protokollsammlungen (z. B. langsam und schnell).
Szenario 1: Nur eine einzelne Abfrage wird auf den beiden Servern unterschiedlich ausgeführt
Wenn nur eine Abfrage unterschiedlich ausgeführt wird, ist das Problem wahrscheinlicher für die einzelne Abfrage und nicht für die Umgebung. Fahren Sie in diesem Fall mit Schritt 2: Sammeln von Daten fort, 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 langsamer ausgeführt werden als der andere, sind die wahrscheinlichsten Ursachen die Unterschiede in der Server- oder Datenumgebung. Wechseln Sie zu Diagnose von Umgebungsunterschieden , und überprüfen Sie, ob der Vergleich zwischen den beiden Servern gültig ist.
Schritt 2: Sammeln von Daten und Bestimmen des Typs des Leistungsproblems
Erfassen verstrichener Zeit, CPU-Zeit und logischer Lesevorgänge
Um die verstrichene Zeit und CPU-Zeit der Abfrage auf beiden Servern zu erfassen, 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 für frühere 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) DESCHinweis
Wenn
avg_wait_timeein negativer Wert angezeigt wird, handelt es sich um eine parallele Abfrage.Wenn Sie die Abfrage bedarfsgesteuert 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 OFFIn Nachrichten werden dann die CPU-Zeit, die verstrichene Zeit und logische Lesevorgänge wie folgt angezeigt:
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 Include Actual Execution Plan (Tatsächlichen Ausführungsplan einschließen ) aus.
Wählen Sie unter Ausführungsplan den Operator ganz links aus.
Erweitern Sie unter Eigenschaften 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 nahe, 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, dass es sich um eine CPU-gebundene Abfrage handelt.
Beispiele für ausgeführte (CPU-gebundene) Abfragen:
| Verstrichene Zeit (ms) | CPU-Zeit (ms) | Lesevorgänge (logisch) |
|---|---|---|
| 3200 | 3000 | 300000 |
| 1080 | 1000 | 20 |
Logische Lesevorgänge – Das 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-Nutzung aus anderen Quellen stammt: einer While-Schleife (in T-SQL oder anderem Code wie XProcs oder SQL CRL-Objekten). Das zweite Beispiel in der Tabelle veranschaulicht ein solches Szenario, bei dem der Großteil der CPU nicht aus Lesevorgängen stammt.
Hinweis
Wenn die CPU-Zeit größer als die Dauer ist, deutet dies darauf hin, dass eine parallele Abfrage ausgeführt wird. Mehrere Threads verwenden die CPU gleichzeitig. Weitere Informationen finden Sie unter Parallele Abfragen – Runner oder Kellner.
Typ 2: Warten auf einen Engpass (Kellner)
Eine Abfrage wartet auf einen Engpass, wenn die verstrichene Zeit erheblich größer als die CPU-Zeit ist. Die verstrichene Zeit umfasst die Zeit zum Ausführen 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 und die CPU-Zeit 300 ms beträgt, beträgt die Wartezeit 1700 ms (2000 - 300 = 1700). Weitere Informationen finden Sie unter Typen von Wartevorgängen.
Beispiele für wartende Abfragen:
| Verstrichene Zeit (ms) | CPU-Zeit (ms) | Lesevorgänge (logisch) |
|---|---|---|
| 2000 | 300 | 28000 |
| 10080 | 700 | 80000 |
Parallele Abfragen: Runner oder Kellner
Parallele Abfragen verbrauchen möglicherweise mehr CPU-Zeit als die Gesamtdauer. Das Ziel der Parallelität besteht darin, mehreren Threads die gleichzeitige Ausführung von Teilen einer Abfrage zu ermöglichen. In einer Sekunde der Uhr kann eine Abfrage acht Sekunden CPU-Zeit verwenden, indem acht parallele Threads ausgeführt werden. Daher ist es schwierig, eine CPU-gebundene oder wartende Abfrage basierend auf der verstrichenen Zeit und der CPU-Zeitdifferenz zu ermitteln. Befolgen Sie jedoch in der Regel die in den beiden obigen Abschnitten aufgeführten Prinzipien. Die Zusammenfassung lautet:
- Wenn die verstrichene Zeit viel größer als die CPU-Zeit ist, betrachten Sie es als Kellner.
- Wenn die CPU-Zeit viel größer als die verstrichene Zeit ist, betrachten Sie sie als Runner.
Beispiele für parallele Abfragen:
| Verstrichene Zeit (ms) | CPU-Zeit (ms) | Lesevorgänge (logisch) |
|---|---|---|
| 1200 | 8100 | 850000 |
| 3080 | 12300 | 1500000 |
Schritt 3: Vergleichen von Daten von beiden Servern, Ermitteln des Szenarios und Beheben des Problems
Angenommen, es gibt zwei Computer namens Server1 und Server2. 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, das am besten zu Ihren passt, in den folgenden Abschnitten.
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 eng mit der CPU-Zeit auf beiden Servern übereinstimmt, gibt es keine größeren Wartezeiten oder Engpässe. Die Erhöhung der CPU-Zeit auf Server1 wird höchstwahrscheinlich durch eine Zunahme logischer Lesevorgänge verursacht. Eine erhebliche Änderung bei logischen Lesevorgängen weist in der Regel auf einen Unterschied bei Abfrageplänen hin. Zum Beispiel:
| Server | Verstrichene Zeit (ms) | CPU-Zeit (ms) | Lesevorgänge (logisch) |
|---|---|---|---|
| Server1 | 3100 | 3000 | 300000 |
| Server2 | 1100 | 1000 | 90200 |
Aktion: Überprüfen von Ausführungsplänen und Umgebungen
- Vergleichen Sie ausführungspläne der Abfrage auf beiden Servern. Verwenden Sie dazu eine der beiden Methoden:
- Visueller Vergleich von Ausführungsplänen. Weitere Informationen finden Sie unter Anzeigen eines tatsächlichen Ausführungsplans.
- Speichern Sie die Ausführungspläne, und vergleichen Sie sie mithilfe SQL Server Management Studio Planvergleichsfeatures.
- Vergleichen Sie Umgebungen. Unterschiedliche Umgebungen können zu Abfrageplanunterschieden oder direkten Unterschieden bei der CPU-Auslastung führen. Zu den Umgebungen gehören Serverversionen, Datenbank- oder Serverkonfigurationseinstellungen, Ablaufverfolgungsflags, CPU-Anzahl oder Taktgeschwindigkeit sowie virtueller Computer im Vergleich zu physischen Computern. Weitere Informationen finden Sie unter Diagnostizieren von Abfrageplanunterschieden .
Szenario 2: Die Abfrage ist ein Kellner 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 damit, auf einen Engpass zu warten. Zum Beispiel:
| Server | Verstrichene Zeit (ms) | CPU-Zeit (ms) | Lesevorgänge (logisch) |
|---|---|---|---|
| Server1 | 4500 | 1000 | 90200 |
| Server2 | 1100 | 1000 | 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 Wartevorgänge sind Blockieren (Sperrenwartevorgänge), Latchwartevorgänge, Datenträger-E/A-Wartevorgänge, Netzwerkwartevorgänge und Arbeitsspeicherwartevorgänge. Um häufige Engpassprobleme zu beheben, fahren Sie mit Diagnostizieren von Wartezeiten oder Engpässen fort.
Szenario 3: Die Abfragen auf beiden Servern sind Kellner, aber die Wartetypen oder -zeiten unterscheiden sich.
Zum Beispiel:
| Server | Verstrichene Zeit (ms) | CPU-Zeit (ms) | Lesevorgänge (logisch) |
|---|---|---|---|
| Server1 | 8000 | 1000 | 90200 |
| Server2 | 3000 | 1000 | 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 darauf hindeutet, dass die 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 für 7000 ms, während sie auf E/A auf Server2 2000 ms wartet.
Aktion: Überprüfen der Wartetypen auf beiden Servern
Beheben Sie jeden Engpass einzeln auf jedem Server, und beschleunigen Sie die Ausführung auf beiden Servern. Die Behandlung dieses Problems 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 Diagnostizieren von Wartezeiten oder Engpässen fort.
Szenario 4: Die Abfrage auf Server1 verwendet mehr CPU-Zeit als auf Server2, aber die logischen Lesevorgänge sind geschlossen.
Zum Beispiel:
| Server | Verstrichene Zeit (ms) | CPU-Zeit (ms) | Lesevorgänge (logisch) |
|---|---|---|---|
| Server1 | 3000 | 3000 | 90200 |
| Server2 | 1000 | 1000 | 90200 |
Wenn die Daten die folgenden Bedingungen erfüllen:
- Die CPU-Zeit auf Server1 ist viel größer als auf Server2.
- Die verstrichene Zeit stimmt genau mit der CPU-Zeit auf jedem Server überein, was auf keine Wartezeiten hinweist.
- Die logischen Lesevorgänge, in der Regel der höchste Treiber der CPU-Zeit, sind auf beiden Servern ähnlich.
Die zusätzliche CPU-Zeit stammt dann aus einigen anderen CPU-gebundenen Aktivitäten. Dieses Szenario ist das 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 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 ist in der Regel die Ursache, wenn andere Bedingungen auf Server1 und Server2 unterschiedlich sind, z. B. Datengröße, CPU-Taktgeschwindigkeit oder Energiesparplan.
- SQL Server CLR-Integration oder erweiterte gespeicherte Prozeduren (Extended Stored Procedures, XPs), die die CPU ansteuern, aber keine logischen Lesevorgänge ausführen. Unterschiede in den DLLs können zu unterschiedlichen CPU-Zeiten führen.
- Unterschied in SQL Server CPU-gebundenen Funktionalität (z. B. Code zur Zeichenfolgenbearbeitung).
Aktion: Überprüfen von Ablaufverfolgungen und Abfragen
Überprüfen Sie Ablaufverfolgungen auf beiden Servern auf 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 jedoch Textfilter aus der Abfrage, falls vorhanden.
Überprüfen Sie, ob die Abfrage UDFs verwendet, die Zeichenfolgenbearbeitungen oder umfangreiche Verarbeitungen für Datenspalten in der
SELECTListe durchführen.Überprüfen Sie, ob die Abfrage Schleifen, Funktionsrekursionen 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 dieselbe Buildversion?
Andernfalls können einige Korrekturen vorgenommen werden, die die Unterschiede verursacht haben. 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. Wenn mehr Arbeitsspeicher zum Zwischenspeichern von Daten-/Indexseiten und Abfrageplänen verfügbar ist, könnte die Abfrage je nach Verfügbarkeit von Hardwareressourcen anders optimiert werden.
Sind CPU-bezogene Hardwarekonfigurationen auf beiden Servern ähnlich? Zum Beispiel:
Die Anzahl der CPUs variiert je nach Computer (24 CPUs auf einem Computer gegenüber 96 CPUs auf dem anderen).
Energiesparpläne – ausgeglichen im Vergleich zu hoher Leistung.
Virtueller Computer (VM) im Vergleich zu physischen (Bare-Metal)-Computern.
Hyper-V im Vergleich zu VMware – Unterschiede bei der Konfiguration.
Taktfrequenzdifferenz (niedrigere Taktgeschwindigkeit im Vergleich zu höherer Taktfrequenz). Beispielsweise können 2 GHz im Vergleich zu 3,5 GHz einen Unterschied machen. Führen Sie den folgenden PowerShell-Befehl aus, um die Taktgeschwindigkeit auf einem Server abzurufen:
Get-CimInstance Win32_Processor | Select-Object -Expand MaxClockSpeed
Verwenden Sie eine der folgenden beiden Möglichkeiten, um die CPU-Geschwindigkeit der Server zu testen. Wenn sie keine vergleichbaren Ergebnisse liefern, liegt das Problem außerhalb SQL Server. Dies kann ein Energiesparplanunterschied, weniger CPUs, VM-Softwareproblem oder Taktfrequenzunterschied sein.
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 Wartevorgängen oder Engpässen
Um eine Abfrage zu optimieren, die auf Engpässe wartet, identifizieren Sie, wie lange die Wartezeit dauert und wo der Engpass ist (der Wartetyp). Nachdem der Wartetyp bestätigt wurde, verringern Sie die Wartezeit, oder beseitigen Sie die Wartezeit vollständig.
Um die ungefähre Wartezeit zu berechnen, subtrahieren Sie die CPU-Zeit (Workerzeit) 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 | 1000 | 6080 |
Identifizieren des Engpasses oder Wartens
Führen Sie die folgende Abfrage aus, >um Abfragen mit langer Wartezeit zu identifizieren (z. B. 20 % der insgesamt verstrichenen Wartezeit). Diese Abfrage verwendet Leistungsstatistiken für zwischengespeicherte Abfragepläne seit Beginn der SQL Server.
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 aktuell ausgeführte Abfragen mit Wartezeiten 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 erfassen können, überprüfen Sie die WaitStats aus den Ausführungsplaneigenschaften in SSMS:
- Führen Sie die Abfrage mit Include Actual Execution Plan (Tatsächlichen Ausführungsplan einschließen ) aus.
- Klicken Sie auf der Registerkarte Ausführungsplan mit der rechten Maustaste auf den Operator ganz links.
- Wählen Sie Eigenschaften und dann WaitStats-Eigenschaft aus.
- Überprüfen Sie waitTimeMs und WaitType.
Wenn Sie mit PSSDiag/SQLdiag- oder SQL LogScout LightPerf/GeneralPerf-Szenarien vertraut sind, sollten Sie eines dieser Szenarien verwenden, um Leistungsstatistiken zu sammeln und wartende Abfragen für Ihre SQL Server instance zu identifizieren. Sie können die gesammelten Datendateien importieren und die Leistungsdaten mit SQL Nexus analysieren.
Verweise zur Vermeidung oder Reduzierung von Wartezeiten
Die Ursachen und Lösungen für jeden Wartetyp variieren. Es gibt keine allgemeine Methode, um alle Wartetypen aufzulösen. Hier finden Sie Artikel zur Problembehandlung und Behebung häufiger Wartetypprobleme:
- Verstehen und Beheben von Blockierungsproblemen (LCK_M_*)
- Verstehen und Beheben von Problemen beim Blockieren von Azure SQL Datenbanken
- Problembehandlung für langsame SQL Server durch E/A-Probleme verursachte Leistung (PAGEIOLATCH_*, WRITELOG, IO_COMPLETION, BACKUPIO)
- Beheben des Konflikts beim Einfügen der letzten Seite PAGELATCH_EX in SQL Server
- Erläuterungen und Lösungen für Speicherzuweisungen (RESOURCE_SEMAPHORE)
- Problembehandlung bei langsamen Abfragen, die sich aus ASYNC_NETWORK_IO Wartetyp ergeben
- Problembehandlung bei HADR_SYNC_COMMIT Wartetyp mit Always On Verfügbarkeitsgruppen
- Funktionsweise: CMEMTHREAD und Debuggen
- Machen Sie Parallelitätswartevorgänge handlungsfähig (CXPACKET und CXCONSUMER)
- THREADPOOL-Wartevorgang
Beschreibungen vieler Wartetypen und deren Angabe finden Sie in der Tabelle unter Typen von Wartevorgängen.
Diagnostizieren von Abfrageplanunterschieden
Im Folgenden finden Sie einige häufige Ursachen für Unterschiede in Abfrageplänen:
Unterschiede bei 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 unterschiedlichen Abfrageplänen führen. Beispielsweise unterscheidet sich das Verknüpfen von Tabelle T1 (1000 Zeilen) mit Tabelle T2 (2.000.000 Zeilen) vom Verknüpfen von Tabelle T1 (100 Zeilen) mit Tabelle T2 (2.000.000 Zeilen). Art und Geschwindigkeit des Vorgangs
JOINkönnen sich erheblich unterscheiden.Statistikunterschiede
Wurden Statistiken für eine Datenbank und nicht für die andere Datenbank aktualisiert? Wurden Statistiken mit einer anderen Stichprobenrate aktualisiert (z. B. 30 % gegenüber 100 % vollständiger Scan)? Stellen Sie sicher, dass Sie Statistiken auf beiden Seiten mit der gleichen Abtastrate aktualisieren.
Unterschiede im Datenbank-Kompatibilitätsgrad
Überprüfen Sie, ob die Kompatibilitätsgrade der Datenbanken zwischen den beiden Servern unterschiedlich sind. Führen Sie die folgende Abfrage aus, um den Datenbank-Kompatibilitätsgrad 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 ein Server SQL Server Version 2014 und der andere SQL Server Version 2016? Möglicherweise gibt es Produktänderungen, 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ätsschätzung (Kardinality Estimator, CE)
Überprüfen Sie, ob die Legacykardinalitätsschätzung auf Datenbankebene aktiviert ist. Weitere Informationen zur Kardinalitätsschätzung finden Sie unter Kardinalitätsschätzung (SQL Server).
SELECT name, value, is_value_default FROM sys.database_scoped_configurations WHERE name = 'LEGACY_CARDINALITY_ESTIMATION'Optimierer-Hotfixes aktiviert/deaktiviert
Wenn die Hotfixes für den Abfrageoptimierer auf einem Server aktiviert, aber auf dem anderen deaktiviert sind, können verschiedene Abfragepläne generiert werden. Weitere Informationen finden Sie unter SQL Server Ablaufverfolgungsflag 4199 des Abfrageoptimierer-Hotfixes.
Führen Sie die folgende Abfrage aus, um den Status von Hotfixes für den Abfrageoptimierer 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'Unterschiede bei Ablaufverfolgungsflags
Einige Ablaufverfolgungsflags wirken sich auf die Auswahl des Abfrageplans aus. Überprüfen Sie, ob auf einem Server Ablaufverfolgungsflags aktiviert sind, die auf dem anderen Server 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 je nach Abfrageoptimierer
Überprüfen Sie die
OptimizerHardwareDependentPropertieseines Abfrageplans, und überprüfen Sie, ob Hardwareunterschiede für verschiedene 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>%'Timeout des Optimierers
Gibt es ein Timeoutproblem beim Optimierer ? Der Abfrageoptimierer kann die Auswertung von Planoptionen beenden, wenn die ausgeführte Abfrage zu komplex ist. Wenn er beendet wird, wählt er den Plan mit den niedrigsten Kosten aus, die zu diesem Zeitpunkt verfügbar sind. Dies kann zu einer willkürlichen Planauswahl auf einem Server gegen einen anderen führen.
SET-Optionen
Einige SET-Optionen wirken sich auf den Plan aus, z. B. SET ARITHABORT. Weitere Informationen finden Sie unter SET-Optionen.
Unterschiede zwischen Abfragehinweisen
Verwendet eine Abfrage Abfragehinweise und die andere nicht? Überprüfen Sie den Abfragetext manuell, um das Vorhandensein von Abfragehinweisen zu ermitteln.
Parameterabhängige Pläne (Problem bei der Parameterermittlung)
Testen Sie die Abfrage mit genau den gleichen Parameterwerten? Wenn nicht, können Sie dort beginnen. Wurde der Plan zuvor auf einem Server basierend auf einem anderen Parameterwert kompiliert? Testen Sie die beiden Abfragen mithilfe des Abfragehinweises RECOMPILE, um sicherzustellen, dass keine Planwiederverwendung stattfindet. Weitere Informationen finden Sie unter Untersuchen und Beheben von problemen mit Parametern.
Verschiedene Datenbankoptionen/bereichsbezogene Konfigurationseinstellungen
Werden die gleichen Datenbankoptionen oder bereichsbezogenen Konfigurationseinstellungen auf beiden Servern verwendet? Einige Datenbankoptionen können die Planauswahl beeinflussen. Beispiel: Datenbankkompatibilität, Legacy-CE im Vergleich zu Standard-CE und Parameterermittlung. Führen Sie die folgende Abfrage auf einem Server aus, um die Datenbankoptionen zu vergleichen, die auf den beiden Servern verwendet werden:
-- 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 Planhinweislisten für Ihre Abfragen auf einem Server verwendet, aber nicht auf dem anderen? Führen Sie die folgende Abfrage aus, um Unterschiede zu ermitteln:
SELECT * FROM sys.plan_guides
Feedback
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Issues stufenweise als Feedbackmechanismus für Inhalte abbauen und durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter https://aka.ms/ContentUserFeedback.
Feedback senden und anzeigen für