Диагностика низкой производительности запросов. Cвертка констант и механизм вычисления выражений во время оценки мощности

Свертываемые выражения

SQL Server использует свертку констант со следующими типами выражений.

• Арифметические выражения, такие как 1+1, 5/3*2, которые содержат только константы.

• Логические выражения, такие как 1=1 и 1>2 AND 3>4, которые содержат только константы.

• Встроенные функции, которые считаются сворачиваемыми SQL Server, включают CAST и CONVERT. Обычно внутренняя функция является свертываемой, если это функция только своих входных данных, а не контекстуальных данных, таких как параметры SET, настройки языка, параметры базы данных, ключи шифрования. Недетерминированные функции не являются свертываемыми. Детерминированные встроенные функции являются свертываемыми за некоторыми исключениями.

Исключение делается для типов больших объектов. Если выходной тип процесса свертки является типом больших объектов (text, image, nvarchar(max), varchar(max) или varbinary(max)), то SQL Server не свертывает такое выражение.

Несворачиваемые выражения

Все остальные типы выражения являются несвертываемыми. В частности, несвертываемыми являются следующие типы выражений.

• Неконстантные выражения, такие как выражение, результат которого зависит от значения столбца.

• Выражения, результат которых зависит от локальной переменной или параметра, такие как @x.

• Недетерминированные функции.

• Пользовательские функции (и языка Transact-SQL, и среды CLR).

• Выражения, результат которых зависит от языковых настроек.

• Выражения, результат которых зависит от параметров SET.

• Выражения, результат которых зависит от параметров конфигурации сервера.

Примеры свертываемых и несвертываемых постоянных выражений

SELECT *
FROM Sales.SalesOrderHeader s JOIN sales.SalesOrderDetail d 
ON s.SalesOrderID = d.SalesOrderID
WHERE TotalDue > 117.00 + 1000.00

Вычисление выражений во время компиляции для оценки мощности

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

Вычисляют во время компиляции следующие встроенные функции и специальные операторы (если их входные данные известны): UPPER, LOWER, RTRIM, DATEPART(только ГГ), GetDate, CAST и CONVERT.

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

• Арифметические операторы: +, -, *, /, унарный -

• Логические операторы: AND, OR, NOT

• Операторы сравнения: <, >, <=, >=, <>, LIKE, IS NULL, IS NOT NULL

Остальные функции или операторы вычисляются оптимизатором во время оценки мощности.

Примеры вычисления выражений во время компиляции

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE MyProc( @d datetime )
AS
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d+1;

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE MyProc2( @d datetime )
AS
BEGIN
DECLARE @d2 datetime
SET @d2 = @d+1
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d2
END;

Рекомендации: использование свертки констант и вычисление выражений во время выполнения для формирования оптимальных планов запросов

Чтобы убедится в том, что формируются оптимальные планы запроса, лучше всего проектировать запросы, хранимые процедуры и пакеты так, чтобы оптимизатор запросов мог точно оценить избирательность условий запроса, основываясь на статистике о распространении данных. Иначе оптимизатор должен использовать при оценке избирательности оценку по умолчанию (как 30 % в предыдущем примере).

Чтобы убедиться в том, что механизм оценки мощности оптимизатора предоставляет хорошие оценки, следует сначала убедиться в том, что параметры SET базы данных AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS находятся в значении ON (установка по умолчанию) или что вручную создана статистика по всем столбцам, упоминаемым в условии запроса. После этого при проектировании условий запроса по возможности выполняйте следующее.

• Избегайте использования локальных переменных в запросах. Вместо них в запросе используйте параметры, литералы или выражения.

• Ограничьте использование внедренных в запрос операторов и функций, содержащих параметр, перечисленными в разделе Compile-Time Expression Evaluation for Cardinality Estimation.

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

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

  • Передайте значение переменной в хранимую процедуру, которая содержит запрос, который необходимо вычислить, и пусть запрос использует параметр процедуры вместо локальной переменной.

  • Постройте строку, которая содержит запрос, частично основанный на значении локальной переменной, и выполните строку с помощью динамического SQL (EXEC или хранимой процедуры sp_executesql).

  • Параметризируйте запрос и выполните его с помощью процедуры sp_executesql, а значение переменной передайте в запрос в качестве параметра запроса.

См. также

Основные понятия

Другие ресурсы