Felsöka problem med UPPDATERINGsprestanda med smala och breda planer i SQL Server

Gäller för: SQL Server

En UPDATE instruktion kan vara snabbare i vissa fall och långsammare i andra. Det finns många faktorer som kan leda till en sådan varians, inklusive antalet rader som uppdateras och resursanvändningen i systemet (blockering, CPU, minne eller I/O). Den här artikeln beskriver en specifik orsak till variansen: valet av frågeplan som görs av SQL Server.

Vad är smala och breda planer?

När du kör en UPDATE instruktion mot en klustrad indexkolumn uppdaterar SQL Server inte bara det klustrade indexet utan även alla icke-klustrade index eftersom de icke-klustrade indexen innehåller klusterindexnyckeln.

SQL Server har två alternativ för att göra uppdateringen:

• Smal plan: Uppdatera det icke-klustrade indexet tillsammans med den klustrade indexnyckeluppdateringen. Den här enkla metoden är lätt att förstå. uppdatera det klustrade indexet och uppdatera sedan alla icke-klustrade index samtidigt. SQL Server uppdaterar en rad och går vidare till nästa tills alla har slutförts. Den här metoden kallas för en smal planuppdatering eller en uppdatering per rad. Den här åtgärden är dock relativt dyr eftersom ordningen på icke-klustrade indexdata som uppdateras kanske inte är i ordning för klustrade indexdata. Om många indexsidor ingår i uppdateringen kan ett stort antal slumpmässiga I/O-begäranden inträffa när data finns på disk.

• Bred plan: För att optimera prestanda och minska slumpmässig I/O kan SQL Server välja en bred plan. Det uppdaterar inte de icke-klustrade indexen tillsammans med den klustrade indexuppdateringen. I stället sorterar den alla icke-klustrade indexdata i minnet först och uppdaterar sedan alla index i den ordningen. Den här metoden kallas för en bred plan (kallas även för en uppdatering per index).

Här är en skärmbild av smala och breda planer:

När väljer SQL Server en bred plan?

Två villkor måste uppfyllas för att SQL Server ska kunna välja en bred plan:

• Antalet rader som påverkas är större än 250.
• Storleken på lövnivån för de icke-klustrade indexen (antal indexsidor * 8 KB) är minst 1/1 000 av den maximala inställningen för serverminne.

Hur fungerar smala och breda planer?

Följ dessa steg i följande miljö för att förstå hur smala och breda planer fungerar:

• SQL Server 2019 CU11
• Maximalt serverminne = 1 500 MB

1. Kör följande skript för att skapa en tabell mytable1 med 41 501 rader, ett grupperat index i kolumnen c1och fem icke-klustrade index i resten av kolumnerna.

   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. Kör följande tre T-SQL-uttryck UPDATE och jämför frågeplanerna:

   • UPDATE mytable1 SET c1=c1 WHERE c1=1 OPTION(RECOMPILE) – en rad uppdateras
   • UPDATE mytable1 SET c1=c1 WHERE c1=50000 OPTION(RECOMPILE) - 250 rader uppdateras.
   • UPDATE mytable1 SET c1=c1 WHERE c1=50001 OPTION(RECOMPILE) - 251 rader uppdateras.

3. Granska resultaten baserat på det första kriteriet (tröskelvärdet för det berörda antalet rader är 250).

   Följande skärmbild visar resultatet baserat på det första kriteriet:

   

   Som förväntat väljer frågeoptimeraren en smal plan för de två första frågorna eftersom antalet påverkade rader är mindre än 250. En bred plan används för den tredje frågan eftersom det påverkade radantalet är 251, vilket är större än 250.

4. Granska resultaten baserat på det andra kriteriet (minnet för lövindexstorleken är minst 1/1 000 av den maximala serverminnesinställningen).

   Följande skärmbild visar resultatet baserat på det andra kriteriet:

   

   En bred plan har valts för den tredje UPDATE frågan. Men indexet ic3 (i kolumnen c3) visas inte i planen. Problemet beror på att det andra kriteriet inte uppfylls – indexstorlek för lövsidor i jämförelse med inställningen för maximalt serverminne.

   Datatypen för kolumnen , och är , medan datatypen för kolumnen c3 är char(20).char(30)c4 c4 c2 Storleken på varje indexrad ic3 är mindre än andra, så antalet lövsidor är mindre än andra.

   Med hjälp av funktionen för dynamisk hantering (DMF) sys.dm_db_database_page_allocationskan du beräkna antalet sidor för varje index. För index ic2, ic4och ic5, har varje index 214 sidor och 209 av dem är lövsidor (resultaten kan variera något). Minnet som förbrukas av lövsidor är 209 x 8 = 1 672 KB. Därför är förhållandet 1672/(1500 x 1024) = 0,00108854101, vilket är större än 1/1000. Men de ic3 enda har 161 sidor; 159 av dem är lövsidor. Förhållandet är 159 x 8/(1500 x 1024) = 0,000828125, vilket är mindre än 1/1000 (0,001).

   Om du infogar fler rader eller minskar det maximala serverminnet för att uppfylla kriteriet ändras planen. Om du vill göra indexets storlek på lövnivå större än 1/1000 kan du sänka inställningen för maximalt serverminne till 1 200 genom att köra följande kommandon:

   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.
   

   I det här fallet 159 x 8/(1200 x 1024) = 0,00103515625 > 1/1000. Efter den här ändringen visas i ic3 planen.

   Mer information om show advanced optionsfinns i Använda Transact-SQL.

   Följande skärmbild visar att den breda planen använder alla index när minneströskeln nås:

   

Är en bred plan snabbare än en smal plan?

Svaret är att det beror på om data- och indexsidorna cachelagras i buffertpoolen eller inte.

Data cachelagras i buffertpoolen

Om data redan finns i buffertpoolen erbjuder frågan med den breda planen inte nödvändigtvis extra prestandafördelar jämfört med smala planer eftersom den breda planen är utformad för att förbättra I/O-prestanda (fysiska läsningar, inte logiska läsningar).

Om du vill testa om en bred plan är snabbare än en smal plan när data finns i en buffertpool följer du dessa steg i följande miljö:

• SQL Server 2019 CU11

• Maximalt serverminne: 30 000 MB

• Datastorleken är 64 MB, medan indexstorleken är cirka 127 MB.

• Databasfiler finns på två olika fysiska diskar:

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

1. Skapa en annan tabell, mytable2, genom att köra följande kommandon:

   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. Kör följande två frågor för att jämföra frågeplanerna:

   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
   

   Mer information finns i spårningsflagga 8790 och spårningsflagga 2338.

   Frågan med den breda planen tar 0,136 sekunder, medan frågan med den smala planen bara tar 0,112 sekunder. De två varaktigheterna är mycket nära och uppdateringen per index (bred plan) är mindre fördelaktig eftersom data redan finns i bufferten innan -instruktionen UPDATE kördes.

   Följande skärmbild visar breda och smala planer när data cachelagras i buffertpoolen:

   

Data cachelagras inte i buffertpoolen

Kör följande frågor för att testa om en bred plan är snabbare än en smal plan när data inte finns i buffertpoolen:

Kommentar

När du gör testet kontrollerar du att din är den enda arbetsbelastningen i SQL Server och att diskarna är dedikerade till 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

Frågan med en bred plan tar 3,554 sekunder, medan frågan med en smal plan tar 6,701 sekunder. Den breda planfrågan körs snabbare den här gången.

Följande skärmbild visar den breda planen när data inte cachelagras i buffertpoolen:

Följande skärmbild visar den smala planen när data inte cachelagras i buffertpoolen:

Är en bred planfråga alltid snabbare än en smal frågeplan när data inte finns i bufferten?

Svaret är "inte alltid". Följ dessa steg för att testa om den breda planfrågan alltid är snabbare än den smala frågeplanen när data inte finns i bufferten:

1. Skapa en annan tabell, mytable2, genom att köra följande kommandon:

   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
   

   mytable3 är samma som mytable2, förutom för data. mytable3 har alla fem kolumner med samma värde, vilket gör att ordningen på icke-grupperade index följer ordningen på det klustrade indexet. Den här sortering av data minimerar fördelen med den breda planen.

2. Kör följande kommandon för att jämföra frågeplaner:

   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
   

   Varaktigheten för båda frågorna minskar avsevärt! Den breda planen tar 0,304 sekunder, vilket är lite långsammare än den smala planen den här gången.

   Följande skärmbild visar jämförelsen av prestanda när breda och smala används:

   

Scenarier där de breda planerna tillämpas

Här är de andra scenarierna där breda planer också tillämpas:

Den klustrade indexkolumnen har en unik eller primärnyckel och flera rader uppdateras

Här är ett exempel för att återskapa scenariot:

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)

Följande skärmbild visar att den breda planen används när klusterindexet har en unik nyckel:

Mer information finns i Underhålla unika index.

Klusterindexkolumnen anges i partitionsschemat

Här är ett exempel för att återskapa scenariot:

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 

Följande skärmbild visar att den breda planen används när det finns en klustrad kolumn i partitionsschemat:

Klustrad indexkolumn är inte en del av partitionsschemat och partitionsschemakolumnen uppdateras

Här är ett exempel för att återskapa scenariot:

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)

Följande skärmbild visar att den breda planen används när kolumnen partitionsschema uppdateras:

Slutsats

• SQL Server väljer en bred planuppdatering när följande villkor uppfylls samtidigt:

  • Det påverkade antalet rader är större än 250.
  • Minnet för lövindexet är minst 1/1 000 av den maximala serverminnesinställningen.

• Breda planer ökar prestandan på bekostnad av att förbruka extra minne.

• Om den förväntade frågeplanen inte används kan det bero på inaktuell statistik (inte rapporterar rätt datastorlek), maximal serverminnesinställning eller andra orelaterade problem som parameterkänsliga planer.

• Varaktigheten för UPDATE instruktioner som använder en bred plan beror på flera faktorer, och i vissa fall kan det ta längre tid än smala planer.

• Spårningsflagga 8790 tvingar fram en bred plan. Spårningsflagga 2338 tvingar fram en smal plan.