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

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

---

Помните, что плотность — средний процент для отдельного значения в столбце. Величина рассчитывается как 1/. В столбце OrderQty 41 отдельное значение, поэтому 1/41 = 0,02439. Если применить этот процент к числу строк в таблице, то полученное значение будет очень близким к оценке на рисунке выше. Чтобы увидеть информацию о плотности, используемую SQL Server при выполнении следующего кода (с использованием имени статистики, полученного из запроса, приведенного в коде, который представлен ниже):

DBCC SHOW_STATISTICS (N'Sales.SalesOrderDetail', N'_WA_Sys_00000004_44CA3770')
  WITH DENSITY_VECTOR;

был получен следующий вывод:
All density   Average Length Columns
------------- -------------- ----------
0.02439024    2              OrderQty

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

При использовании неуникального столбца и недоступной плотности в старой и новой CE применяются различные методы. В старой CE используется оценка С^0,75 (степень три четвертых), где С — входное число элементов, а в новой используется оценка СЛ0,5 (квадратный корень).

Чтобы продемонстрировать это, сначала удалите любую статистику для столбца OrderQty и отключите автоматическое создание статистики, как было показано ранее:

ALTER DATABASE AdventureWorks2014 SET AUTO_CREATE_STATISTICS OFF;

GO

DROP STATISTICS Sales.SalesOrderDetail._WA_Sys_00000004_44CA3770;

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

DECLARE @Qty AS INT = 1;

SELECT ProductID, COUNT(*) AS NumOrders

FROM Sales.SalesOrderDetail

WHERE OrderQty = @Qty

GROUP BY ProductID

OPTION(QUERYTRACEON 9481);

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

Оценка 6500,42 — результат вычисления 121317^3/4.

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

DECLARE @Qty AS INT = 1;

SELECT ProductID, COUNT(*) AS NumOrders

FROM Sales.SalesOrderDetail

WHERE OrderQty = @Qty

GROUP BY ProductID;

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

Оценка 348,306 получена в результате вычисления 121317^0,5.

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

ALTER DATABASE AdventureWorks2014 SET AUTO_CREATE_STATISTICS ON;

Таким образом, метод оптимизации для неизвестного используется оптимизатором SQL Server, чтобы создать оценку СЕ при неизвестных входных данных или недостатке статистики.

Иногда у оптимизатора нет иного выбора, кроме использования этого метода просто из-за нехватки информации. Иногда данный метод применяется принудительно, если метод оптимизации для известного не подходит. Итак, метод оптимизации для неизвестного применяется в следующих случаях:
1. Использование переменных (кроме случаев использования RECOMPILE на уровне инструкций).
2. Использование параметров с указанием OPTIMIZE FOR UNKNOWN или OPTIMIZE FOR (@parameter UNKNOWN) или флагом трассировки 4136 (всегда при использовании хранимой процедуры, скомпилированной в собственном коде).
3. Статистика недоступна.


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