Prestatieproblemen met UPDATE oplossen met smalle en brede plannen in SQL Server

Van toepassing op: SQL Server

Een UPDATE instructie kan in sommige gevallen sneller en langzamer zijn in andere gevallen. Er zijn veel factoren die kunnen leiden tot een dergelijke variantie, waaronder het aantal bijgewerkte rijen en het resourcegebruik op het systeem (blokkeren, CPU, geheugen of I/O). In dit artikel wordt één specifieke reden behandeld voor de variantie: de keuze van het queryplan dat door SQL Server is gemaakt.

Wat zijn smalle en brede plannen?

Wanneer u een UPDATE instructie uitvoert voor een geclusterde indexkolom, werkt SQL Server niet alleen de geclusterde index zelf bij, maar ook alle niet-geclusterde indexen omdat de niet-geclusterde indexen de clusterindexsleutel bevatten.

SQL Server heeft twee opties om de update uit te voeren:

• Smal plan: Doe de niet-geclusterde indexupdate samen met de update van de geclusterde indexsleutel. Deze eenvoudige benadering is eenvoudig te begrijpen; werk de geclusterde index bij en werk vervolgens alle niet-geclusterde indexen tegelijkertijd bij. SQL Server werkt één rij bij en gaat naar de volgende totdat alles is voltooid. Deze methode wordt een smalle planupdate of een update per rij genoemd. Deze bewerking is echter relatief duur omdat de volgorde van niet-geclusterde indexgegevens die worden bijgewerkt mogelijk niet in de volgorde van geclusterde indexgegevens staan. Als er veel indexpagina's betrokken zijn bij de update, kan er een groot aantal willekeurige I/O-aanvragen optreden wanneer de gegevens op schijf staan.

• Breed plan: Om de prestaties te optimaliseren en willekeurige I/O te verminderen, kan SQL Server een breed plan kiezen. De niet-geclusterde indexen worden niet samen met de geclusterde index bijgewerkt. In plaats daarvan worden eerst alle niet-geclusterde indexgegevens in het geheugen gesorteerd en worden vervolgens alle indexen in die volgorde bijgewerkt. Deze benadering wordt een breed plan genoemd (ook wel een update per index genoemd).

Hier volgt een schermopname van smalle en brede plannen:

Wanneer kiest SQL Server een breed plan?

Aan twee criteria moet worden voldaan voor SQL Server om een breed plan te kiezen:

• Het aantal betrokken rijen is groter dan 250.
• De grootte van het bladniveau van de niet-geclusterde indexen (aantal indexpagina's * 8 kB) is ten minste 1/1000 van de maximale servergeheugeninstelling.

Hoe werken smalle en brede plannen?

Volg deze stappen in de volgende omgeving om te begrijpen hoe smalle en brede plannen werken:

• SQL Server 2019 CU11
• Maximaal servergeheugen = 1500 MB

1. Voer het volgende script uit om een tabel mytable1 te maken met respectievelijk 41.501 rijen, één geclusterde index op kolom c1en vijf niet-geclusterde indexen voor de rest van de kolommen.

   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. Voer de volgende drie T-SQL-instructies UPDATE uit en vergelijk de queryplannen:

   • UPDATE mytable1 SET c1=c1 WHERE c1=1 OPTION(RECOMPILE) - één rij wordt bijgewerkt
   • UPDATE mytable1 SET c1=c1 WHERE c1=50000 OPTION(RECOMPILE) - 250 rijen worden bijgewerkt.
   • UPDATE mytable1 SET c1=c1 WHERE c1=50001 OPTION(RECOMPILE) - 251 rijen worden bijgewerkt.

3. Bekijk de resultaten op basis van het eerste criterium (de drempelwaarde van het betrokken aantal rijen is 250).

   In de volgende schermopname ziet u de resultaten op basis van het eerste criterium:

   

   Zoals verwacht kiest de optimalisatiefunctie voor query's een smal plan voor de eerste twee query's, omdat het aantal betrokken rijen kleiner is dan 250. Voor de derde query wordt een breed plan gebruikt omdat het aantal betrokken rijen 251 is, wat groter is dan 250.

4. Bekijk de resultaten op basis van het tweede criterium (het geheugen van de leaf-indexgrootte is ten minste 1/1000 van de maximale servergeheugeninstelling).

   In de volgende schermopname ziet u de resultaten op basis van het tweede criterium:

   

   Er wordt een breed plan geselecteerd voor de derde UPDATE query. Maar de index ic3 (op kolom c3) is niet zichtbaar in het plan. Het probleem treedt op omdat niet aan het tweede criterium wordt voldaan: de indexgrootte van bladpagina's in vergelijking met het instellen van het maximale servergeheugen.

   Het gegevenstype van de kolom c2en c4 is char(30), c4 terwijl het gegevenstype van de kolom c3 is char(20). De grootte van elke indexrij ic3 is kleiner dan andere, dus het aantal bladpagina's is kleiner dan andere.

   Met behulp van de functie dynamisch beheer (DMF) sys.dm_db_database_page_allocationskunt u het aantal pagina's voor elke index berekenen. Voor indexen ic2, ic4en ic5, elke index heeft 214 pagina's en 209 hiervan zijn bladpagina's (resultaten kunnen enigszins variëren). Het geheugen dat wordt verbruikt door bladpagina's is 209 x 8 = 1.672 kB. Daarom is de verhouding 1672/(1500 x 1024) = 0,00108854101, die groter is dan 1/1000. De ic3 enige pagina's zijn echter 161 pagina's; 159 pagina's zijn bladpagina's. De verhouding is 159 x 8/(1500 x 1024) = 0,000828125, kleiner dan 1/1000 (0,001).

   Als u meer rijen invoegt of het maximale servergeheugen beperkt om aan het criterium te voldoen, wordt het plan gewijzigd. Als u de grootte van het indexbladniveau groter wilt maken dan 1/1000, kunt u de maximale servergeheugeninstelling verlagen tot 1200 door de volgende opdrachten uit te voeren:

   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 dit geval is 159 x 8/(1200 x 1024) = 0,00103515625 > 1/1000. Na deze wijziging wordt het ic3 weergegeven in het plan.

   Zie Transact-SQL gebruiken voor meer informatie.show advanced options

   In de volgende schermopname ziet u dat in het brede plan alle indexen worden gebruikt wanneer de geheugendrempel wordt bereikt:

   

Is een breed plan sneller dan een smal plan?

Het antwoord is dat het afhankelijk is van of de gegevens- en indexpagina's in de cache worden opgeslagen in de buffergroep of niet.

Gegevens worden in de cache opgeslagen in de buffergroep

Als de gegevens zich al in de buffergroep bevinden, biedt de query met het brede plan niet noodzakelijkerwijs extra prestatievoordelen in vergelijking met smalle abonnementen, omdat het brede plan is ontworpen om de I/O-prestaties te verbeteren (fysieke leesbewerkingen, niet logische leesbewerkingen).

Als u wilt testen of een breed plan sneller is dan een smal plan wanneer de gegevens zich in een buffergroep bevinden, volgt u deze stappen in de volgende omgeving:

• SQL Server 2019 CU11

• Maximaal servergeheugen: 30.000 MB

• De gegevensgrootte is 64 MB, terwijl de indexgrootte ongeveer 127 MB is.

• Databasebestanden bevinden zich op twee verschillende fysieke schijven:

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

1. Maak een andere tabel mytable2door de volgende opdrachten uit te voeren:

   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. Voer de volgende twee query's uit om de queryplannen te vergelijken:

   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
   

   Zie traceringsvlag 8790 en traceringsvlag 2338 voor meer informatie.

   De query met het brede plan duurt 0,136 seconden, terwijl de query met het smalle plan slechts 0,112 seconden duurt. De twee duur zijn zeer dichtbij en de update per index (breed plan) is minder nuttig omdat de gegevens zich al in de buffer bevinden voordat de UPDATE instructie werd uitgevoerd.

   In de volgende schermopname ziet u brede en smalle plannen wanneer gegevens in de cache worden opgeslagen in de buffergroep:

   

Gegevens worden niet in de cache opgeslagen in de buffergroep

Als u wilt testen of een breed plan sneller is dan een smal plan wanneer de gegevens zich niet in de buffergroep bevinden, voert u de volgende query's uit:

Notitie

Wanneer u de test uitvoert, moet u ervoor zorgen dat uw werkbelasting de enige workload in SQL Server is en dat de schijven zijn toegewezen aan 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

De query met een breed plan duurt 3,554 seconden, terwijl de query met een smal plan 6,701 seconden duurt. De brede planquery wordt deze keer sneller uitgevoerd.

In de volgende schermopname ziet u het brede plan wanneer gegevens niet in de cache worden opgeslagen in de buffergroep:

In de volgende schermopname ziet u het smalle plan wanneer gegevens niet in de cache worden opgeslagen in de buffergroep:

Is een brede planquery altijd sneller dan een smal queryplan wanneer gegevens zich niet in de buffer bevinden?

Het antwoord is 'niet altijd'. Voer de volgende stappen uit om te testen of de brede planquery altijd sneller is dan het smalle queryplan wanneer de gegevens zich niet in de buffer bevinden:

1. Maak een andere tabel mytable2door de volgende opdrachten uit te voeren:

   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
   

   Dit mytable3 is hetzelfde als mytable2, met uitzondering van de gegevens. mytable3 heeft alle vijf kolommen met dezelfde waarde, waardoor de volgorde van niet-geclusterde indexen de volgorde van de geclusterde index volgt. Door deze sortering van de gegevens wordt het voordeel van het brede plan geminimaliseerd.

2. Voer de volgende opdrachten uit om de queryplannen te vergelijken:

   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
   

   De duur van beide query's is aanzienlijk verminderd. Het brede plan duurt 0,304 seconden, wat iets langzamer is dan het smalle plan deze keer.

   In de volgende schermopname ziet u hoe de prestaties worden vergeleken wanneer breed en smal worden gebruikt:

   

Scenario's waarin de brede plannen worden toegepast

Hier volgen de andere scenario's waarin ook brede plannen worden toegepast:

De geclusterde indexkolom heeft een unieke of primaire sleutel en meerdere rijen worden bijgewerkt

Hier volgt een voorbeeld om het scenario te reproduceren:

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)

In de volgende schermopname ziet u dat het brede plan wordt gebruikt wanneer de clusterindex een unieke sleutel heeft:

Raadpleeg Onderhoud van unieke indexen voor meer informatie.

De clusterindexkolom wordt opgegeven in het partitieschema

Hier volgt een voorbeeld om het scenario te reproduceren:

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 

In de volgende schermopname ziet u dat het brede plan wordt gebruikt wanneer er een geclusterde kolom in het partitieschema staat:

De geclusterde indexkolom maakt geen deel uit van het partitieschema en de kolom partitieschema wordt bijgewerkt

Hier volgt een voorbeeld om het scenario te reproduceren:

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)

In de volgende schermopname ziet u dat het brede plan wordt gebruikt wanneer de kolom partitieschema wordt bijgewerkt:

Conclusie

• SQL Server kiest een brede planupdate wanneer aan de volgende criteria tegelijkertijd wordt voldaan:

  • Het beïnvloede aantal rijen is groter dan 250.
  • Het geheugen van de leaf-index is ten minste 1/1000 van de maximale servergeheugeninstelling.

• Brede plannen verbeteren de prestaties ten koste van het verbruik van extra geheugen.

• Als het verwachte queryplan niet wordt gebruikt, kan dit worden veroorzaakt door verlopen statistieken (niet de juiste gegevensgrootte rapporteren), de maximale servergeheugeninstelling of andere niet-gerelateerde problemen, zoals parametergevoelige plannen.

• De duur van instructies die een breed plan gebruiken, is afhankelijk van UPDATE verschillende factoren en in sommige gevallen kan het langer duren dan beperkte plannen.

• Traceringsvlag 8790 dwingt een breed plan af; traceringsvlag 2338 dwingt een smal plan af.