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

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

  • Статья

Применимо к: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 на SQL Server, если эта проблема существовала в исходном 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> сканирование параметра будет полностью отключено.

Совет

Используйте функцию анализа планов Management Studio, чтобы быстро определить, является ли это проблемой. Дополнительные сведения см. в статье "Новые сведения в SSMS: устранение неполадок с производительностью запросов упрощается!".

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

Применяется к: внешней платформе (например, 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 на 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.

Примечание.

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

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

Внимание

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

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

  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. Для более сложных вариантов можно использовать промежуточные результаты, которые хранятся в таблицах, оптимизированных для памяти, или во временных таблицах.

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