Оптимизация запросов SQL Server. Часть III
Содержание:
1. Часть I;
2. Часть II;
3.Часть III (Вы читаете данный раздел);
4. Часть IV;
5. Часть V.
2. При использовании параметров, но отключенном автоматическом прослушивании параметров с указанием OPTIMIZE FOR UNKNOWN или OPTIMIZE FOR (@parameter UNKNOWN) или с флагом трассировки 4136
Обычно значения параметров доступны для прослушивания, так как они задаются при выполнении процедуры или функции, прежде чем пакет передается оптимизатору. Однако можно применить метод оптимизации для неизвестного с двумя указаниями запроса. Если нужно отменить прослушивание параметров для всех входов, используйте указание OPTIMIZE FOR UNKNOWN. Если требуется отменить прослушивание для определенного параметра, используйте указание OPTIMIZE FOR (@parameter UNKNOWN). Также можно использовать флаг трассировки 4136 для отключения прослушивания параметров при разных детализациях: запроса, сеанса или глобальной детализации. Обратите внимание, что при использовании хранимой процедуры, скомпилированной в собственном коде, оптимизация для неизвестного выбирается по умолчанию.
В качестве примера следующий программный код создает хранимую процедуру и отключает прослушивание параметров в запросе с использованием указания OPTIMIZE FOR UNKNOWN:
Используйте следующий программный код для тестирования хранимой процедуры:
План запроса при использовании оператора >= с переменной
Я получил такой же план запроса, как показанный на рисунке выше, с оценкой 30%.
Рассмотрим еще один сценарий, в котором используется метод оптимизации для неизвестного.
3. Статистика недоступна
Идентификация автоматически созданной статистики для столбца OrderQty
Возьмем случай, когда гистограмма для фильтруемого столбца отсутствует и вы не позволяете SQL Server создать гистограмму, отключив автоматическое создание статистики на уровне базы данных и не формируя индекс для столбца. Используйте следующий программный код, чтобы организовать такую среду для нашей демонстрации, заменив имя статистики именем, полученным в результате выполнения запроса, приведенного в коде выше:
Затем выполните код, в котором используется константа в фильтре:
План запроса для запроса с константой
Выполнив этот запрос ранее, вы получили план, показанный на рисунке выше, с точной оценкой. Но на этот раз у оптимизатора не было гистограммы, поэтому используется метод оптимизации для неизвестного и создается план, показанный на рисунке «План запроса при использовании оператора >= с переменной», с оценкой 30%.
Выполните следующий программный код, чтобы повторно активировать автоматическое создание статистики в базе данных:
Вы можете повторно запустить запрос и убедиться, что вы получаете план, как на рисунке выше.
1. Часть I;
2. Часть II;
3.
4. Часть IV;
5. Часть V.
2. При использовании параметров, но отключенном автоматическом прослушивании параметров с указанием OPTIMIZE FOR UNKNOWN или OPTIMIZE FOR (@parameter UNKNOWN) или с флагом трассировки 4136
Обычно значения параметров доступны для прослушивания, так как они задаются при выполнении процедуры или функции, прежде чем пакет передается оптимизатору. Однако можно применить метод оптимизации для неизвестного с двумя указаниями запроса. Если нужно отменить прослушивание параметров для всех входов, используйте указание OPTIMIZE FOR UNKNOWN. Если требуется отменить прослушивание для определенного параметра, используйте указание OPTIMIZE FOR (@parameter UNKNOWN). Также можно использовать флаг трассировки 4136 для отключения прослушивания параметров при разных детализациях: запроса, сеанса или глобальной детализации. Обратите внимание, что при использовании хранимой процедуры, скомпилированной в собственном коде, оптимизация для неизвестного выбирается по умолчанию.
В качестве примера следующий программный код создает хранимую процедуру и отключает прослушивание параметров в запросе с использованием указания OPTIMIZE FOR UNKNOWN:
IF OBJECT_ID(N'dbo.Proc1', N'P') IS NOT NULL DROP PROC dbo.Proc1;
GO
CREATE PROC dbo.Proc1
@Qty AS INT
AS
SELECT ProductID, COUNT(*) AS NumOrders
FROM Sales.SalesOrderDetail
WHERE OrderQty >= @Qty
GROUP BY ProductID
OPTION (OPTIMIZE FOR UNKNOWN);
GOИспользуйте следующий программный код для тестирования хранимой процедуры:
EXEC dbo.Proc1 @Qty = 40;
План запроса при использовании оператора >= с переменной
Я получил такой же план запроса, как показанный на рисунке выше, с оценкой 30%.
Рассмотрим еще один сценарий, в котором используется метод оптимизации для неизвестного.
3. Статистика недоступна
Идентификация автоматически созданной статистики для столбца OrderQty
Возьмем случай, когда гистограмма для фильтруемого столбца отсутствует и вы не позволяете SQL Server создать гистограмму, отключив автоматическое создание статистики на уровне базы данных и не формируя индекс для столбца. Используйте следующий программный код, чтобы организовать такую среду для нашей демонстрации, заменив имя статистики именем, полученным в результате выполнения запроса, приведенного в коде выше:
ALTER DATABASE AdventureWorks2014 SET AUTO_CREATE_STATISTICS OFF;
GO
DROP STATISTICS Sales.SalesOrderDetail._WA_Sys_00000004_44CA3770;Затем выполните код, в котором используется константа в фильтре:
SELECT ProductID, COUNT(*) AS NumOrders
FROM Sales.SalesOrderDetail
WHERE OrderQty >= 40
GROUP BY ProductID;
План запроса для запроса с константой
Выполнив этот запрос ранее, вы получили план, показанный на рисунке выше, с точной оценкой. Но на этот раз у оптимизатора не было гистограммы, поэтому используется метод оптимизации для неизвестного и создается план, показанный на рисунке «План запроса при использовании оператора >= с переменной», с оценкой 30%.
Выполните следующий программный код, чтобы повторно активировать автоматическое создание статистики в базе данных:
ALTER DATABASE AdventureWorks2014 SET AUTO_CREATE_STATISTICS ON;Вы можете повторно запустить запрос и убедиться, что вы получаете план, как на рисунке выше.
