Solución de problemas de rendimiento de UPDATE con planes estrechos y amplios en SQL Server

Se aplica a: SQL Server

Una UPDATE instrucción puede ser más rápida en algunos casos y más lenta en otros. Hay muchos factores que pueden dar lugar a dicha varianza, como el número de filas actualizadas y el uso de recursos en el sistema (bloqueo, CPU, memoria o E/S). En este artículo se explica una razón específica de la varianza: la elección del plan de consulta realizada por SQL Server.

¿Qué son los planes estrechos y anchos?

Al ejecutar una UPDATE instrucción en una columna de índice clúster, SQL Server actualiza no solo el propio índice agrupado, sino también todos los índices no agrupados porque los índices no agrupados contienen la clave de índice del clúster.

SQL Server tiene dos opciones para realizar la actualización:

• Plan estrecho: realice la actualización de índices no agrupados junto con la actualización de clave de índice en clúster. Este enfoque sencillo es fácil de entender; actualice el índice clúster y, a continuación, actualice todos los índices no agrupados al mismo tiempo. SQL Server actualizará una fila y pasará a la siguiente hasta que se completen todas. Este enfoque se denomina actualización de plan estrecho o actualización de Per-Row. Sin embargo, esta operación es relativamente costosa porque el orden de los datos de índice no agrupados que se actualizarán puede no estar en el orden de los datos de índice agrupados. Si hay muchas páginas de índice implicadas en la actualización, cuando los datos están en el disco, se puede producir un gran número de solicitudes de E/S aleatorias.

• Plan amplio: para optimizar el rendimiento y reducir la E/S aleatoria, SQL Server puede elegir un plan amplio. No realiza la actualización de índices no agrupados junto con la actualización de índices agrupados. En su lugar, ordena primero todos los datos de índice no agrupados en la memoria y, a continuación, actualiza todos los índices en ese orden. Este enfoque se denomina plan amplio (también denominado actualización de Per-Index).

Esta es una captura de pantalla de planes estrechos y anchos:

¿Cuándo SQL Server elegir un plan amplio?

Se deben cumplir dos criterios para que SQL Server elija un plan amplio:

• El número de filas afectadas es mayor que 250.
• El tamaño del nivel hoja de los índices no agrupados (recuento de páginas de índice * 8 KB) es al menos 1/1000 de la configuración de memoria máxima del servidor.

¿Cómo funcionan los planes estrechos y anchos?

Para comprender cómo funcionan los planes estrechos y anchos, siga estos pasos en el entorno siguiente:

• SQL Server 2019 CU11
• Memoria máxima del servidor = 1.500 MB

1. Ejecute el siguiente script para crear una tabla mytable1 que tenga 41 501 filas, un índice clúster en la columna c1y cinco índices no agrupados en el resto de las columnas, respectivamente.

   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. Ejecute las tres instrucciones T-SQL UPDATE siguientes y compare los planes de consulta:

   • UPDATE mytable1 SET c1=c1 WHERE c1=1 OPTION(RECOMPILE) : se actualiza una fila
   • UPDATE mytable1 SET c1=c1 WHERE c1=50000 OPTION(RECOMPILE) - Se actualizan 250 filas.
   • UPDATE mytable1 SET c1=c1 WHERE c1=50001 OPTION(RECOMPILE) - Se actualizan 251 filas.

3. Examine los resultados según el primer criterio (el umbral del número afectado de filas es 250).

   En la captura de pantalla siguiente se muestran los resultados en función del primer criterio:

   

   Como se esperaba, el optimizador de consultas elige un plan estrecho para las dos primeras consultas porque el número de filas afectadas es menor que 250. Se usa un plan ancho para la tercera consulta porque el recuento de filas afectado es 251, que es mayor que 250.

4. Examine los resultados según el segundo criterio (la memoria del tamaño del índice hoja es al menos 1/1000 de la configuración de memoria máxima del servidor).

   En la captura de pantalla siguiente se muestran los resultados en función del segundo criterio:

   

   Se selecciona un plan ancho para la tercera UPDATE consulta. Pero el índice ic3 (en la columna c3) no se ve en el plan. El problema se produce porque no se cumple el segundo criterio: tamaño de índice de páginas hoja en comparación con la configuración de memoria máxima del servidor.

   El tipo de datos de columna , y es , mientras que el tipo de datos de columna c3 es char(20).char(30)c4c4c2 El tamaño de cada fila de índice ic3 es menor que otros, por lo que el número de páginas hoja es menor que otras.

   Con la ayuda de la función de administración dinámica (DMF), sys.dm_db_database_page_allocationspuede calcular el número de páginas para cada índice. Para los índices ic2, ic4y ic5, cada índice tiene 214 páginas y 209 de ellas son páginas hoja (los resultados pueden variar ligeramente). La memoria consumida por las páginas hoja es de 209 x 8 = 1672 KB. Por lo tanto, la relación es 1672/(1500 x 1024) = 0,00108854101, que es mayor que 1/1000. Sin embargo, el ic3 único tiene 161 páginas; 159 de ellas son páginas hoja. La relación es 159 x 8/(1500 x 1024) = 0,000828125, que es menor que 1/1000 (0,001).

   Si inserta más filas o reduce la memoria máxima del servidor para cumplir el criterio, el plan cambiará. Para que el tamaño de nivel hoja del índice sea mayor que 1/1000, puede reducir la configuración de memoria máxima del servidor un poco a 1200 mediante la ejecución de los siguientes comandos:

   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.
   

   En este caso, 159 x 8/(1200 x 1024) = 0,00103515625 > 1/1000. Después de este cambio, ic3 aparece en el plan.

   Para obtener más información sobre show advanced options, vea Uso de Transact-SQL.

   En la captura de pantalla siguiente se muestra que el plan ancho usa todos los índices cuando se alcanza el umbral de memoria:

   

¿Un plan amplio es más rápido que un plan estrecho?

La respuesta es que depende de si los datos y las páginas de índice se almacenan en caché en el grupo de búferes o no.

Los datos se almacenan en caché en el grupo de búferes

Si los datos ya están en el grupo de búferes, la consulta con el plan ancho no ofrece necesariamente ventajas de rendimiento adicionales en comparación con los planes reducidos porque el plan ancho está diseñado para mejorar el rendimiento de E/S (lecturas físicas, no lecturas lógicas).

Para probar si un plan amplio es más rápido que un plan restringido cuando los datos están en un grupo de búferes, siga estos pasos en el entorno siguiente:

• SQL Server 2019 CU11

• Memoria máxima del servidor: 30 000 MB

• El tamaño de los datos es de 64 MB, mientras que el tamaño del índice es de aproximadamente 127 MB.

• Los archivos de base de datos se encuentran en dos discos físicos diferentes:

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

1. Cree otra tabla, mytable2, ejecutando los siguientes comandos:

   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. Ejecute las dos consultas siguientes para comparar los planes de consulta:

   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
   

   Para obtener más información, consulte la marca de seguimiento 8790 y la marca de seguimiento 2338.

   La consulta con el plan ancho tarda 0,136 segundos, mientras que la consulta con el plan estrecho solo tarda 0,112 segundos. Las dos duraciones son muy cercanas y la actualización de Per-Index (plan ancho) es menos beneficiosa porque los datos ya están en el búfer antes de que se ejecutara la UPDATE instrucción.

   En la captura de pantalla siguiente se muestran planes anchos y estrechos cuando los datos se almacenan en caché en el grupo de búferes:

   

Los datos no se almacenan en caché en el grupo de búferes

Para probar si un plan amplio es más rápido que un plan estrecho cuando los datos no están en el grupo de búferes, ejecute las siguientes consultas:

Nota:

Al realizar la prueba, asegúrese de que la suya es la única carga de trabajo de SQL Server y que los discos están dedicados a 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

La consulta con un plan ancho tarda 3.554 segundos, mientras que la consulta con un plan estrecho tarda 6.701 segundos. Esta vez, la consulta de plan ancho se ejecuta más rápido.

En la captura de pantalla siguiente se muestra el plan ancho cuando los datos no se almacenan en caché en el grupo de búferes:

En la captura de pantalla siguiente se muestra el plan restringido cuando los datos no se almacenan en caché en el grupo de búferes:

¿Una consulta de plan ancho siempre es más rápida que un plan de consulta estrecho cuando los datos no están en el búfer?

La respuesta es "no siempre". Para probar si la consulta de plan ancho siempre es más rápida que el plan de consulta restringido cuando los datos no están en el búfer, siga estos pasos:

1. Cree otra tabla, mytable2, ejecutando los siguientes comandos:

   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 es igual que mytable2, excepto para los datos. mytable3 tiene las cinco columnas con el mismo valor, lo que hace que el orden de los índices no agrupados siga el orden del índice clúster. Esta ordenación de los datos minimizará la ventaja del plan ancho.

2. Ejecute los siguientes comandos para comparar los planes de consulta:

   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 duración de ambas consultas se reduce significativamente. El plan ancho tarda 0,304 segundos, lo que es un poco más lento que el plan estrecho esta vez.

   En la captura de pantalla siguiente se muestra la comparación del rendimiento cuando se usan anchos y estrechos:

   

Escenarios en los que se aplican los planes amplios

Estos son los otros escenarios en los que también se aplican planes amplios:

La columna de índice agrupado tiene una clave única o principal y se actualizan varias filas.

Este es un ejemplo para reproducir el escenario:

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)

En la captura de pantalla siguiente se muestra que el plan ancho se usa cuando el índice del clúster tiene una clave única:

Para obtener más información, consulte Mantenimiento de índices únicos.

La columna de índice de clúster se especifica en el esquema de partición

Este es un ejemplo para reproducir el escenario:

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 

En la captura de pantalla siguiente se muestra que el plan ancho se usa cuando hay una columna agrupada en el esquema de partición:

La columna de índice clúster no forma parte del esquema de partición y la columna de esquema de partición se actualiza.

Este es un ejemplo para reproducir el escenario:

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)

En la captura de pantalla siguiente se muestra que se usa el plan ancho cuando se actualiza la columna de esquema de partición:

Conclusión

• SQL Server elige una actualización de plan amplia cuando se cumplen los criterios siguientes al mismo tiempo:

  • El número afectado de filas es mayor que 250.
  • La memoria del índice hoja es al menos 1/1000 de la configuración de memoria máxima del servidor.

• Los planes amplios aumentan el rendimiento a costa de consumir memoria adicional.

• Si no se usa el plan de consulta esperado, puede deberse a estadísticas obsoletas (que no notifican el tamaño de datos correcto), a la configuración de memoria máxima del servidor u otros problemas no relacionados, como los planes confidenciales de parámetros.

• La duración de UPDATE las instrucciones que usan un plan amplio depende de varios factores y, en algunos casos, puede tardar más que planes estrechos.

• La marca de seguimiento 8790 forzará un plan ancho; la marca de seguimiento 2338 forzará un plan estrecho.