Руководство по оптимизации и проверке после миграцииPost-migration Validation and Optimization Guide

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server нетБаза данных SQL Azure нетAzure Synapse Analytics (хранилище данных SQL) нетParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Проверка SQL ServerSQL Server после миграции — очень важный шаг, позволяющий добиться точности и полноты данных, а также выявить проблемы с производительностью рабочей нагрузки.SQL ServerSQL Server post migration step is very crucial for reconciling any data accuracy and completeness, as well as uncover performance issues with the workload.

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

Ниже представлены некоторые типовые сценарии производительности, которые встречаются после миграции на платформу SQL ServerSQL Server, а также способы устранения связанных с ними проблем.Below are some of the common performance scenarios encountered after migrating to SQL ServerSQL Server Platform and how to resolve them. К ним относятся сценарии, связанные с миграцией с SQL ServerSQL Server на SQL ServerSQL Server (переход на более новые версии), а также с миграцией с внешней платформы (например, Oracle, DB2, MySQL и Sybase) на SQL ServerSQL Server.These include scenarios that are specific to SQL ServerSQL Server to SQL ServerSQL Server migration (older versions to newer versions), as well as foreign platform (such as Oracle, DB2, MySQL and Sybase) to SQL ServerSQL Server migration.

Замедление запросов из-за изменения в версии CEQuery regressions due to change in CE version

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

При миграции со старых версий SQL ServerSQL Server на SQL Server 2014 (12.x)SQL Server 2014 (12.x) или более новые версии и при обновлении уровня совместимости базы данных до последнего доступного выполнение рабочей нагрузки может замедляться.When migrating from an older version of SQL ServerSQL Server to SQL Server 2014 (12.x)SQL Server 2014 (12.x) or newer, and upgrading the database compatibility level to the latest available, a workload may be exposed to the risk of performance regression.

Это объясняется тем, что, начиная с SQL Server 2014 (12.x)SQL Server 2014 (12.x), все изменения в оптимизаторе запросов привязаны к последнему уровню совместимости базы данных, поэтому планы изменяются не в момент обновления, а когда пользователь изменяет параметр базы данных COMPATIBILITY_LEVEL на последнюю версию.This is because starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x), all Query Optimizer changes are tied to the latest database compatibility level, so plans are not changed right at point of upgrade but rather when a user changes the COMPATIBILITY_LEVEL database option to the latest one. В сочетании с хранилищем запросов эта возможность обеспечивает высокий уровень контроля над производительностью запросов в процессе обновления.This capability, in combination with Query Store gives you a great level of control over the query performance in the upgrade process.

Дополнительные сведения об изменениях оптимизатора запросов, появившихся в SQL Server 2014 (12.x)SQL Server 2014 (12.x), см. в документе Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator (Оптимизация планов запросов с помощью модуля оценки кратности SQL Server 2014).For more information on Query Optimizer changes introduced in SQL Server 2014 (12.x)SQL Server 2014 (12.x), see Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator.

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

Измените уровень совместимости базы данных на исходную версию и следуйте рекомендуемому рабочему процессу обновления, показанному на следующем рисунке:Change the database compatibility level to the source version, and follow the recommended upgrade workflow as shown in the following picture:

хранилище запросов-использование-5query-store-usage-5

Дополнительные сведения по этой теме см. в разделе Поддержание стабильной производительности во время обновления до более новой версии SQL Server.For more information on this topic, see Keep performance stability during the upgrade to newer SQL Server.

Чувствительность к пробному сохранению параметровSensitivity to parameter sniffing

Применимо к: внешняя платформа (например, Oracle, DB2, MySQL или Sybase) для миграции SQL ServerSQL Server.Applies to: Foreign platform (such as Oracle, DB2, MySQL and Sybase) to SQL ServerSQL Server migration.

Примечание

Если при миграции с SQL ServerSQL Server на SQL ServerSQL Server подобная проблема возникнет в источнике SQL ServerSQL Server, миграция на новую версию SQL ServerSQL Server без изменений будет проходить без учета этого сценария.For SQL ServerSQL Server to SQL ServerSQL Server migrations, if this issue existed in the source SQL ServerSQL Server, migrating to a newer version of SQL ServerSQL Server as-is will not address this scenario.

SQL ServerSQL Server компилирует планы запросов для хранимых процедур, используя перехват входных параметров во время первой компиляции и создавая параметризованный план с возможностью повторного использования, оптимизированный для распространения этих параметров.compiles query plans on stored procedures by using sniffing the input parameters at the first compile, generating a parameterized and reusable plan, optimized for that input data distribution. Даже если хранимых процедур нет, большинство операторов, создающий простые планы, будет параметризовано.Even if not stored procedures, most statements generating trivial plans will be parameterized. После первого кэширования плана любое выполнение сопоставляется с кэшированным ранее планом.After a plan is first cached, any future execution maps to a previously cached plan. Проблемы могут возникнуть, если в первой компиляции не использовались наборы параметров, наиболее типичные для обычной рабочей нагрузки.A potential problem arises when that first compilation may not have used the most common sets of parameters for the usual workload. С другими параметрами план выполнения будет неэффективным.For different parameters, the same execution plan becomes inefficient. Дополнительные сведения по этой теме см. в разделе Сканирование параметров.For more information on this topic, see Parameter Sniffing.

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

  1. Воспользуйтесь подсказкой RECOMPILE.Use the RECOMPILE hint. Для каждого значения параметра план вычисляется заново.A plan is calculated every time adapted to each parameter value.
  2. Перепишите хранимую процедуру, задействовав параметр (OPTIMIZE FOR(<input parameter> = <value>)).Rewrite the stored procedure to use the option (OPTIMIZE FOR(<input parameter> = <value>)). Определите, какое значение соответствует большей части рабочей нагрузки — это позволит создать и использовать единый план, который будет эффективным для параметризованного значения.Decide which value to use that suits most of the relevant workload, creating and maintaining one plan that becomes efficient for the parameterized value.
  3. Перепишите хранимую процедуру, добавив в нее локальную переменную.Rewrite the stored procedure using local variable inside the procedure. После этого оптимизатор будет использовать для оценки вектор плотностей, а значит план будет выполняться независимо от значения параметра.Now the optimizer uses the density vector for estimations, resulting in the same plan regardless of the parameter value.
  4. Перепишите хранимую процедуру, задействовав параметр (OPTIMIZE FOR UNKNOWN).Rewrite the stored procedure to use the option (OPTIMIZE FOR UNKNOWN). Результат будет точно таким же, как при использовании локальной переменной.Same effect as using the local variable technique.
  5. Перепишите запрос, задействовав подсказку DISABLE_PARAMETER_SNIFFING.Rewrite the query to use the hint DISABLE_PARAMETER_SNIFFING. Результат будет таким же, как при использовании локальной переменной — в отсутствие OPTION(RECOMPILE), WITH RECOMPILE или OPTIMIZE FOR <value> сканирование параметра будет полностью отключено.Same effect as using the local variable technique by totally disabling parameter sniffing, unless OPTION(RECOMPILE), WITH RECOMPILE or OPTIMIZE FOR <value> is used.

Совет

Воспользуйтесь функцией анализа плана Среда Management StudioManagement Studio, чтобы быстро определить наличие проблемы.Leverage the Среда Management StudioManagement Studio Plan Analysis feature to quickly identify if this is an issue. Дополнительные сведения см. здесь.More information available here.

Отсутствие индексовMissing indexes

Применимо к: внешняя платформа (например, Oracle, DB2, MySQL или Sybase) для миграции с SQL ServerSQL Server на SQL ServerSQL Server.Applies to: Foreign platform (such as Oracle, DB2, MySQL and Sybase) and SQL ServerSQL Server to SQL ServerSQL Server migration.

Неправильные индексы или их отсутствие создают дополнительную нагрузку по вводу-выводу, а значит и лишний расход ресурсов памяти и процессора.Incorrect or missing indexes causes extra I/O that leads to extra memory and CPU being wasted. Это может быть связано с изменением профиля рабочей нагрузки, например применением других предикатов, нарушающим существующую структуру индекса.This maybe because workload profile has changed such as using different predicates, invalidating existing index design. Как понять, что стратегия индексации или изменения в профиле рабочей нагрузки неадекватны:Evidence of a poor indexing strategy or changes in workload profile include:

  • обращайте внимание на повторяющиеся, избыточные, редко применяемые и абсолютно неиспользуемые индексы;Look for duplicate, redundant, rarely used and completely unused indexes.
  • проявляйте особое внимание к неиспользуемым индексам с обновлениями.Special care with unused indexes with updates.

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

  1. Использование графического плана выполнения для отсутствующих ссылок на индексы.Leverage the graphical execution plan for any Missing Index references.
  2. Индексирование предложений, созданных помощником по настройке ядра СУБД.Indexing suggestions generated by Database Engine Tuning Advisor.
  3. Использование динамического административного представления отсутствующих индексов или панели мониторинга производительности SQL Server.Leverage the Missing Indexes DMV or through the SQL Server Performance Dashboard.
  4. Использование существующих сценариев, которые могут обращаться к существующим динамическим административным представлениям для получения представления об отсутствующих, повторяющихся, избыточных, редко применяемых и абсолютно неиспользуемых индексах, а также в случае, если какая-либо ссылка на индекс указана в подсказке или прописана в коде процедур или функций, существующих в вашей базе данных.Leverage pre-existing scripts that can use existing DMVs to provide insight into any missing, duplicate, redundant, rarely used and completely unused indexes, but also if any index reference is hinted/hard-coded into existing procedures and functions in your database.

Совет

В качестве примеров таких существующих скриптов можно привести создание индекса и сведения об индексе.Examples of such pre-existing scripts include Index Creation and Index Information.

Невозможность использовать предикаты для фильтрации данных Inability to use predicates to filter data

Применимо к: внешняя платформа (например, Oracle, DB2, MySQL или Sybase) для миграции с SQL ServerSQL Server на SQL ServerSQL Server.Applies to: Foreign platform (such as Oracle, DB2, MySQL and Sybase) and SQL ServerSQL Server to SQL ServerSQL Server migration.

Примечание

Если при миграции с SQL ServerSQL Server на SQL ServerSQL Server подобная проблема возникнет в источнике SQL ServerSQL Server, миграция на новую версию SQL ServerSQL Server без изменений будет проходить без учета этого сценария.For SQL ServerSQL Server to SQL ServerSQL Server migrations, if this issue existed in the source SQL ServerSQL Server, migrating to a newer version of SQL ServerSQL Server as-is will not address this scenario.

Оптимизатор запросов SQL ServerSQL Server работает только с теми данными, которые известны на момент компиляции.SQL ServerSQL Server Query Optimizer can only account for information that is known at compile time. Если рабочая нагрузка выполняется с предикатами, которые могут быть известны только во время выполнения, вероятность неадекватного выбора плана возрастает.If a workload relies on predicates that can only be known at execution time, then the potential for a poor plan choice increases. Для получения плана оптимального качества требуются предикаты SARGable или Search Argumentable.For a better-quality plan, predicates must be SARGable, or Search Argumentable.

Приведем несколько примеров предикатов, отличных от SARGable:Some examples of non-SARGable predicates:

  • Неявные преобразования данных, например VARCHAR в NVARCHAR или INT в VARCHAR.Implicit data conversions, like VARCHAR to NVARCHAR, or INT to VARCHAR. Ищите предупреждения CONVERT_IMPLICIT в фактических планах выполнения в среде выполнения.Look for runtime CONVERT_IMPLICIT warnings in the Actual Execution Plans. Преобразование одного типа в другой также может приводить к потере точности.Converting from one type to another can also cause a loss of precision.
  • Сложные неопределенные выражения, такие как WHERE UnitPrice + 1 < 3.975, но не WHERE UnitPrice < 320 * 200 * 32.Complex undetermined expressions such as WHERE UnitPrice + 1 < 3.975, but not WHERE UnitPrice < 320 * 200 * 32.
  • Выражения с функциями, такие как WHERE ABS(ProductID) = 771 или WHERE UPPER(LastName) = 'Smith'.Expressions using functions, such as WHERE ABS(ProductID) = 771 or WHERE UPPER(LastName) = 'Smith'
  • Строки, которые начинаются с подстановочных знаков, такие как WHERE LastName LIKE '%Smith', но не WHERE LastName LIKE 'Smith%'.Strings with a leading wildcard character, such as WHERE LastName LIKE '%Smith', but not WHERE LastName LIKE 'Smith%'.

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

  1. Всегда объявляйте переменные или параметры как намеченный целевой тип данных.Always declare variables/parameters as the intended target data type.
  • Для этого может потребоваться сравнение конструкции пользовательского кода, хранящийся в базе данных (например, хранимых процедур, определяемых пользователем функций или представлений), с системными таблицами, которые содержат сведения о типах данных, используемых в базовых таблицах (таких как sys.columns).This may involve comparing any user-defined code construct that is stored in the database (such as stored procedures, user-defined functions or views) with system tables that hold information on data types used in underlying tables (such as sys.columns).
  1. Если перебрать весь код до указанной выше точки нельзя, то с той же целью можно изменить тип данных в таблице в соответствии с объявлением переменной или параметра.If unable to traverse all code to the previous point, then for the same purpose, change the data type on the table to match any variable/parameter declaration.
  2. Рассмотрите целесообразность применения следующих конструкций:Reason out the usefulness of the following constructs:
  • функции, используемые в качестве предикатов;Functions being used as predicates;
  • поиск с подстановочными знаками;Wildcard searches;
  • сложные выражения на основе данных, расположенных в один столбец (подумайте, не стоит ли вместо них создать материализованные вычисляемые столбцы, которые можно проиндексировать).Complex expressions based on columnar data - evaluate the need to instead create persisted computed columns, which can be indexed;

Примечание

Все это можно сделать программным способом.All of the above can be done programmatically.

Применение функций, возвращающих табличные значения (многооператорные и встроенные функции)Use of Table Valued Functions (Multi-Statement vs Inline)

Применимо к: внешняя платформа (например, Oracle, DB2, MySQL или Sybase) для миграции с SQL ServerSQL Server на SQL ServerSQL Server.Applies to: Foreign platform (such as Oracle, DB2, MySQL and Sybase) and SQL ServerSQL Server to SQL ServerSQL Server migration.

Примечание

Если при миграции с SQL ServerSQL Server на SQL ServerSQL Server подобная проблема возникнет в источнике SQL ServerSQL Server, миграция на новую версию SQL ServerSQL Server без изменений будет проходить без учета этого сценария.For SQL ServerSQL Server to SQL ServerSQL Server migrations, if this issue existed in the source SQL ServerSQL Server, migrating to a newer version of SQL ServerSQL Server as-is will not address this scenario.

Функции, возвращающие табличные значения, возвращают табличные данные, которые можно просматривать в различных представлениях.Table Valued Functions return a table data type that can be an alternative to views. В то время как представления ограничены одной инструкцией SELECT, пользовательские функции могут содержать дополнительные инструкции, обеспечивающие более обширную логику, чем та, которая возможна в представлениях.While views are limited to a single SELECT statement, user-defined functions can contain additional statements that allow more logic than is possible in views.

Важно!

Поскольку во время компиляции таблица результатов многооператорных функций, возвращающих табличные значения, не создается, оптимизатор запросов SQL ServerSQL Server при подсчете строк обращается к эвристическим правилам, а не к фактической статистике.Since the output table of an MSTVF (Multi-Statement Table Valued Function) is not created at compile time, the SQL ServerSQL Server Query Optimizer relies on heuristics, and not actual statistics, to determine row estimations. Не помогает даже добавление индексов в базовую таблицу (или таблицы).Even if indexes are added to the base table(s), this is not going to help. Для многооператорных функций, возвращающих табличные значения, SQL ServerSQL Server использует в качестве количества строк, которое должна возвращать такая функция, фиксированное значение 1 (начиная с SQL Server 2014 (12.x)SQL Server 2014 (12.x), фиксированное значение составляет 100 строк).For MSTVFs, SQL ServerSQL Server uses a fixed estimation of 1 for the number of rows expected to be returned by an MSTVF (starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x) that fixed estimation is 100 rows).

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

  1. Если многооператорной функцией, возвращающей табличное значение, является только одна инструкция, преобразуйте ее во встроенную функцию.If the Multi-Statement TVF is single statement only, convert to Inline TVF.

    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
    

    Далее приведен пример встроенного формата.The inline format example is displayed next.

    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. Для более сложных вариантов можно использовать промежуточные результаты, которые хранятся в таблицах, оптимизированных для памяти, или во временных таблицах.If more complex, consider using intermediate results stored in Memory-Optimized tables or temporary tables.

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

Рекомендации по хранилищу запросовBest Practice with the Query Store
Таблицы, оптимизированные для памятиMemory-Optimized Tables
Определяемые пользователем функцииUser-Defined Functions
Табличные переменные и расчетное количество строк — часть 1Table Variables and Row Estimations - Part 1
Табличные переменные и расчетное количество строк — часть 2Table Variables and Row Estimations - Part 2
Кэширование и повторное использование плана выполненияExecution Plan Caching and Reuse