Microsoft уже более года  работает над тем, чтобы сделать showplan единым магазином для анализа эффективности запросов и устранения неполадок (насколько это возможно).

С недавним выпуском SQL Server 2017 CU3 вышло еще больше улучшений showplan. Здесь мы расскажем об одном из этих улучшений, которое может помочь в обнаружении  использования Оптимизатора целевого числа строки и его влиянии на выполнение запроса. Новое свойство оператора EstimateRowsWithoutRowGoal. Это также будет доступно на предстоящем SQL Server 2016 SP2.

Итак, что же такое Row Goal?

Когда Оптимизатор запросов оценивает стоимость плана выполнения, он обычно предполагает, что все квалификационные строки из всех таблиц должны быть обработаны. Однако некоторые шаблоны запросов заставляют Оптимизатор искать план, который будет возвращать меньшее количество строк, с целью сделать это быстрее. Таким образом, Row Goal или целевое число строк  - очень полезная стратегия оптимизации для определенных шаблонов запросов.

Как используется цель строки?

Это может произойти, если запрос указывает целевое количество строк, которые можно ожидать во время выполнения. Когда запрос использует условие TOP, IN или EXISTS, подсказку в запросе FAST или оператор SET ROWCOUNT, это целевое количество строк используется как часть процесса оптимизации запроса. Если применяется план целевого числа строк, оценочное количество строк в плане запроса уменьшается, поскольку Оптимизатор предполагает, что  должно быть обработано меньшее количество строк, чтобы достичь целевого количества.

Когда Row Goal очень низкий и требуется JOIN, Оптимизатор предпочитает объединение вложенных циклов, потому что его первоначальная стоимость (стоимость создания первой строки) относительно невысока. Но другие типы JOIN могут также использоваться, если Row Goal больше:

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

Пример преимуществ цепочки строк

Давайте используем следующий запрос в Adventureworks2016 и рассмотрим результирующий план выполнения выполнения и показатели выполнения:

SELECT TOP (100) *
FROM Sales.SalesOrderHeaderBulk AS s
   INNER JOIN Sales.SalesOrderDetailBulk AS d ON s.SalesOrderID = d.SalesOrderID
WHERE s.TotalDue > 1000
OPTION (RECOMPILE);
GO

Обратите внимание на план вложенных циклов, управляемый нижним Row Goal (100):

Этот запрос выполняется чуть более 1,6 с 40 мс процессорного времени. Давайте запомним это, чтобы использовать позже.

Посмотрев на свойства внешнего ввода для вложенных циклов, обратите внимание, что это сканирование возвратило 1550 строк (Фактическое количество строк) и только прочитало строки 1550, которые удовлетворяют условию отбора магазинного типа (Number of Rows Read). Хорошо!

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

Мы можем видеть эту информацию в новом операторе EstimateRowsWoutoutRowGoal. В этом плане новое свойство можно увидеть в этом Clustered Index Scan, а также в других операторах, таких как Scout Scute.

Это новое свойство добавляется только к оператору плана, если целевое число строк было оценено и использовано - если нет, это свойство отсутствует.

Фактически, мы можем использовать USE HINT, чтобы отключить оптимизацию целевого числа строк, и посмотреть, как это действительно помогло нашей форме плана запроса и параметрам выполнения:

SELECT TOP (100) *
FROM Sales.SalesOrderHeaderBulk AS s
   INNER JOIN Sales.SalesOrderDetailBulk AS d ON s.SalesOrderID = d.SalesOrderID
WHERE s.TotalDue > 1000
OPTION (RECOMPILE, USE HINT('DISABLE_OPTIMIZER_ROWGOAL'));
GO

Понятно, что целевое число строк было преимуществом, так как время выполнения увеличилось до чуть более 2,4 с (на 60% хуже). Row goal - преимущество в большинстве случаев, когда оно используется.

Пример целевого числа строк, которое мы можем улучшить

В большинстве случаев это выгодно, но не всегда понятно. Вот почему  может быть полезна новая информация. Давайте посмотрим пример, используя другой запрос:

SELECT TOP 250 *
FROM Production.TransactionHistory H
INNER JOIN Production.Product P ON  H.ProductID = P.ProductID
OPTION (RECOMPILE)
GO

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

Этот однопользовательский запрос, без сомнений, выполняется очень быстро,. Но давайте представим, что вы видите эти цифры, умноженные на 100, и этот запрос выполняется много раз в минуту.

Ниже в разделе Clustered Index Scan показано, как оцениваемые строки без целевого числа строк (EstimateRowsWithoutRowGoal) намного больше, чем количество прочитанных строк (Number of Rows Read): от 113 тыс. до 250 тыс. Целевое число строк привело к некоторым решениям в Оптимизаторе, которые кажутся полезными, и создало план вложенных циклов с определенным порядком внешних и внутренних таблиц - TransactionHistory и Product соответственно. На внутренней таблице Product, SQL Server выполняет поиск только требуемых 250 запросов, как и ожидалось.

Помня о метриках выше, и о том, что было  использовано целевое число строк, я могу попытаться отключить Row goal и посмотреть, получаю ли я лучшую производительность.

SELECT TOP 250 *

FROM Production.TransactionHistory H

INNER JOIN Production.Product P ON  H.ProductID = P.ProductID

OPTION (RECOMPILE, USE HINT('DISABLE_OPTIMIZER_ROWGOAL'))

GO

В результате план выполнения запроса теперь использует хэш-соединение, и обратите внимание, как изменился порядок входов соединения Product  представляет собой таблицу построения, TransactionHistory - зонд.

Что относительно показателей выполнения? Они стали несомненно лучше.

Таблица построения  Product имеет только 504 строки (см. Ниже), и после ее создания она стала намного дешевле, чем вложенный цикл, который я вижу в этом примере. Несмотря на то, что зондовая таблица TransactionHistory намного больше, теперь нам нужно всего лишь исследовать требуемые 250 строк для нашего условия TOP.

Поэтому в этом случае лучше отключить целевое число строк, что дает 89% -ное улучшение времени выполнения (от 165 мс до 18 мс).

Перед этим улучшением showplan, если вам было поручено анализировать запрос с использованием шаблона, который может использовать целевое число строк, вы могли бы только догадываться о его присутствии. Но с помощью свойства EstimateRowsWithoutRowGoal становится возможным увидеть, действительно ли используется Row Goal, а затем задействовать эти алгоритмы настройки.

No Comments