OPTIMIZED Nested Loops Joins

Автор: Craig Freedman OPTIMIZED Nested Loops Joins

В предыдущих двух статьях мы рассмотрели при каких условиях SQL Server может добавить сортировку на внешней стороне соединения вложенных циклов и как эта сортировка может повысить производительность. А чуть раннее мы увидели как SQL Server может использовать упреждающую случайную выборку для повышения производительности соединения вложенных циклов. В этой статье давайте исследуем еще одну возможность повышения производительности соединения вложенных циклов. Я буду использовать ту же базу данных, которую использовал в двух предыдущих статьях.

Давайте начнем со следующего простого запроса:

SELECT SUM(Data)
FROM T
WHERE RandKey < 1000

  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1011]=(0) THEN NULL ELSE [Expr1012] END))
       |--Stream Aggregate(DEFINE:([Expr1011]=COUNT_BIG([T].[Data]), [Expr1012]=SUM([T].[Data])))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([T].[PK], [Expr1010]) OPTIMIZED WITH UNORDERED PREFETCH)
                 |--Index Seek(OBJECT:([T].[IRandKey]), SEEK:([T].[RandKey] < (1000)) ORDERED FORWARD)
                 |--Clustered Index Seek(OBJECT:([T].[PK__T__...]), SEEK:([T].[PK]=[T].[PK]) LOOKUP ORDERED FORWARD)

Обратите внимание, что соединение вложенных циклов (Nested Loops) включает дополнительное ключевое слово: OPTIMIZED. Это ключевое слово указывает, что соединение вложенных циклов может попытаться переупорядочить строки на входе для повышения производительности ввода-вывода. Это поведение похоже на явное добавление в план запроса сортировки, которые мы видели в двух предыдущих статьях, но в отличие от полной сортировки это выглядит менее затратно. То есть у оптимизированного Nested Loops результаты могут избежать полной сортировки (и на самом деле вряд ли будут отсортированы).

SQL Server использует оптимизированное соединение Nested Loops только тогда, когда оптимизатор на основе его оценок мощности и стоимости по доступной ему статистике приходит к выводу, что сортировка, скорее всего, не потребуется. Но при этом существует вероятность что сортировка может оказаться полезна в случае если оценки мощности или стоимости неверны и статистика не актуальна. Другими словами, оптимизированное соединение Nested Loops можно рассматривать как «страховку» для тех случаев, когда SQL Server выбирает Nested Loops, но лучшим является альтернативный план запроса с полным просмотром или Nested Loops с явной сортировкой. Для приведенного выше запроса, который затрагивает только несколько строк, оптимизация вряд ли окажет какое-либо влияние. Давайте рассмотрим пример, где оптимизация действительно помогает:

SELECT SUM(Data)
FROM T
WHERE RandKey < 100000000 AND
    Flags & 0x1 = 0x1 AND
    Flags & 0x2 = 0x2 AND
    Flags & 0x4 = 0x4 AND
    Flags & 0x8 = 0x8

  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1014]=(0) THEN NULL ELSE [Expr1015] END))
       |--Stream Aggregate(DEFINE:([Expr1014]=COUNT_BIG([T].[Data]), [Expr1015]=SUM([T].[Data])))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([T].[PK], [Expr1013]) OPTIMIZED WITH UNORDERED PREFETCH)
                 |--Index Seek(OBJECT:([T].[IRandKey]), SEEK:([T].[RandKey] < (100000000)),  WHERE:(([T].[Flags]&(1))=(1) AND ([T].[Flags]&(2))=(2) AND ([T].[Flags]&(4))=(4) AND ([T].[Flags]&(8))=(8)) ORDERED FORWARD)

                 |--Clustered Index Seek(OBJECT:([T].[PK__T__...]), SEEK:([T].[PK]=[T].[PK]) LOOKUP ORDERED FORWARD)

Колонка Flags в каждой записи имеет значение 0xFF. Таким образом, каждый из предикатов побитового сравнения AND оценивается как истинный, и запрос вернёт около 2,5 миллионов строк или 10% таблицы. Обычно, для подобных запросов, SQL Server выбрал бы план с последовательным просмотром таблицы. Действительно, если выполнить запрос без дополнительных фильтров побитового сравнения, вы получите последовательный просмотр. Однако SQL Server не знает, что эти предикаты всегда истинны, его оценка получается с более низкой кардинальностью (менее 10000 строк) и выбирает простой план с Nested Loops. Хочу обратить ваше внимание, что я бы вообще не рекомендовал использовать такие предикаты в реальных приложениях именно потому, что они приведут к ошибкам оценки кардинальности и плохим планам.

Чтобы увидеть, какой эффект дает оптимизированный Nested Loops, давайте сравним приведенный выше план с "неоптимизированным" соединением Nested Loops. Мы заставим оптимизатор выбрать план с оптимизацией, выполнив команду UPDATE STATISTICS, которая обманет SQL Server, заставив его поверить, что таблица очень маленькая:

UPDATE STATISTICS T WITH ROWCOUNT = 1, PAGECOUNT = 1

Сравним приведенный выше запрос со следующим ниже более простым запросом, который использует по сути тот же план и затрагивает те же данные, но у него в плане «неоптимизированное» соединение Nested Loops:

SELECT SUM(Data)

FROM T WITH (INDEX (IRandKey))

WHERE RandKey < 100000000

  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1009]=(0) THEN NULL ELSE [Expr1010] END))
       |--Stream Aggregate(DEFINE:([Expr1009]=COUNT_BIG([T].[Data]), [Expr1010]=SUM([T].[Data])))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([T].[PK]))
                 |--Index Seek(OBJECT:([T].[IRandKey]), SEEK:([T].[RandKey] < (100000000)) ORDERED FORWARD)
                 |--Clustered Index Seek(OBJECT:([T].[PK__T__...]), SEEK:([T].[PK]=[T].[PK]) LOOKUP ORDERED FORWARD)

Мы можем «починить» статистику, используя следующую команду:

UPDATE STATISTICS T WITH ROWCOUNT = 25600000, PAGECOUNT = 389323

Как и в предыдущей статье, мы будем моделировать большую таблицу, уменьшив до 1 ГБ объем памяти, доступной экземпляру сервера. Это достигается с помощью SP_CONFIGURE «MAX SERVER MEMORY», и ещё будем очищать буферный пул между запусками с помощью DBCC DROPCLEANBUFFERS.

Обратите внимание: НЕ СТОИТ запускать этот набор операторов на промышленном сервере!

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

		Execution Time		Increase
		OPTIMIZED	"un-OPTIMIZED"
Constant	10,000,000(1% of rows)	6.5 minutes	26 minutes	4x
	100,000,000(10% of rows)	10.4 minutes	4.3 hours	25x
	250,000,000(25% of rows)	11.3 minutes	10.6 hours	56x

Очевидно, что оптимизированное соединение Nested Loops может оказать большое влияние на производительность. Более того, по мере увеличения числа затрагиваемых строк, выгода от оптимизации резко возрастает. Хотя полный просмотр или Nested Loops с сортировкой был бы быстрее, оптимизированное соединение Nested Loops действительно является защитной от худшей альтернативы.