Поделиться через

Устранение неполадок с производительностью UPDATE с узкими и широкими планами в SQL Server

  • Статья

Применяется к: SQL Server

Оператор UPDATE может быть быстрее в некоторых случаях и медленнее в других случаях. Существует множество факторов, которые могут привести к такой дисперсии, включая количество обновленных строк и использование ресурсов в системе (блокировка, ЦП, память или ввод-вывод). В этой статье рассматривается одна из конкретных причин дисперсии: выбор плана запроса, созданного SQL Server.

Что такое узкие и широкие планы?

При выполнении UPDATE инструкции для столбца кластеризованного индекса SQL Server обновляет не только кластеризованный индекс, но и все некластикционные индексы, так как некластикционные индексы содержат ключ индекса кластера.

SQL Server имеет два варианта выполнения обновления:

  • Узкий план. Обновление некластикционного индекса вместе с обновлением ключа кластеризованного индекса. Этот простой подход легко понять; обновите кластеризованный индекс и обновите все некластикционные индексы одновременно. SQL Server обновит одну строку и перейдет к следующей, пока все не завершится. Этот подход называется узким обновлением плана или обновлением для каждой строки. Однако эта операция является относительно дорогой, так как порядок некластикционных данных индекса, которые будут обновлены, могут не находиться в порядке кластеризованных данных индекса. Если во время обновления участвуют многие страницы индекса, при наличии данных на диске может возникнуть большое количество случайных запросов ввода-вывода.

  • Широкий план. Для оптимизации производительности и уменьшения случайного ввода-вывода SQL Server может выбрать широкий план. Он не выполняет обновление некластикционных индексов вместе с кластеризованным обновлением индекса. Вместо этого он сортирует все некластикционные данные индекса в памяти, а затем обновляет все индексы в этом порядке. Этот подход называется широким планом (также называемым обновлением per-Index).

Ниже приведен снимок экрана с узкими и широкими планами:

Снимок экрана: узкие и широкие планы.

Когда SQL Server выбирает широкий план?

Для SQL Server необходимо выполнить два критерия, чтобы выбрать широкий план:

  • Число затронутых строк превышает 250.
  • Размер конечного уровня некластичных индексов (число страниц индексов * 8 КБ) составляет не менее 1/1000 параметра максимальной памяти сервера.

Как работают узкие и широкие планы?

Чтобы понять, как работают узкие и широкие планы, выполните следующие действия в следующей среде:

  • SQL Server 2019 CU11
  • Максимальная память сервера = 1500 МБ

  1. Выполните следующий сценарий, чтобы создать таблицу mytable1 с 41501 строками, одним кластеризованным индексом столбца c1и пятью некластикционными индексами в остальных столбцах соответственно.

    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. Выполните следующие три инструкции T-SQL UPDATE и сравните планы запросов:

    • UPDATE mytable1 SET c1=c1 WHERE c1=1 OPTION(RECOMPILE) — обновляется одна строка
    • UPDATE mytable1 SET c1=c1 WHERE c1=50000 OPTION(RECOMPILE) — обновляются 250 строк.
    • UPDATE mytable1 SET c1=c1 WHERE c1=50001 OPTION(RECOMPILE) — обновляются 251 строки.

  3. Изучите результаты на основе первого критерия (пороговое значение затронутого числа строк равно 250).

    На следующем снимку экрана показаны результаты на основе первого критерия:

    Снимок экрана: широкие и узкие планы на основе размера индекса.

    Как ожидается, оптимизатор запросов выбирает узкий план для первых двух запросов, так как количество затронутых строк меньше 250. Для третьего запроса используется широкий план, так как число затронутых строк равно 251, что больше 250.

  4. Проверьте результаты на основе второго критерия (объем памяти конечного индекса составляет не менее 1/1000 параметра максимальной памяти сервера).

    На следующем снимку экрана показаны результаты на основе второго критерия:

    Снимок экрана: широкий план, не использующий индекс из-за размера.

    Для третьего UPDATE запроса выбран широкий план. Но индекс ic3 (в столбце c3) не отображается в плане. Проблема возникает из-за того, что второй критерий не соответствует — размер индекса конечных страниц по сравнению с параметром максимальной памяти сервера.

    Тип данных столбца и имеет char(30)тип данных, а тип данных столбца c3 — char(20).c4 c2c4 Размер каждой строки индекса ic3 меньше, чем другие, поэтому количество конечных страниц меньше, чем другие.

    С помощью динамической функции управления (DMF) sys.dm_db_database_page_allocationsможно вычислить количество страниц для каждого индекса. Для индексов ic2, ic4и ic5, каждый индекс имеет 214 страниц, и 209 из них являются конечными страницами (результаты могут немного отличаться). Объем памяти, потребляемой конечными страницами, составляет 209 x 8 = 1672 КБ. Поэтому соотношение равно 1672/(1500 x 1024) = 0,00108854101, что больше 1/1000. ic3 Однако только 161 страниц; 159 из них являются конечными страницами. Соотношение равно 159 x 8/(1500 x 1024) = 0,000828125, что меньше 1/1000 (0,001).

    Если вы вставляете больше строк или сокращаете максимальный объем памяти сервера в соответствии с критерием, план изменится. Чтобы сделать размер конечного уровня индекса больше 1/1000, можно уменьшить максимальный размер памяти сервера до 1200, выполнив следующие команды:

    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.

    В этом случае 159 x 8/(1200 x 1024) = 0,00103515625 > 1/1000. После этого изменения ic3 появится в плане.

    Дополнительные сведения см. в show advanced optionsразделе "Использование Transact-SQL".

    На следующем снимку экрана показано, что широкий план использует все индексы при достижении порогового значения памяти:

    Снимок экрана: широкий план, использующий все индексы при достижении порогового значения памяти.

Является ли широкий план быстрее, чем узкий план?

Ответ заключается в том, что он зависит от того, кэшируются ли страницы данных и индексов в буферном пуле.

Данные кэшируются в буферном пуле

Если данные уже в буферном пуле, запрос с широким планом не обязательно обеспечивает дополнительные преимущества производительности по сравнению с узкими планами, так как широкий план предназначен для повышения производительности операций ввода-вывода (физические операции чтения, а не логические операции чтения).

Чтобы проверить, выполняется ли широкий план быстрее, чем узкий план, если данные входят в буферный пул, выполните следующие действия в следующей среде:

  • SQL Server 2019 CU11

  • Максимальная память сервера: 30 000 МБ

  • Размер данных составляет 64 МБ, а размер индекса составляет около 127 МБ.

  • Файлы базы данных находятся на двух разных физических дисках:

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

  1. Создайте другую таблицу, mytable2выполнив следующие команды:

    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. Выполните следующие два запроса, чтобы сравнить планы запросов:

    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

    Дополнительные сведения см. в разделе флаг трассировки 8790 и флаг трассировки 2338.

    Запрос с широким планом занимает 0,136 секунд, а запрос с узким планом занимает только 0,112 секунды. Две длительности очень близки, и обновление per-Index (широкий план) менее полезно, так как данные уже находятся в буфере до UPDATE выполнения инструкции.

    На следующем снимке экрана показаны широкие и узкие планы при кэшировании данных в буферном пуле:

    Снимок экрана: широкие и узкие планы при кэшировании данных в буферном пуле.

Данные не кэшируются в буферном пуле

Чтобы проверить, выполняется ли широкий план быстрее, чем узкий план, если данные не в буферном пуле, выполните следующие запросы:

Примечание.

При тестировании убедитесь, что ваша рабочая нагрузка является единственной рабочей нагрузкой в SQL Server, а диски выделены для 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

Запрос с широким планом занимает 3,554 секунды, а запрос с узким планом занимает 6,701 секунды. Запрос на широкий план выполняется быстрее на этот раз.

На следующем снимке экрана показан широкий план, если данные не кэшируются в буферном пуле:

Снимок экрана: широкий план, когда данные не кэшируются в буферном пуле.

На следующем снимке экрана показан узкий план, если данные не кэшируются в буферном пуле:

Снимок экрана: узкий план, когда данные не кэшируются в буферном пуле.

Является ли запрос широкого плана всегда быстрее, чем узкий план запроса, если данные не в буфере?

Ответ "не всегда". Чтобы проверить, всегда ли запрос широкого плана быстрее, чем узкий план запроса, если данные не в буфере, выполните следующие действия.

  1. Создайте другую таблицу, mytable2выполнив следующие команды:

    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 же самое, что mytable2и данные. mytable3 имеет все пять столбцов с одинаковым значением, что делает порядок некластикционных индексов следовать порядку кластеризованного индекса. Эта сортировка данных свести к минимуму преимущества широкого плана.

  2. Выполните следующие команды, чтобы сравнить планы запросов:

    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

    Длительность обоих запросов значительно уменьшается! Широкий план занимает 0,304 секунды, что немного медленнее узкого плана на этот раз.

    На следующем снимку экрана показано сравнение производительности при использовании широкого и узкого.

    Снимок экрана: сравнение производительности при использовании широкого и узкого.

Сценарии, в которых применяются широкие планы

Ниже приведены другие сценарии, в которых также применяются широкие планы:

Столбец кластеризованного индекса имеет уникальный или первичный ключ, а несколько строк обновляются.

Ниже приведен пример воспроизведения сценария:

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)

На следующем снимку экрана показано, что широкий план используется при использовании индекса кластера уникального ключа:

Снимок экрана: широкий план, используемый при использовании индекса кластера уникальным ключом.

Дополнительные сведения см. в статье "Обслуживание уникальных индексов".

Столбец индекса кластера указан в схеме секционирования

Ниже приведен пример воспроизведения сценария:

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

На следующем снимке экрана показано, что широкий план используется при наличии кластеризованного столбца в схеме секционирования:

Снимок экрана: широкий план используется при наличии кластеризованного столбца в схеме секционирования.

Столбец кластеризованного индекса не является частью схемы секционирования, а столбец схемы секционирования обновляется.

Ниже приведен пример воспроизведения сценария:

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)

На следующем снимку экрана показано, что при обновлении столбца схемы секционирования используется широкий план:

Снимок экрана: широкий план, используемый при обновлении столбца схемы секционирования.

Заключение

  • SQL Server выбирает обновление широкого плана, если в то же время выполняются следующие условия:

    • Затронуто число строк больше 250.
    • Память конечного индекса составляет не менее 1/1000 параметра максимальной памяти сервера.

  • Широкие планы повышают производительность за счет использования дополнительной памяти.

  • Если ожидаемый план запроса не используется, это может быть вызвано устаревшими статистическими данными (не сообщая правильный размер данных), максимальным параметром памяти сервера или другими не связанными проблемами, такими как планы с учетом параметров.

  • Длительность инструкций UPDATE , использующих широкий план, зависит от нескольких факторов, и в некоторых случаях может занять больше времени, чем узкие планы.

  • Флаг трассировки 8790 принудительно принудит широкий план; флаг трассировки 2338 принудительно задаст узкий план.