Оптимизация запросов SQL Server. Часть II

Содержание:
1. Часть I;
2. Часть II (Вы читаете данный раздел);
3. Часть III;
4. Часть IV;
5. Часть V.

---

Метод оптимизации для неизвестного используется в следующих случаях:
1. При работе с локальными переменными
В отличие от значений параметров, которые можно прослушивать, значения переменных обычно прослушать нельзя. Исключение будет описано немного позже. Причина проста: начальная единица оптимизации — весь пакет, а не только инструкция запроса. Объявление и задание значений переменным выполняются в оптимизируемом пакете. Точка, в которой запрос оптимизируется, предшествует заданию любой переменной, поэтому значения переменных нельзя прослушивать. В результате оптимизатору приходится использовать метод оптимизации для неизвестного.

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

SELECT ProductID, COUNT(*) AS NumOrders

FROM Sales.SalesOrderDetail

WHERE OrderQty >= 40

GROUP BY ProductID;

План выполнения для этого запроса показан на рисунке выше.


Классический инструмент, используемый оптимизатором, чтобы получить СЕ для фильтра, — гистограмма. Если ее не существовало для столбца OrderQty перед выполнением этого запроса и вы не отключили автоматическое создание статистики в базе данных, то SQL Server создает ее при выполнении запроса. Вы можете использовать запрос, приведенный в коде выше, чтобы получить автоматически созданное имя статистики.

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

DBCC SHOW_STATISTICS (N'Sales.SalesOrderDetail', N'_WA_Sys_00000004_44CA3770')

  WITH HISTOGRAM;

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

Мы ясно видим, что CE, показанная на рисунке выше, основана на последних трех шагах гистограммы. Оценка довольно точная: 4,00639 при действительном значении 4.

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

DECLARE @Qty AS INT = 40;

SELECT ProductID, COUNT(*) AS NumOrders

FROM Sales.SalesOrderDetail

WHERE OrderQty >= @Qty

GROUP BY ProductID;

План для этого запроса показан на рисунке выше.

Как было предсказано, это оценка 30% количества элементов ввода. Примечательно, что из-за неточности оценки оптимизатор выбрал неоптимальную стратегию статистической обработки. Здесь использован алгоритм статистической обработки Hash Match вместо сортировки и алгоритма Stream Aggregate. Это лишь одно из многих возможных последствий неточных оценок.

Существует исключение, при котором оптимизатор может прослушивать переменные: событие перекомпиляции происходит на уровне инструкций. Дело в том, что по определению перекомпиляция на уровне инструкций происходит после того, как выполнено задание всех переменных. Автоматическая перекомпиляция всегда происходит на уровне инструкций. Такбыло все время после появления SQL Server 2005 и до написания данной статьи. Я тестирую программный код на SQL Server 2016. Для ручной перекомпиляции на уровне инструкций нужно добавить указание запроса RECOMPILE с использованием оператора OPTION:

DECLARE @Qty AS INT = 40;

SELECT ProductID, COUNT(*) AS NumOrders

FROM Sales.SalesOrderDetail

WHERE OrderQty >= @Qty

GROUP BY ProductID

OPTION (RECOMPILE);

Этот запрос формирует такой же план, как показанный на рисунке «Последние несколько шагов гистограммы», где оценка является точной. Обратите внимание, что если указать параметр WITH RECOMPILE на уровне процедуры, то прослушивание не будет включено — это достигается только указанием в запросе OPTION (RECOMPILE). Перейдем к следующему случаю использования метода оптимизации для неизвестного.