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

Статья
05/23/2023

Применимо к:SQL Server

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

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

Ниже представлены некоторые распространенные сценарии производительности, которые встречаются после миграции на платформу SQL Server, а также способы устранения связанных с ними проблем. К ним относятся сценарии, связанные с миграцией c 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

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

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

Diagram showing the recommended upgrade workflow.

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

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

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

Заметка

В случае миграции с SQL Server на SQL Server, если эта проблема существовала в исходном SQL Server, миграция на более новую версию "как есть" будет проходить без учета этого сценария.

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

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

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

Совет

Воспользуйтесь функцией анализа плана Management Studio, чтобы быстро определить наличие проблемы. Дополнительные сведения см. здесь.

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

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

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

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

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

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

Совет

В качестве примеров таких существующих скриптов можно привести создание индекса и сведения об индексе.

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

Область применения: миграция с внешней платформы (например, 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%'.

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

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

Для этого может потребоваться сравнение конструкции пользовательского кода, хранящийся в базе данных (например, хранимых процедур, определяемых пользователем функций или представлений), с системными таблицами, которые содержат сведения о типах данных, используемых в базовых таблицах (таких как sys.columns).

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

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

Заметка

Все это можно сделать программным способом.

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

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

Заметка

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

Внимание

Поскольку во время компиляции таблица результатов MSTVF (функции с табличным значением с несколькими инструкциями), не создается, оптимизатор запросов SQL Server использует эвристику, а не фактическую статистику, для оценки количества строк. Не помогает даже добавление индексов в базовую таблицу (или таблицы). Для функций MSTVF SQL Server в качестве количества строк, которое должна возвращать такая функция, использует фиксированное значение 1 (начиная с SQL Server 2014 (12.x) фиксированное значение составляет 100 строк).

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

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

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
)

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

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

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

Регрессии запросов из-за изменения в версии CE

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

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

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

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

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

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

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

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

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

Дополнительные материалы

Обратная связь

Обратная связь

Дополнительные ресурсы