Руководство по оптимизации и проверке после миграции

Область применения: SQL Server

Шаг после миграции SQL Server имеет решающее значение для согласования точности и полноты данных и выявления проблем с производительностью рабочей нагрузки.

Обычные сценарии производительности

Ниже приведены некоторые распространенные сценарии производительности, возникающие после миграции на платформу SQL Server и способы их устранения. К ним относятся сценарии, относящиеся к миграции SQL Server на SQL Server (более ранние версии на более новые версии), а также внешнюю платформу (например, Oracle, DB2, MySQL и Sybase) на миграцию SQL Server.

Регрессии запросов из-за изменения версии оценки кратности (CE)

Область применения:мигарция с SQL Server на SQL Server.

При переходе с более старой версии SQL Server на SQL Server 2014 (12.x) или более поздних версий и обновлении уровня совместимости базы данных до последней доступной рабочей нагрузки может быть подвержена риску регрессии производительности.

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

Дополнительные сведения об изменениях оптимизатора запросов, появившихся в SQL Server 2014 (12.x), см. в документе Оптимизация планов запросов с помощью модуля оценщика количества элементов SQL Server 2014

Дополнительные сведения об CE см. в разделе "Оценка кратности" (SQL Server).

Действия по устранению

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

Дополнительные сведения об этой статье см. в статье "Сохранение стабильности производительности во время обновления до более нового SQL Server".

Чувствительность к чувствительностью к параметров

Применяется к: внешней платформе (например, Oracle, DB2, MySQL и Sybase) к миграции SQL Server.

SQL Server компилирует планы запросов для хранимых процедур, используя перехват параметров входных данных во время первой компиляции и создавая параметризованный план с возможностью повторного использования, оптимизированный для распространения этих параметров. Даже если не хранимые процедуры, большинство инструкций, создающих тривиальные планы, параметризуются. После первого кэширования плана любое выполнение сопоставляется с кэшированным ранее планом.

Потенциальная проблема возникает, когда первая компиляция не использует наиболее распространенные наборы параметров для обычной рабочей нагрузки. С другими параметрами план выполнения будет неэффективным. Дополнительные сведения об этой статье см. в разделе "Конфиденциальность параметров".

Действия по устранению

1. Воспользуйтесь подсказкой RECOMPILE. Для каждого значения параметра план вычисляется заново.
2. Перепишите хранимую процедуру, задействовав параметр (OPTIMIZE FOR(<input parameter> = <value>)). Определите, какое значение соответствует большей части рабочей нагрузки — это позволит создать и использовать единый план, который будет эффективным для параметризованного значения.
3. Перепишите хранимую процедуру, добавив в нее локальную переменную. После этого оптимизатор будет использовать для оценки вектор плотностей, а значит план будет выполняться независимо от значения параметра.
4. Перепишите хранимую процедуру, задействовав параметр (OPTIMIZE FOR UNKNOWN). Результат будет точно таким же, как при использовании локальной переменной.
5. Перепишите запрос, задействовав подсказку DISABLE_PARAMETER_SNIFFING. Результат будет таким же, как при использовании локальной переменной — в отсутствие OPTION(RECOMPILE), WITH RECOMPILE или OPTIMIZE FOR <value> сканирование параметра будет полностью отключено.

Отсутствующие индексы

Применяется к: внешней платформе (например, Oracle, DB2, MySQL и Sybase) и SQL Server к миграции SQL Server.

Неправильные или отсутствующие индексы вызывают дополнительные операции ввода-вывода, что приводит к потере дополнительной памяти и ЦП. Это может быть связано с тем, что профиль рабочей нагрузки изменился, например использование различных предикатов, недопустимое существующее проектирование индекса. Как понять, что стратегия индексации или изменения в профиле рабочей нагрузки неадекватны:

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

Действия по устранению

1. Используйте графический план выполнения для любых отсутствующих ссылок на индексы.
2. Индексирование предложений, созданных помощником по настройке ядра СУБД.
3. Используйте sys.dm_db_missing_index_details или с помощью панели мониторинга производительности SQL Server.
4. Используйте уже существующие скрипты, которые могут использовать существующие динамические административные представления для получения сведений о отсутствующих, повторяющихся, избыточных, редко используемых и полностью неиспользуемых индексах, но также, если ссылка на индекс указывает или жестко закодирована в существующие процедуры и функции в базе данных.

Неспособность использовать предикаты для фильтрации данных

Применяется к: внешней платформе (например, Oracle, DB2, MySQL и Sybase) и SQL Server к миграции SQL Server.

Оптимизатор запросов SQL Server работает только с теми данными, которые известны на момент компиляции. Если рабочая нагрузка выполняется с предикатами, которые могут быть известны только во время выполнения, вероятность неадекватного выбора плана возрастает. Для получения плана оптимального качества требуются предикаты SARGable или Search Argumentable.

Приведем несколько примеров предикатов, отличных от SARGable:

• Неявные преобразования данных, например varchar в nvarchar или int в varchar. Найдите предупреждения среды выполнения CONVERT_IMPLICIT в фактических планах выполнения. Преобразование одного типа в другой также может приводить к потере точности.
• Сложные неопределенные выражения, такие как WHERE UnitPrice + 1 < 3.975, но не WHERE UnitPrice < 320 * 200 * 32.
• Выражения с функциями, такие как WHERE ABS(ProductID) = 771 или WHERE UPPER(LastName) = 'Smith'.
• Строки, которые начинаются с подстановочных знаков, такие как WHERE LastName LIKE '%Smith', но не WHERE LastName LIKE 'Smith%'.

Действия по устранению

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

   Это может включать сравнение любой определяемой пользователем конструкции кода, хранящейся в базе данных (например, хранимых процедур, определяемых пользователем функций или представлений) с системными таблицами, которые содержат сведения о типах данных, используемых в базовых таблицах (например , sys.columns (Transact-SQL)).

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

3. Рассмотрите целесообразность применения следующих конструкций:

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

Использование табличных функций (многофакторная и встроенная)

Применяется к: внешней платформе (например, Oracle, DB2, MySQL и Sybase) и SQL Server к миграции SQL Server.

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

Действия по устранению

1. Если MSTVF является только одним оператором, преобразуйте в встроенную табличную функцию.

   CREATE FUNCTION dbo.tfnGetRecentAddress(@ID int)
   RETURNS @tblAddress TABLE
   ([Address] VARCHAR(60) NOT NULL)
   AS
   BEGIN
     INSERT INTO @tblAddress ([Address])
     SELECT TOP 1 [AddressLine1]
     FROM [Person].[Address]
     WHERE  AddressID = @ID
     ORDER BY [ModifiedDate] DESC
   RETURN
   END
   

   Далее приведен пример встроенного формата.

   CREATE FUNCTION dbo.tfnGetRecentAddress_inline(@ID int)
   RETURNS TABLE
   AS
   RETURN (
     SELECT TOP 1 [AddressLine1] AS [Address]
     FROM [Person].[Address]
     WHERE  AddressID = @ID
     ORDER BY [ModifiedDate] DESC
   )
   

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

Связанный контент

• Рекомендации по хранилищу запросов
• Таблицы, оптимизированные для памяти
• Определяемые пользователем функции
• Табличные переменные и расчетное количество строк — часть 1
• Табличные переменные и расчетное количество строк — часть 2
• Кэширование и повторное использование плана выполнения