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

Van toepassing op: SQL Server

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

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: de niet-geclusterde indexupdate uitvoeren samen met de update van de geclusterde indexsleutel. Deze eenvoudige aanpak is gemakkelijk te begrijpen; werk de geclusterde index bij en werk vervolgens alle niet-geclusterde indexen tegelijkertijd bij. SQL Server wordt één rij bijgewerkt en naar de volgende rij verplaatst totdat alles is voltooid. Deze methode wordt een narrow plan update of een Per-Row update 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 staat. Als er veel indexpagina's bij de update betrokken zijn en de gegevens zich op de schijf bevinden, kan er een groot aantal willekeurige I/O-aanvragen optreden.

• Breed plan: om de prestaties te optimaliseren en willekeurige I/O te verminderen, kunt 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 Per-Index update genoemd).

Hier volgt een schermopname van smalle en brede abonnementen:

Wanneer kiest SQL Server een breed abonnement?

Voor SQL Server moet aan twee criteria worden voldaan 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) - er wordt één rij 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 het queryoptimalisatieprogramma 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 (in kolom c3) wordt niet weergegeven in het plan. Het probleem treedt op omdat niet aan het tweede criterium wordt voldaan: indexgrootte van bladpagina's in vergelijking met de instelling maximumservergeheugen.

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

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

   Als u meer rijen invoegt of het maximum aantal servergeheugens vermindert om aan het criterium te voldoen, verandert het plan. Als u de grootte van het indexbladniveau groter wilt maken dan 1/1000, kunt u de instelling voor maximaal servergeheugen iets verlagen naar 1.200 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 159 x 8/(1200 x 1024) = 0,00103515625 > 1/1000. Na deze wijziging wordt de ic3 weergegeven in het plan.

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

   In de volgende schermopname ziet u dat het brede plan alle indexen gebruikt wanneer de geheugendrempelwaarde is 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 zijn 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 beperkt 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, mytable2, door 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 duurs liggen heel dicht bij elkaar en de Per-Index update (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 abonnementen 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 beperkt plan wanneer de gegevens zich niet in de buffergroep bevinden, voert u de volgende query's uit:

Opmerking

Wanneer u de test uitvoert, moet u ervoor zorgen dat de uwe 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 query breed plan wordt deze keer sneller uitgevoerd.

In de volgende schermopname ziet u het brede plan wanneer gegevens niet in de cache zijn 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 query met een breed plan altijd sneller dan een smal queryplan wanneer gegevens zich niet in de buffer bevinden?

Het antwoord is 'niet altijd'. Als u wilt testen of de query voor een breed plan altijd sneller is dan het smalle queryplan wanneer gegevens zich niet in de buffer bevinden, voert u de volgende stappen uit:

1. Maak een andere tabel, mytable2, door 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
   

   De 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 korter! Het brede plan duurt 0,304 seconden, wat een beetje langzamer is dan het smalle plan deze keer.

   In de volgende schermopname ziet u de vergelijking van prestaties 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 gegroepeerde 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:

Zie Unieke indexen onderhouden voor meer informatie.

De kolom Clusterindex is 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 het partitieschema een geclusterde kolom bevat:

De gegroepeerde indexkolom maakt geen deel uit van het partitieschema en de kolom voor het 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 voor het partitieschema wordt bijgewerkt:

Conclusie

• SQL Server kiest voor een update van een breed plan wanneer tegelijkertijd aan de volgende criteria wordt voldaan:

  • Het beïnvloede aantal rijen is groter dan 250.
  • Het geheugen van de leaf-index is ten minste 1/1000 van de instelling voor maximaal servergeheugen.

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

• Als het verwachte queryplan niet wordt gebruikt, kan dit worden veroorzaakt door verouderde statistieken (niet de juiste gegevensgrootte rapporteren), de maximale instelling van het servergeheugen 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 dit langer duren dan beperkte plannen.

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