Поделиться через

  • Статья

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

SQL Server предварительно вычисляет некоторые постоянные выражения для улучшения производительности запросов. Это называет сверткой констант. Константа — это литерал языка Transact-SQL, такой как 3, 'ABC', '2005-12-31', 1.0e3 или 0x12345678.

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

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

Если параметр базы данных PARAMETERIZATION не установлен для этого запроса в FORCED (по умолчанию устанавливается SIMPLE), то выражение 117.00 + 1000.00 вычисляется и заменяется его результатом 1117.00 перед компиляцией запроса. Такая свертка констант имеет следующие преимущества.

  • Нет необходимости вычислять выражение несколько раз во время выполнения.

  • Значение выражения после его вычисления используется оптимизатором запросов для оценки размера результирующего набора части запроса TotalDue > 117.00 + 1000.00.

С другой стороны, если dbo.f является скалярной, определяемой пользователем функцией, выражение dbo.f(100) не свертывается, так как SQL Server не свертывает выражения, которые включают определяемые пользователем функции, даже если они детерминированы.

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

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

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

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

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

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

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

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

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

Рассмотрим следующую хранимую процедуру языка Transact-SQL:

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

Во время оптимизации инструкции SELECT в процедуре оптимизатор пытается вычислить ожидаемую мощность результирующего набора для условия OrderDate > @d+1. Выражение @d+1 не свертывается, так как @d является параметром. Однако во время оптимизации значение этого параметра известно. Это дает возможность оптимизатору точно оценить размер результирующего набора, что поможет выбрать наилучший план запроса.

Теперь рассмотрим пример, похожий на предыдущий, за исключением того, что локальная переменная @d2 заменена в запросе выражением @d+1 и это выражение вычисляется в инструкции SET вместо вычисления в запросе.

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;

Когда инструкция SELECT в процедуре MyProc2 оптимизируется в SQL Server, значение переменной @d2 неизвестно. Поэтому оптимизатор использует оценку по умолчанию для избирательности значений OrderDate > @d2 (в данном случае 30 %).

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

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

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

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

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

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

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

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

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

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