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

Gilt für: SQL Server

Eine UPDATE Anweisung kann in einigen Fällen schneller und in anderen langsamer 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 durch SQL Server.

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 hat zwei Optionen, um das Update durchzuführen:

• 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 Per-Row Update 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-Vorgänge zu reduzieren, können SQL Server einen breiten Plan auswählen. Die nicht gruppierten Indizes werden nicht zusammen mit dem Clusterindexupdate aktualisiert. Stattdessen werden zunächst alle nicht gruppierten Indexdaten im Arbeitsspeicher sortiert und dann alle Indizes in dieser Reihenfolge aktualisiert. Dieser Ansatz wird als breit angelegter Plan (auch als Per-Index Update bezeichnet) bezeichnet.

Hier ist ein Screenshot der schmalen und breiten Pläne:

Wann wählt SQL Server einen breiten Plan?

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

• 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 maximalen Serverarbeitsspeicher.

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
• Maximaler Serverarbeitsspeicher = 1.500 MB

1. Führen Sie das folgende Skript aus, um eine Tabelle mytable1 mit 41.501 Zeilen, einem gruppierten Index für die Spalte c1bzw. 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. Für die dritte Abfrage wird ein breit angelegter Plan verwendet, da die anzahl der betroffenen Zeilen 251 beträgt und größer als 250 ist.

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

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

   

   Für die dritte UPDATE Abfrage wird ein breit angelegter Plan ausgewählt. Der Index ic3 (in der Spalte c3) wird jedoch nicht im Plan angezeigt. Das Problem tritt auf, weil das zweite Kriterium nicht erfüllt ist: Blattseitenindexgröße im Vergleich zur Einstellung max. Serverarbeitsspeicher.

   Der Datentyp der Spalte , und ist , während der Datentyp der Spalte c3 istchar(20).char(30)c4c4c2 Die Größe der einzelnen Indexzeilen ic3 ist kleiner als bei anderen, sodass die Anzahl der Blattseiten kleiner ist als bei anderen.

   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. Bei den Indizes ic2, ic4und ic5hat jeder Index 214 Seiten, und 209 davon sind Blattseiten (Die Ergebnisse können leicht variieren). Der von Blattseiten belegte Arbeitsspeicher beträgt 209 x 8 = 1.672 KB. Daher ist das Verhältnis 1672/(1500 x 1024) = 0,00108854101, was größer als 1/1000 ist. Die ic3 hat jedoch nur 161 Seiten; 159 davon sind Blattseiten. Das Verhältnis ist 159 x 8/(1500 x 1024) = 0,000828125, was kleiner als 1/1000 (0,001) ist.

   Wenn Sie weitere Zeilen einfügen oder den maximalen Serverarbeitsspeicher verringern, um das Kriterium zu erfüllen, ändert sich der Plan. Um die Größe des Indexblatts auf Blattebene größer als 1/1000 zu machen, können Sie die Einstellung für den maximalen Serverarbeitsspeicher 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 im ic3 Plan angezeigt.

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

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

   

Ist ein breiter Plan schneller als ein schmaler Plan?

Die Antwort ist, dass es 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 Serverarbeitsspeicher: 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, mytable2, indem 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 Ablaufverfolgungsflag 8790 und Ablaufverfolgungsflag 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 nahe, und das Per-Index Update (breiter Plan) ist weniger vorteilhaft, da sich die Daten bereits im Puffer befinden, 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:

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 Abfrage für den breit angelegten Plan 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 Abfrage mit einem breiten Plan immer schneller als ein schmaler Abfrageplan, wenn sich keine Daten im Puffer befinden?

Die Antwort lautet "nicht immer". Führen Sie die folgenden Schritte aus, um zu testen, ob die Abfrage des breiten Plans immer schneller als der schmale Abfrageplan ist, wenn sich keine Daten im Puffer befinden:

1. Erstellen Sie eine weitere Tabelle, mytable2, indem 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
   

   ist mytable3 identisch mit mytable2, mit Ausnahme der Daten. mytable3 weist alle fünf Spalten mit demselben Wert auf, wodurch die Reihenfolge der nicht gruppierten Indizes der Reihenfolge des gruppierten Indexes folgt. Diese Sortierung der Daten minimiert den Vorteil des breiten Plans.

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 diesmal etwas langsamer als der schmale Plan ist.

   Der folgende Screenshot zeigt den Vergleich der Leistung, wenn breit und schmal verwendet werden:

   

Szenarien, in denen die breiten Pläne angewendet werden

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

Die gruppierte Indexspalte verfügt über einen eindeutigen oder primären Schlüssel, und mehrere Zeilen werden aktualisiert.

Im Folgenden finden Sie ein Beispiel, um das Szenario zu reproduzieren:

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 Informationen finden Sie unter Verwalten eindeutiger Indizes.

Die Clusterindexspalte wird im Partitionsschema angegeben.

Im Folgenden finden Sie ein Beispiel, um das Szenario zu reproduzieren:

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 eine gruppierte Spalte im Partitionsschema vorhanden ist:

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

Im Folgenden finden Sie ein Beispiel, um das Szenario zu reproduzieren:

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 eine breite Planaktualisierung aus, wenn die folgenden Kriterien gleichzeitig erfüllt sind:

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

• Breite Pläne steigern die Leistung auf Kosten der Nutzung von zusätzlichem Arbeitsspeicher.

• Wenn der erwartete Abfrageplan nicht verwendet wird, kann dies auf veraltete Statistiken (ohne Angabe der richtigen Datengröße), die Einstellung für den maximalen Serverarbeitsspeicher oder andere nicht zusammenhängende Probleme wie parameterabhängige 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 sie länger dauern als schmale Pläne.

• Die Spurflag 8790 wird einen breiten Plan erzwingen; Ablaufverfolgungsflag 2338 erzwingt einen schmalen Plan.