Rozwiązywanie problemów z wydajnością aktualizacji za pomocą wąskich i szerokich planów w programie SQL Server

Dotyczy: SQL Server

Instrukcja może być szybsza UPDATE w niektórych przypadkach i wolniejsza w innych. Istnieje wiele czynników, które mogą prowadzić do takiej wariancji, w tym liczby wierszy zaktualizowanych i użycia zasobów w systemie (blokowanie, procesor, pamięć lub operacje we/wy). W tym artykule omówiono jedną konkretną przyczynę wariancji: wybór planu zapytania dokonanego przez program SQL Server.

Co to są wąskie i szerokie plany?

Po wykonaniu UPDATE instrukcji względem kolumny indeksu klastrowanego program SQL Server aktualizuje nie tylko indeks klastrowany, ale także wszystkie indeksy nieklasterowane, ponieważ indeksy niekla klastrowane zawierają klucz indeksu klastra.

Program SQL Server ma dwie opcje aktualizacji:

• Wąski plan: czy aktualizacja indeksu niekla klastrowanego wraz z aktualizacją klucza indeksu klastrowanego. To proste podejście jest łatwe do zrozumienia; zaktualizuj indeks klastrowany, a następnie zaktualizuj wszystkie indeksy nieklasterowane w tym samym czasie. Program SQL Server zaktualizuje jeden wiersz i przejdzie do następnego do momentu ukończenia wszystkich. Takie podejście jest nazywane zawężną aktualizacją planu lub aktualizacją dla poszczególnych wierszy. Jednak ta operacja jest stosunkowo kosztowna, ponieważ kolejność nieklasowanych danych indeksu, które zostaną zaktualizowane, może nie być w kolejności danych indeksu klastrowanego. Jeśli wiele stron indeksu jest zaangażowanych w aktualizację, gdy dane znajdują się na dysku, może wystąpić duża liczba losowych żądań we/wy.

• Szeroki plan: Aby zoptymalizować wydajność i zmniejszyć losowe operacje we/wy, program SQL Server może wybrać szeroki plan. Nie wykonuje aktualizacji indeksów nieklasterowanych wraz z aktualizacją indeksu klastrowanego razem. Zamiast tego najpierw sortuje wszystkie dane indeksów nieklasterowanych w pamięci, a następnie aktualizuje wszystkie indeksy w tej kolejności. Takie podejście jest nazywane szerokim planem (nazywanym również aktualizacją poszczególnych indeksów).

Oto zrzut ekranu przedstawiający wąskie i szerokie plany:

Kiedy program SQL Server wybierze szeroki plan?

Aby program SQL Server wybrał szeroki plan, należy spełnić dwa kryteria:

• Liczba wierszy, których dotyczy ten wpływ, jest większa niż 250.
• Rozmiar liścia indeksów nieklasterowanych (liczba stron indeksu * 8 KB) wynosi co najmniej 1/1000 maksymalnego ustawienia pamięci serwera.

Jak działają wąskie i szerokie plany?

Aby zrozumieć, jak działają wąskie i szerokie plany, wykonaj następujące kroki w następującym środowisku:

• SQL Server 2019 CU11
• Maksymalna pamięć serwera = 1500 MB

1. Uruchom następujący skrypt, aby utworzyć tabelę mytable1 zawierającą 41 501 wierszy, jeden indeks klastrowany w kolumnie c1i pięć indeksów nieklasterowanych odpowiednio w pozostałych kolumnach.

   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. Uruchom następujące trzy instrukcje języka T-SQL UPDATE i porównaj plany zapytań:

   • UPDATE mytable1 SET c1=c1 WHERE c1=1 OPTION(RECOMPILE) - jeden wiersz jest aktualizowany
   • UPDATE mytable1 SET c1=c1 WHERE c1=50000 OPTION(RECOMPILE) - Zaktualizowano 250 wierszy.
   • UPDATE mytable1 SET c1=c1 WHERE c1=50001 OPTION(RECOMPILE) - Zaktualizowano 251 wierszy.

3. Sprawdź wyniki na podstawie pierwszego kryterium (próg liczby wierszy, których dotyczy problem, wynosi 250).

   Poniższy zrzut ekranu przedstawia wyniki na podstawie pierwszego kryterium:

   

   Zgodnie z oczekiwaniami optymalizator zapytań wybiera wąski plan dla dwóch pierwszych zapytań, ponieważ liczba wierszy, których dotyczy ten wpływ, jest mniejsza niż 250. Dla trzeciego zapytania jest używany szeroki plan, ponieważ liczba wierszy, których dotyczy ten wpływ, wynosi 251, co jest większe niż 250.

4. Sprawdź wyniki na podstawie drugiego kryterium (pamięć rozmiaru indeksu liścia wynosi co najmniej 1/1000 maksymalnego ustawienia pamięci serwera).

   Poniższy zrzut ekranu przedstawia wyniki na podstawie drugiego kryterium:

   

   Dla trzeciego UPDATE zapytania wybrano szeroki plan. Jednak indeks ic3 (w kolumnie c3) nie jest widoczny w planie. Problem występuje, ponieważ drugie kryterium nie jest spełnione — rozmiar indeksu stron liścia w porównaniu z ustawieniem maksymalnej pamięci serwera.

   Typ danych kolumny c2i c4 c4 to char(30), a typ danych kolumny c3 to char(20). Rozmiar każdego wiersza indeksu ic3 jest mniejszy niż inne, więc liczba stron liścia jest mniejsza niż inne.

   Za pomocą funkcji dynamicznego zarządzania (DMF) sys.dm_db_database_page_allocationsmożna obliczyć liczbę stron dla każdego indeksu. W przypadku indeksów ic2, ic4i ic5każdy indeks ma 214 stron, a 209 z nich to strony liści (wyniki mogą się nieznacznie różnić). Pamięć zużywana przez strony liścia to 209 x 8 = 1672 KB. W związku z tym stosunek wynosi 1672/(1500 x 1024) = 0,00108854101, co jest większe niż 1/1000. Jednak tylko 161 stron; ic3 159 z nich to strony liścia. Współczynnik wynosi 159 x 8/(1500 x 1024) = 0,000828125, czyli mniej niż 1/1000 (0,001).

   Jeśli wstawisz więcej wierszy lub zmniejszysz maksymalną pamięć serwera, aby spełnić kryterium, plan zmieni się. Aby rozmiar indeksu na poziomie liścia był większy niż 1/1000, można obniżyć ustawienie maksymalnej pamięci serwera na 1200, uruchamiając następujące polecenia:

   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.
   

   W tym przypadku 159 x 8/(1200 x 1024) = 0,00103515625 > 1/1000. Po tej zmianie element ic3 pojawi się w planie.

   Aby uzyskać więcej informacji na temat show advanced optionsprogramu , zobacz Use Transact-SQL (Używanie języka Transact-SQL).

   Poniższy zrzut ekranu przedstawia, że plan szeroki używa wszystkich indeksów po osiągnięciu progu pamięci:

   

Czy szeroki plan jest szybszy niż wąski plan?

Odpowiedź polega na tym, czy dane i strony indeksu są buforowane w puli, czy nie.

Dane są buforowane w puli

Jeśli dane znajdują się już w puli, zapytanie z szerokim planem niekoniecznie oferuje dodatkowe korzyści z wydajności w porównaniu z wąskimi planami, ponieważ szeroki plan został zaprojektowany w celu zwiększenia wydajności operacji we/wy (odczyty fizyczne, a nie odczyty logiczne).

Aby sprawdzić, czy szeroki plan jest szybszy niż wąski plan, gdy dane są w puli, wykonaj następujące kroki w następującym środowisku:

• SQL Server 2019 CU11

• Maksymalna pamięć serwera: 30 000 MB

• Rozmiar danych wynosi 64 MB, a rozmiar indeksu wynosi około 127 MB.

• Pliki bazy danych znajdują się na dwóch różnych dyskach fizycznych:

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

1. Utwórz kolejną tabelę , mytable2uruchamiając następujące polecenia:

   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. Wykonaj następujące dwa zapytania, aby porównać plany zapytań:

   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
   

   Aby uzyskać więcej informacji, zobacz flaga śledzenia 8790 i flaga śledzenia 2338.

   Zapytanie z szerokim planem trwa 0,136 sekund, a zapytanie z wąskim planem trwa tylko 0,112 sekund. Dwa czasy trwania są bardzo bliskie, a aktualizacja poszczególnych indeksów (plan szeroki) jest mniej korzystna, ponieważ dane znajdują się już w buforze przed wykonaniem UPDATE instrukcji.

   Poniższy zrzut ekranu przedstawia szerokie i wąskie plany, gdy dane są buforowane w puli:

   

Dane nie są buforowane w puli

Aby sprawdzić, czy szeroki plan jest szybszy niż wąski plan, gdy dane nie są w puli, uruchom następujące zapytania:

Uwaga 16.

Podczas testowania upewnij się, że twoje jest jedynym obciążeniem w programie SQL Server, a dyski są dedykowane dla programu SQL Server.

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

Zapytanie z szerokim planem trwa 3,554 sekundy, a zapytanie z wąskim planem trwa 6,701 sekund. Tym razem szerokie zapytanie planu jest uruchamiane szybciej.

Poniższy zrzut ekranu przedstawia szeroki plan, gdy dane nie są buforowane w puli:

Poniższy zrzut ekranu przedstawia wąski plan, gdy dane nie są buforowane w puli:

Czy szerokie zapytanie planu jest zawsze szybsze niż wąski plan zapytania, gdy dane nie są w buforze?

Odpowiedź brzmi "nie zawsze". Aby sprawdzić, czy zapytanie o szeroki plan jest zawsze szybsze niż wąski plan zapytania, gdy dane nie są w buforze, wykonaj następujące kroki:

1. Utwórz kolejną tabelę , mytable2uruchamiając następujące polecenia:

   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
   

   Parametr mytable3 jest taki sam jak mytable2, z wyjątkiem danych. mytable3 zawiera wszystkie pięć kolumn z tą samą wartością, co sprawia, że kolejność indeksów nieklasowanych jest kolejność indeksu klastrowanego. To sortowanie danych zminimalizuje zaletę szerokiego planu.

2. Wykonaj następujące polecenia, aby porównać plany zapytań:

   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
   

   Czas trwania obu zapytań jest znacznie krótszy! Plan szeroki zajmuje 0,304 sekundy, co jest nieco wolniejsze niż wąski plan tym razem.

   Poniższy zrzut ekranu przedstawia porównanie wydajności, gdy są używane szerokie i wąskie:

   

Scenariusze, w których stosowane są szerokie plany

Poniżej przedstawiono inne scenariusze, w których stosowane są również szerokie plany:

Kolumna indeksu klastrowanego ma unikatowy lub podstawowy klucz, a wiele wierszy jest aktualizowanych

Oto przykład odtworzenia scenariusza:

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)

Poniższy zrzut ekranu pokazuje, że szeroki plan jest używany, gdy indeks klastra ma unikatowy klucz:

Aby uzyskać więcej informacji, zobacz Obsługa unikatowych indeksów.

Kolumna indeksu klastra jest określona w schemacie partycji

Oto przykład odtworzenia scenariusza:

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 

Poniższy zrzut ekranu pokazuje, że szeroki plan jest używany, gdy w schemacie partycji znajduje się kolumna klastrowana:

Kolumna indeksu klastrowanego nie jest częścią schematu partycji, a kolumna schematu partycji jest aktualizowana

Oto przykład odtworzenia scenariusza:

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)

Poniższy zrzut ekranu pokazuje, że szeroki plan jest używany podczas aktualizowania kolumny schematu partycji:

Podsumowanie

• Program SQL Server wybiera szeroką aktualizację planu po spełnieniu następujących kryteriów w tym samym czasie:

  • Liczba wierszy, których dotyczy wpływ, jest większa niż 250.
  • Pamięć indeksu liścia wynosi co najmniej 1/1000 maksymalnego ustawienia pamięci serwera.

• Szerokie plany zwiększają wydajność kosztem zużywania dodatkowej pamięci.

• Jeśli oczekiwany plan zapytania nie jest używany, może to być spowodowane nieaktualnymi statystykami (nie raportowaniem poprawnego rozmiaru danych), ustawieniem maksymalnej pamięci serwera lub innymi niepowiązanymi problemami, takimi jak plany wrażliwe na parametry.

• Czas trwania instrukcji korzystających z szerokiego UPDATE planu zależy od kilku czynników, a w niektórych przypadkach może to potrwać dłużej niż wąskie plany.

• Flaga śledzenia 8790 wymusi szeroki plan; flaga śledzenia 2338 wymusi wąski plan.