Risolvere i problemi di prestazioni di UPDATE con piani ristretti e estesi in SQL Server

Si applica a: SQL Server

Un'istruzione UPDATE può essere più veloce in alcuni casi e più lenta in altri. Esistono molti fattori che possono causare tale varianza, tra cui il numero di righe aggiornate e l'utilizzo delle risorse nel sistema (blocco, CPU, memoria o I/O). Questo articolo illustra un motivo specifico per la varianza: la scelta del piano di query eseguita da SQL Server.

Che cosa sono i piani stretti e ampi?

Quando si esegue un'istruzione UPDATE su una colonna di indice cluster, SQL Server aggiorna non solo l'indice cluster stesso, ma anche tutti gli indici non cluster perché gli indici non cluster contengono la chiave di indice del cluster.

SQL Server dispone di due opzioni per eseguire l'aggiornamento:

• Piano ristretto: eseguire l'aggiornamento dell'indice non cluster insieme all'aggiornamento della chiave di indice cluster. Questo approccio semplice è facile da comprendere; aggiornare l'indice cluster e quindi tutti gli indici non cluster contemporaneamente. SQL Server aggiornerà una riga e passerà alla riga successiva fino al completamento di tutti. Questo approccio è denominato aggiornamento di piano ristretto o aggiornamento Per-Row. Tuttavia, questa operazione è relativamente costosa perché l'ordine dei dati di indice non cluster che verranno aggiornati potrebbe non essere nell'ordine dei dati dell'indice cluster. Se molte pagine di indice sono coinvolte nell'aggiornamento, quando i dati sono su disco, può verificarsi un numero elevato di richieste di I/O casuali.

• Piano ampio: per ottimizzare le prestazioni e ridurre le operazioni di I/O casuali, SQL Server possono scegliere un piano ampio. Non esegue l'aggiornamento degli indici non cluster insieme all'aggiornamento dell'indice cluster. Ordina invece tutti i dati di indice non cluster in memoria e quindi aggiorna tutti gli indici in tale ordine. Questo approccio è denominato piano wide (detto anche aggiornamento Per-Index).

Ecco uno screenshot dei piani stretti e estesi:

Quando SQL Server scegliere un piano ampio?

Per SQL Server scegliere un piano ampio, è necessario soddisfare due criteri:

• Il numero di righe interessate è maggiore di 250.
• Le dimensioni del livello foglia degli indici non cluster (numero di pagine di indice * 8 KB) sono almeno 1/1000 dell'impostazione max server memory.

Come funzionano i piani stretti e estesi?

Per comprendere il funzionamento dei piani stretti e estesi, seguire questa procedura nell'ambiente seguente:

• SQL Server 2019 CU11
• Memoria massima del server = 1.500 MB

1. Eseguire lo script seguente per creare una tabella mytable1 contenente rispettivamente 41.501 righe, un indice cluster nella colonna c1e cinque indici non cluster nel resto delle colonne.

   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. Eseguire le tre istruzioni T-SQL UPDATE seguenti e confrontare i piani di query:

   • UPDATE mytable1 SET c1=c1 WHERE c1=1 OPTION(RECOMPILE) - Viene aggiornata una riga
   • UPDATE mytable1 SET c1=c1 WHERE c1=50000 OPTION(RECOMPILE) - Vengono aggiornate 250 righe.
   • UPDATE mytable1 SET c1=c1 WHERE c1=50001 OPTION(RECOMPILE) - Vengono aggiornate 251 righe.

3. Esaminare i risultati in base al primo criterio (la soglia del numero di righe interessato è 250).

   Lo screenshot seguente mostra i risultati in base al primo criterio:

   

   Come previsto, Query Optimizer sceglie un piano ristretto per le prime due query perché il numero di righe interessate è inferiore a 250. Per la terza query viene usato un piano wide perché il numero di righe interessate è 251, ovvero maggiore di 250.

4. Esaminare i risultati in base al secondo criterio (la memoria delle dimensioni dell'indice foglia è almeno 1/1000 dell'impostazione max server memory).

   Lo screenshot seguente mostra i risultati in base al secondo criterio:

   

   Per la terza UPDATE query viene selezionato un piano wide. Ma l'indice ic3 (nella colonna c3) non viene visualizzato nel piano. Il problema si verifica perché il secondo criterio non viene soddisfatto: le dimensioni dell'indice delle pagine foglia rispetto all'impostazione max server memory.

   Il tipo di dati della colonna , e è , mentre il tipo di dati della colonna c3 è char(20).char(30)c4c4c2 La dimensione di ogni riga di indice ic3 è minore di altre, quindi il numero di pagine foglia è minore di altre.

   Con l'aiuto della funzione DMF (Dynamic Management Function), sys.dm_db_database_page_allocationsè possibile calcolare il numero di pagine per ogni indice. Per gli indici ic2, ic4e ic5, ogni indice ha 214 pagine e 209 sono pagine foglia (i risultati possono variare leggermente). La memoria utilizzata dalle pagine foglia è 209 x 8 = 1.672 KB. Pertanto, il rapporto è 1672/(1500 x 1024) = 0,00108854101, maggiore di 1/1000. Tuttavia, l'unico ic3 ha 161 pagine, 159 delle quali sono pagine foglia. Il rapporto è 159 x 8/(1500 x 1024) = 0,000828125, che è minore di 1/1000 (0,001).

   Se si inseriscono più righe o si riduce la memoria massima del server per soddisfare il criterio, il piano cambierà. Per rendere la dimensione del livello foglia dell'indice maggiore di 1/1000, è possibile ridurre l'impostazione max server memory a bit a 1.200 eseguendo i comandi seguenti:

   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 questo caso, 159 x 8/(1200 x 1024) = 0,00103515625 > 1/1000. Dopo questa modifica, l'oggetto ic3 viene visualizzato nel piano.

   Per altre informazioni su show advanced options, vedere Usare Transact-SQL.

   Lo screenshot seguente mostra che il piano wide usa tutti gli indici quando viene raggiunta la soglia di memoria:

   

Un piano ampio è più veloce di un piano ristretto?

La risposta è che dipende dal fatto che i dati e le pagine di indice siano memorizzati nella cache nel pool di buffer o meno.

I dati vengono memorizzati nella cache nel pool di buffer

Se i dati si trova già nel pool di buffer, la query con il piano wide non offre necessariamente vantaggi di prestazioni aggiuntivi rispetto ai piani limitati perché il piano wide è progettato per migliorare le prestazioni di I/O (letture fisiche, non letture logiche).

Per verificare se un piano wide è più veloce di un piano ristretto quando i dati si trova in un pool di buffer, seguire questa procedura nell'ambiente seguente:

• SQL Server 2019 CU11

• Memoria massima del server: 30.000 MB

• Le dimensioni dei dati sono di 64 MB, mentre le dimensioni dell'indice sono di circa 127 MB.

• I file di database si trovano in due dischi fisici diversi:

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

1. Creare un'altra tabella, mytable2, eseguendo i comandi seguenti:

   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. Eseguire le due query seguenti per confrontare i piani di query:

   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
   

   Per altre informazioni, vedere flag di traccia 8790 e flag di traccia 2338.

   La query con il piano wide richiede 0,136 secondi, mentre la query con il piano ristretto richiede solo 0,112 secondi. Le due durate sono molto vicine e l'aggiornamento Per-Index (piano wide) è meno vantaggioso perché i dati si trovano già nel buffer prima dell'esecuzione dell'istruzione UPDATE .

   Lo screenshot seguente mostra i piani estesi e limitati quando i dati vengono memorizzati nella cache nel pool di buffer:

   

I dati non vengono memorizzati nella cache nel pool di buffer

Per verificare se un piano wide è più veloce di un piano ristretto quando i dati non si trova nel pool di buffer, eseguire le query seguenti:

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

La query con un piano wide richiede 3,554 secondi, mentre la query con un piano ristretto richiede 6,701 secondi. Questa volta la query di piano wide viene eseguita più velocemente.

Lo screenshot seguente mostra il piano wide quando i dati non vengono memorizzati nella cache nel pool di buffer:

Lo screenshot seguente mostra il piano ristretto quando i dati non vengono memorizzati nella cache nel pool di buffer:

Una query di piano wide è sempre più veloce di un piano di query ristretto quando i dati non sono nel buffer?

La risposta è "non sempre". Per verificare se la query di piano esteso è sempre più veloce del piano di query ristretto quando i dati non sono nel buffer, seguire questa procedura:

1. Creare un'altra tabella, mytable2, eseguendo i comandi seguenti:

   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 uguale mytable2a , ad eccezione dei dati. mytable3 include tutte e cinque le colonne con lo stesso valore, in modo che l'ordine degli indici non cluster segua l'ordine dell'indice cluster. Questo ordinamento dei dati ridurrà al minimo il vantaggio del piano wide.

2. Eseguire i comandi seguenti per confrontare i piani di query:

   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
   

   La durata di entrambe le query viene ridotta in modo significativo. Il piano wide richiede 0,304 secondi, che questa volta è un po' più lento del piano ristretto.

   Lo screenshot seguente mostra il confronto delle prestazioni quando vengono usati larghezza e larghezza:

   

Scenari in cui vengono applicati i piani estesi

Ecco gli altri scenari in cui vengono applicati anche i piani wide:

La colonna dell'indice cluster ha una chiave univoca o primaria e più righe vengono aggiornate

Ecco un esempio per riprodurre lo scenario:

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)

Lo screenshot seguente mostra che il piano wide viene usato quando l'indice del cluster ha una chiave univoca:

Per altri dettagli, vedere Gestione degli indici univoci.

La colonna dell'indice del cluster viene specificata nello schema di partizione

Ecco un esempio per riprodurre lo scenario:

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 

Lo screenshot seguente mostra che il piano wide viene usato quando è presente una colonna cluster nello schema di partizione:

La colonna dell'indice cluster non fa parte dello schema di partizione e la colonna dello schema di partizione viene aggiornata

Ecco un esempio per riprodurre lo scenario:

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)

Lo screenshot seguente mostra che il piano wide viene usato quando viene aggiornata la colonna dello schema di partizione:

Conclusione

• SQL Server sceglie un aggiornamento di piano esteso quando vengono soddisfatti contemporaneamente i criteri seguenti:

  • Il numero di righe interessate è maggiore di 250.
  • La memoria dell'indice foglia è di almeno 1/1000 dell'impostazione max server memory.

• I piani wide migliorano le prestazioni a scapito dell'utilizzo di memoria aggiuntiva.

• Se il piano di query previsto non viene usato, potrebbe essere dovuto a statistiche non aggiornate (non segnalando le dimensioni corrette dei dati), all'impostazione della memoria massima del server o ad altri problemi non correlati, ad esempio i piani sensibili ai parametri.

• La durata delle UPDATE istruzioni che usano un piano ampio dipende da diversi fattori e, in alcuni casi, può richiedere più tempo dei piani limitati.

• Il flag di traccia 8790 forza un piano largo; il flag di traccia 2338 forza un piano ristretto.