Behandeln von UPDATE-Leistungsproblemen mit engen und breiten Plänen in SQL Server

Gilt für: SQL Server

Eine UPDATE Anweisung kann in einigen Fällen schneller und langsamer in anderen sein. Es gibt viele Faktoren, die zu einer solchen Varianz führen können, einschließlich der Anzahl der aktualisierten Zeilen und der Ressourcenauslastung im System (Blockieren, CPU, Arbeitsspeicher oder E/A). In diesem Artikel wird ein spezifischer Grund für die Varianz behandelt: die Auswahl des abfrageplans, der von SQL Server erstellt wurde.

Was sind schmale und breite Pläne?

Wenn Sie eine UPDATE Anweisung für eine gruppierte Indexspalte ausführen, aktualisiert SQL Server nicht nur den gruppierten Index selbst, sondern auch alle nicht gruppierten Indizes, da die nicht gruppierten Indizes den Clusterindexschlüssel enthalten.

SQL Server verfügt über zwei Optionen zum Ausführen des Updates:

• Schmaler Plan: Führen Sie die Aktualisierung des nicht gruppierten Indexes zusammen mit der Aktualisierung des gruppierten Indexschlüssels durch. Dieser einfache Ansatz ist leicht zu verstehen; aktualisieren Sie den gruppierten Index, und aktualisieren Sie dann alle nicht gruppierten Indizes gleichzeitig. SQL Server aktualisiert eine Zeile und wechselt zur nächsten, bis alle abgeschlossen sind. Dieser Ansatz wird als schmales Planupdate oder als Aktualisierung pro Zeile bezeichnet. Dieser Vorgang ist jedoch relativ teuer, da die Reihenfolge der nicht gruppierten Indexdaten, die aktualisiert werden, möglicherweise nicht in der Reihenfolge der gruppierten Indexdaten liegt. Wenn viele Indexseiten an der Aktualisierung beteiligt sind, kann eine große Anzahl zufälliger E/A-Anforderungen auftreten, wenn sich die Daten auf dem Datenträger befinden.

• Breiter Plan: Um die Leistung zu optimieren und zufällige E/A zu reduzieren, kann SQL Server einen breiten Plan auswählen. Die nicht gruppierten Indizes werden zusammen mit der gruppierten Indexaktualisierung nicht aktualisiert. Stattdessen werden alle nicht gruppierten Indexdaten zuerst im Arbeitsspeicher sortiert und dann alle Indizes in dieser Reihenfolge aktualisiert. Dieser Ansatz wird als breiter Plan bezeichnet (auch als Aktualisierung pro Index bezeichnet).

Hier sehen Sie einen Screenshot von schmalen und breiten Plänen:

Wann wählt SQL Server einen breiten Plan aus?

Zwei Kriterien müssen erfüllt sein, damit SQL Server einen breiten Plan auswählen kann:

• Die Anzahl der betroffenen Zeilen ist größer als 250.
• Die Größe der Blattebene der nicht gruppierten Indizes (Indexseitenanzahl * 8 KB) beträgt mindestens 1/1000 der Einstellung für den maximalen Serverspeicher.

Wie funktionieren schmale und breite Pläne?

Führen Sie die folgenden Schritte in der folgenden Umgebung aus, um zu verstehen, wie schmale und breite Pläne funktionieren:

• SQL Server 2019 CU11
• Max. Serverspeicher = 1.500 MB

1. Führen Sie das folgende Skript aus, um eine Tabelle mytable1 mit 41.501 Zeilen, einem gruppierten Index in Spalten c1und fünf nicht gruppierten Indizes für die restlichen Spalten zu erstellen.

   CREATE TABLE mytable1(c1 INT,c2 CHAR(30),c3 CHAR(20),c4 CHAR(30),c5 CHAR(30))
   GO
   WITH cte
   AS
   (
     SELECT ROW_NUMBER() OVER(ORDER BY c1.object_id) id FROM sys.columns CROSS JOIN sys.columns c1
   )
   INSERT mytable1
   SELECT TOP 41000 id,REPLICATE('a',30),REPLICATE('a',20),REPLICATE('a',30),REPLICATE('a',30) 
   FROM cte
   GO
   
   INSERT mytable1
   SELECT TOP 250 50000,c2,c3,c4,c5 
   FROM mytable1
   GO
   
   INSERT mytable1
   SELECT TOP 251 50001,c2,c3,c4,c5 
   FROM mytable1
   GO
   
   CREATE CLUSTERED INDEX ic1 ON mytable1(c1)
   CREATE INDEX ic2 ON mytable1(c2)
   CREATE INDEX ic3 ON mytable1(c3)
   CREATE INDEX ic4 ON mytable1(c4)
   CREATE INDEX ic5 ON mytable1(c5)
   

2. Führen Sie die folgenden drei T-SQL-Anweisungen UPDATE aus, und vergleichen Sie die Abfragepläne:

   • UPDATE mytable1 SET c1=c1 WHERE c1=1 OPTION(RECOMPILE) - eine Zeile wird aktualisiert
   • UPDATE mytable1 SET c1=c1 WHERE c1=50000 OPTION(RECOMPILE) - 250 Zeilen werden aktualisiert.
   • UPDATE mytable1 SET c1=c1 WHERE c1=50001 OPTION(RECOMPILE) - 251 Zeilen werden aktualisiert.

3. Untersuchen Sie die Ergebnisse basierend auf dem ersten Kriterium (der Schwellenwert der betroffenen Anzahl von Zeilen beträgt 250).

   Der folgende Screenshot zeigt die Ergebnisse basierend auf dem ersten Kriterium:

   

   Wie erwartet wählt der Abfrageoptimierer einen schmalen Plan für die ersten beiden Abfragen aus, da die Anzahl der betroffenen Zeilen kleiner als 250 ist. Ein breiter Plan wird für die dritte Abfrage verwendet, da die betroffene Zeilenanzahl 251 ist, die größer als 250 ist.

4. Untersuchen Sie die Ergebnisse basierend auf dem zweiten Kriterium (der Speicher der Blattindexgröße beträgt mindestens 1/1000 der Einstellung für den maximalen Serverspeicher).

   Der folgende Screenshot zeigt die Ergebnisse basierend auf dem zweiten Kriterium:

   

   Für die dritte UPDATE Abfrage wird ein breiter Plan ausgewählt. Der Index ic3 (in Spalte c3) wird jedoch nicht im Plan angezeigt. Das Problem tritt auf, da das zweite Kriterium nicht erfüllt ist – Seitenindexgröße für Blattseiten im Vergleich zum maximalen Serverspeicher.

   Der Datentyp der Spalte c2c4 und c4 ist char(30), während der Datentyp der Spalte c3 istchar(20). Die Größe jeder Indexzeile ic3 ist kleiner als andere, sodass die Anzahl der Blattseiten kleiner als andere ist.

   Mithilfe der Dynamischen Verwaltungsfunktion (Dynamic Management Function, DMF) sys.dm_db_database_page_allocationskönnen Sie die Anzahl der Seiten für jeden Index berechnen. Für Indizes ic2ic4, und ic5, jeder Index hat 214 Seiten, und 209 davon sind Blattseiten (Ergebnisse können leicht variieren). Der von Blattseiten verbrauchte Speicher beträgt 209 x 8 = 1.672 KB. Daher beträgt das Verhältnis 1672/(1500 x 1024) = 0,00108854101, das größer als 1/1000 ist. ic3 Es gibt jedoch nur 161 Seiten; 159 davon sind Blattseiten. Das Verhältnis beträgt 159 x 8/(1500 x 1024) = 0,000828125, das kleiner als 1/1000 (0,001) ist.

   Wenn Sie mehr Zeilen einfügen oder den maximalen Serverspeicher reduzieren, um das Kriterium zu erfüllen, ändert sich der Plan. Um die Größe der Indexblattebene größer als 1/1000 zu machen, können Sie die maximale Serverspeichereinstellung etwas auf 1.200 verringern, indem Sie die folgenden Befehle ausführen:

   sp_configure 'show advanced options', 1;
   GO
   RECONFIGURE;
   GO
   sp_configure 'max server memory', 1200;
   GO
   RECONFIGURE
   GO
   UPDATE mytable1 SET c1=c1 WHERE c1=50001 OPTION(RECOMPILE) --251 rows are updated.
   

   In diesem Fall 159 x 8/(1200 x 1024) = 0,00103515625 > 1/1000. Nach dieser Änderung wird der ic3 Plan angezeigt.

   Weitere Informationen show advanced optionsfinden Sie unter Verwenden von Transact-SQL.

   Der folgende Screenshot zeigt, dass der breite Plan alle Indizes verwendet, wenn der Speicherschwellenwert erreicht ist:

   

Ist ein breiter Plan schneller als ein schmaler Plan?

Die Antwort lautet, dass sie davon abhängt, ob die Daten- und Indexseiten im Pufferpool zwischengespeichert werden oder nicht.

Daten werden im Pufferpool zwischengespeichert.

Wenn sich die Daten bereits im Pufferpool befinden, bietet die Abfrage mit dem breiten Plan nicht unbedingt zusätzliche Leistungsvorteile im Vergleich zu schmalen Plänen, da der breite Plan darauf ausgelegt ist, die E/A-Leistung zu verbessern (physische Lesevorgänge, keine logischen Lesevorgänge).

Führen Sie die folgenden Schritte in der folgenden Umgebung aus, um zu testen, ob ein breiter Plan schneller als ein schmaler Plan ist, wenn sich die Daten in einem Pufferpool befinden:

• SQL Server 2019 CU11

• Maximaler Serverspeicher: 30.000 MB

• Die Datengröße beträgt 64 MB, während die Indexgröße etwa 127 MB beträgt.

• Datenbankdateien befinden sich auf zwei verschiedenen physischen Datenträgern:

  • I:\sql19\dbWideplan.mdf
  • H:\sql19\dbWideplan.ldf

1. Erstellen Sie eine weitere Tabelle, mytable2indem Sie die folgenden Befehle ausführen:

   CREATE TABLE mytable2(C1 INT,C2 INT,C3 INT,C4 INT,C5 INT)
   GO
   CREATE CLUSTERED INDEX IC1 ON mytable2(C1)
   CREATE INDEX IC2 ON mytable2(C2)
   CREATE INDEX IC3 ON mytable2(C3)
   CREATE INDEX IC4 ON mytable2(C4)
   CREATE INDEX IC5 ON mytable2(C5)
   GO
   DECLARE @N INT=1
   WHILE @N<1000000
   BEGIN
     DECLARE @N1 INT=RAND()*4500
     DECLARE @N2 INT=RAND()*100000
     DECLARE @N3 INT=RAND()*100000
     DECLARE @N4 INT=RAND()*100000
     DECLARE @N5 INT=RAND()*100000
     INSERT mytable2 VALUES(@N1,@N2,@N3,@N4,@N5)
     SET @N+=1
   END
   GO
   UPDATE STATISTICS mytable2 WITH FULLSCAN
   

2. Führen Sie die folgenden beiden Abfragen aus, um die Abfragepläne zu vergleichen:

   update mytable2 set c1=c1 where c2<260 option(querytraceon 8790) --trace flag 8790 will force Wide plan
   update mytable2 set c1=c1 where c2<260 option(querytraceon 2338) --trace flag 2338 will force Narrow plan
   

   Weitere Informationen finden Sie unter Ablaufverfolgungskennzeichnung 8790 und Ablaufverfolgungskennzeichnung 2338.

   Die Abfrage mit dem breiten Plan dauert 0,136 Sekunden, während die Abfrage mit dem schmalen Plan nur 0,112 Sekunden dauert. Die beiden Daueren sind sehr nah, und die Aktualisierung pro Index (breiter Plan) ist weniger vorteilhaft, da die Daten bereits im Puffer vorhanden sind, bevor die UPDATE Anweisung ausgeführt wurde.

   Der folgende Screenshot zeigt breite und schmale Pläne, wenn Daten im Pufferpool zwischengespeichert werden:

   

Daten werden nicht im Pufferpool zwischengespeichert.

Führen Sie die folgenden Abfragen aus, um zu testen, ob ein breiter Plan schneller als ein schmaler Plan ist, wenn sich die Daten nicht im Pufferpool befinden:

Notiz

Wenn Sie den Test durchführen, stellen Sie sicher, dass Ihres die einzige Arbeitsauslastung in SQL Server ist, und die Datenträger sind sql Server zugeordnet.

CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
GO
WAITFOR DELAY '00:00:02' --wait for 1~2 seconds
UPDATE mytable2 SET c1=c1 WHERE c2 < 260 OPTION (QUERYTRACEON 8790) --force Wide plan
CHECKPOINT 
GO
DBCC DROPCLEANBUFFERS
GO 
WAITFOR DELAY '00:00:02' --wait for 1~2 SECONDS
UPDATE mytable2 SET c1=c1 WHERE c2 < 260 OPTION (QUERYTRACEON 2338) --force Narrow plan

Die Abfrage mit einem breiten Plan dauert 3,554 Sekunden, während die Abfrage mit einem schmalen Plan 6,701 Sekunden dauert. Die breite Planabfrage wird dieses Mal schneller ausgeführt.

Der folgende Screenshot zeigt den breiten Plan, wenn Daten nicht im Pufferpool zwischengespeichert werden:

Der folgende Screenshot zeigt den schmalen Plan, wenn Daten nicht im Pufferpool zwischengespeichert werden:

Ist eine umfassende Planabfrage immer schneller als ein schmaler Abfrageplan, wenn sich Daten nicht im Puffer befinden?

Die Antwort lautet "nicht immer". Führen Sie die folgenden Schritte aus, um zu testen, ob die breite Planabfrage immer schneller als der schmale Abfrageplan ist, wenn sich daten nicht im Puffer befinden:

1. Erstellen Sie eine weitere Tabelle, mytable2indem Sie die folgenden Befehle ausführen:

   SELECT c1,c1 AS c2,c1 AS C3,c1 AS c4,c1 AS C5 INTO mytable3 FROM mytable2
   GO
   CREATE CLUSTERED INDEX IC1 ON mytable3(C1)
   CREATE INDEX IC2 ON mytable3(C2)
   CREATE INDEX IC3 ON mytable3(C3)
   CREATE INDEX IC4 ON mytable3(C4)
   CREATE INDEX IC5 ON mytable3(C5)
   GO
   

   Dies mytable3 ist identisch mit mytable2, mit Ausnahme der Daten. mytable3 hat alle fünf Spalten mit demselben Wert, wodurch die Reihenfolge der nicht gruppierten Indizes der Reihenfolge des gruppierten Indexes entspricht. Durch diese Sortierung der Daten wird der Vorteil des breiten Plans minimiert.

2. Führen Sie die folgenden Befehle aus, um die Abfragepläne zu vergleichen:

   CHECKPOINT 
   GO
   DBCC DROPCLEANBUFFERS
   go
   UPDATE mytable3 SET c1=c1 WHERE c2<12 OPTION(QUERYTRACEON 8790) --tf 8790 will force Wide plan
   
   CHECKPOINT 
   GO
   DBCC DROPCLEANBUFFERS
   GO
   UPDATE mytable3 SET c1=c1 WHERE c2<12 OPTION(QUERYTRACEON 2338) --tf 2338 will force Narrow plan
   

   Die Dauer beider Abfragen wird erheblich reduziert! Der breite Plan dauert 0,304 Sekunden, was etwas langsamer ist als der schmale Plan dieses Mal.

   Der folgende Screenshot zeigt den Vergleich der Leistung, wenn breite und schmale Verwendet werden:

   

Szenarien, in denen die breiten Pläne angewendet werden

Hier sind die anderen Szenarien, in denen auch breite Pläne angewendet werden:

Die Gruppierte Indexspalte weist einen eindeutigen oder primärschlüssel auf, und mehrere Zeilen werden aktualisiert.

Hier ist ein Beispiel zum Reproduzieren des Szenarios:

CREATE TABLE mytable4(c1 INT primary key,c2 INT,c3 INT,c4 INT)
GO
CREATE INDEX ic2 ON mytable4(c2)
CREATE INDEX ic3 ON mytable4(c3)
CREATE INDEX ic4 ON mytable4(c4)
GO
INSERT mytable4 VALUES(0,0,0,0)
INSERT mytable4 VALUES(1,1,1,1)

Der folgende Screenshot zeigt, dass der breite Plan verwendet wird, wenn der Clusterindex über einen eindeutigen Schlüssel verfügt:

Weitere Details erhalten Sie unter "Verwalten eindeutiger Indizes".

Clusterindexspalte wird im Partitionsschema angegeben

Hier ist ein Beispiel zum Reproduzieren des Szenarios:

CREATE TABLE mytable5(c1 INT,c2 INT,c3 INT,c4 INT)
GO
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name='PS1')
    DROP PARTITION SCHEME PS1
GO
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name='PF1')
    DROP PARTITION FUNCTION PF1
GO
CREATE PARTITION FUNCTION PF1(INT) AS 
  RANGE right FOR VALUES 
  (2000)   
GO
CREATE PARTITION SCHEME PS1 AS 
  PARTITION PF1 all TO 
  ([PRIMARY]) 
GO 
CREATE CLUSTERED INDEX c1 ON mytable5(c1) ON PS1(c1)
CREATE INDEX c2 ON mytable5(c2)
CREATE INDEX c3 ON mytable5(c3)
CREATE INDEX c4 ON mytable5(c4)
GO
UPDATE mytable5 SET c1=c1 WHERE c1=1 

Der folgende Screenshot zeigt, dass der breite Plan verwendet wird, wenn im Partitionsschema eine gruppierte Spalte vorhanden ist:

Gruppierte Indexspalte ist nicht Teil des Partitionsschemas, und die Partitionsschemaspalte wird aktualisiert.

Hier ist ein Beispiel zum Reproduzieren des Szenarios:

CREATE TABLE mytable6(c1 INT,c2 INT,c3 INT,c4 INT)
GO
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name='PS2')
    DROP PARTITION SCHEME PS2
GO
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name='PF2')
    DROP PARTITION FUNCTION PF2
GO
CREATE PARTITION FUNCTION PF2(int) AS 
  RANGE right FOR VALUES 
  (2000)   
GO
CREATE PARTITION SCHEME PS2 AS 
  PARTITION PF2 all TO 
  ([PRIMARY]) 
GO 
CREATE CLUSTERED INDEX c1 ON mytable6(c1) ON PS2(c2) --on c2 column
CREATE INDEX c3 ON mytable6(c3)
CREATE INDEX c4 ON mytable6(c4)

Der folgende Screenshot zeigt, dass der breite Plan verwendet wird, wenn die Partitionsschemaspalte aktualisiert wird:

Zusammenfassung

• SQL Server wählt ein breites Planupdate aus, wenn die folgenden Kriterien gleichzeitig erfüllt sind:

  • Die betroffene Anzahl von Zeilen ist größer als 250.
  • Der Speicher des Blattindexes ist mindestens 1/1000 der Einstellung für den maximalen Serverspeicher.

• Breite Pläne steigern die Leistung auf Kosten des Verbrauchs von zusätzlichem Arbeitsspeicher.

• Wenn der erwartete Abfrageplan nicht verwendet wird, kann er auf veraltete Statistiken (keine berichtige Datengröße), maximale Serverspeichereinstellung oder andere nicht zusammenhängende Probleme wie Parametersensitive Pläne zurückzuführen sein.

• Die Dauer von UPDATE Anweisungen, die einen breiten Plan verwenden, hängt von mehreren Faktoren ab, und in einigen Fällen kann es länger dauern als schmale Pläne.

• Die Ablaufverfolgungskennzeichnung 8790 erzwingt einen breiten Plan. Spurkennzeichnung 2338 erzwingt einen schmalen Plan.