Снижение производительности запросов после обновления с SQL Server 2012 или более ранней версии до 2014 или более поздней версии

После обновления SQL Server с 2012 или более ранней до версии 2014 или более поздней версии может возникнуть следующая проблема: большинство исходных запросов выполняются хорошо, но некоторые из ваших запросов выполняются медленнее, чем в предыдущей версии. Хотя существует множество возможных причин и факторов, одна относительно распространенная причина заключается в изменениях в модели оценки кратности (CE) после обновления. Значительные изменения были введены в модели CE, начиная с SQL Server 2014.

В этой статье приведены инструкции по устранению неполадок и устранения проблем с производительностью запросов, возникающих при использовании CE по умолчанию, но не возникают при использовании устаревшей ce.

Примечание.

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

Устранение неполадок. Определите, являются ли изменения CE проблемой и узнайте причину

Шаг 1. Определение использования CE по умолчанию

1. Выберите запрос, который выполняется медленнее после обновления.
2. Запустите запрос и соберите план выполнения.
3. В окно свойств плана выполнения проверьте кратностьEstimationModelVersion.
4. Значение 70 указывает на устаревшую CE, а значение 120 или выше указывает на использование CE по умолчанию.

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

Шаг 2. Определение того, может ли оптимизатор запросов создать лучший план с помощью устаревшей CE

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

Шаг 3. Узнайте, почему запрос работает лучше с устаревшим CE

Проверьте различные подсказки запросов, связанных с CE, для запроса. Для SQL Server 2014 используйте соответствующие флаги трассировки 4137, 9472 и 4139 для тестирования запроса. Определите, какие подсказки или флаги трассировки положительно влияют на производительность на основе этих тестов.

Решение

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

• Оптимизация запроса.

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

• Используйте указания запросов, определенные на шаге 3.

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

• Заставить хороший план.

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

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

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

• Используйте флаг трассировки 9841, чтобы принудительно использовать устаревший CE глобально. Для этого используйте DBCC TRACEON или задайте флаг трассировки в качестве параметра запуска.

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

Параметры включения устаревшего CE

Уровень запроса. Использование параметра "Подсказка запроса" или "QUERYTRACEON"

• Для SQL Server 2016 с пакетом обновления 1 (SP1) и более поздних версий используйте подсказку FORCE_LEGACY_CARDINALITY_ESTIMATION для запроса, например:

  SELECT * FROM Table1
  WHERE Col1 = 10
  OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
  

• Включите флаг трассировки 9481 для принудительного применения устаревшего плана CE. Приведем пример:

  SELECT * FROM Table1
  WHERE Col1 = 10
  OPTION (QUERYTRACEON 9481)
  

Уровень базы данных: настройка или уровень совместимости с областью действия

• Для SQL Server 2016 и более поздних версий измените конфигурацию базы данных:

    --Force a specific database to use legacy CE
    ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
  
    -- Validate what databases use legacy CE
    SELECT name, value
        FROM sys.database_scoped_configurations 
    WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';
  

• Измените уровень совместимости для базы данных. Это единственный вариант уровня базы данных, доступный для SQL Server 2014. Обратите внимание, что это изменение влияет не только на CE. Чтобы определить влияние изменений уровня совместимости, перейдите на уровень совместимости ALTER DATABASE (Transact-SQL) и изучите таблицы "Отличия" в нем.

  ALTER DATABASE <YourDatabase>
  SET COMPATIBILITY_LEVEL = 110  -- set it to SQL Server 2012 level
  

Примечание.

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

Уровень сервера: использование флага трассировки

Используйте флаг трассировки 9481, чтобы принудительно использовать устаревший CE на уровне сервера:

--Turn on 
DBCC TRACEON(9481, -1)
--Validate
DBCC TRACESTATUS

Примечание.

Это изменение повлияет на все запросы, выполняемые в контексте экземпляра SQL Server, если не используется переопределяющий флаг трассировки или указание запроса. Запросы, которые выполняются лучше из-за стандартного CE, могут регрессии.

Часто задаваемые вопросы

Вопрос 1. Я заинтересован в обновлении до более последней версии SQL Server, и я обеспокоен регрессией производительности оценки производительности. Какое планирование обновления рекомендуется использовать для минимизации проблем?

Для существующих баз данных, работающих на более низких уровнях совместимости, рекомендуемый рабочий процесс для обновления обработчика запросов до более высокого уровня совместимости подробно описан в разделе "Изменение режима совместимости базы данных" и использование хранилище запросов и сценариев использования хранилище запросов. Методология, представленная в статье, применяется к переходу к 130 или более поздней версии для SQL Server и База данных SQL Azure.

Вопрос 2. У меня нет времени на тестирование изменений CE. Что я могу сделать в этом случае?

Для существующих приложений и рабочих нагрузок мы не рекомендуем переходить к CE по умолчанию, пока не будет выполнено достаточное тестирование регрессии. Если у вас по-прежнему есть сомнения, рекомендуется обновить SQL Server и перейти к последнему доступному уровню совместимости. В качестве меры предосторожности также включите флаг трассировки 9481 для SQL Server 2014 или настройте конфигурацию ON LEGACY_CARDINALITY_ESTIMATION базы данных с областью действия для SQL Server 2016 и более поздних версий, пока вы не сможете протестировать.

Вопрос 3. Есть ли какие-либо недостатки использования устаревшего CE навсегда?

Будущие улучшения оценки кратности и исправления, связанные с кратностью, сосредоточены на более поздних версиях. Версия 70 является приемлемым промежуточным состоянием. Однако после тщательного тестирования мы рекомендуем в конечном итоге перейти на более новую версию CE, чтобы воспользоваться самыми последними исправлениями CE. Существует высокая вероятность изменений плана запросов при переходе из устаревшей среды CE, поэтому перед внесением изменений в рабочие системы выполняется проверка. Изменения могут повысить производительность запросов во многих случаях, но в некоторых случаях производительность запросов может снизиться.

Внимание

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

Вопрос 4. У меня есть тысячи баз данных и не требуется вручную включить LEGACY_CARDINALITY_ESTIMATION для каждого. Существует ли альтернативный метод?

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

SELECT [name], 0 AS [isdone]
INTO #tmpDatabases
FROM master.sys.databases WITH (NOLOCK)
WHERE database_id > 4 AND source_database_id IS NULL AND is_read_only = 0

DECLARE @dbname sysname, @sqlcmd NVARCHAR(500);

WHILE (SELECT COUNT([name]) FROM #tmpDatabases WHERE isdone = 0) > 0
BEGIN
    SELECT TOP 1 @dbname = [name] FROM #tmpDatabases WHERE isdone = 0

    SET @sqlcmd = 'USE ' + QUOTENAME(@dbname) + '; 
        IF (SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = ''LEGACY_CARDINALITY_ESTIMATION'') = 0
        ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;'
 
    BEGIN TRY
        EXECUTE sp_executesql @sqlcmd
    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState, ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    UPDATE #tmpDatabases
    SET isdone = 1
    WHERE [name] = @dbname
END;

Для База данных SQL Azure можно создать запрос в службу поддержки, чтобы этот флаг трассировки был включен на уровне подписки, но не на уровне сервера.

Вопрос 5. Будет ли работать с устаревшим CE запретить мне получать доступ к новым функциям?

Даже если включена LEGACY_CARDINALITY_ESTIMATION, вы по-прежнему получите доступ к последним функциям, включенным в версию SQL Server и связанному уровню совместимости базы данных. Например, база данных с LEGACY_CARDINALITY_ESTIMATION включена на уровне совместимости базы данных 140 в SQL Server 2017, по-прежнему может воспользоваться семейством функций адаптивной обработки запросов.

Вопрос 6. Когда устаревшая CE выйдет из поддержки?

У нас нет планов остановить поддержку устаревшего CE на этом этапе. Однако будущие улучшения оценки кратности, связанные с оценками и исправлениями, сосредоточены вокруг более поздних версий CE.

Вопрос 7. У меня есть только несколько запросов регрессии с ce по умолчанию, но большинство производительности запросов одинаковы или даже улучшены. Что делать?

Более детальная альтернатива флагу трассировки на уровне сервера 9481 или LEGACY_CARDINALITY_ESTIMATION конфигурации базы данных — использование конструкции USE HINT с областью запроса. Дополнительные сведения см. в аргументе указания запроса USE HINT в SQL Server 2016 и USE HINT.

Примечание.

Существует также вариант с флагом QUERYTRACEON трассировки 9481, но следует рассмотреть возможность использования USE HINT вместо этого, так как это более чисто семантической и не требует специальных разрешений.

USE HINT FORCE_LEGACY_CARDINALITY_ESTIMATION позволяет задать модель CE оптимизатора запросов на версию 70 независимо от уровня совместимости базы данных. См . раздел "Уровень запроса: использование подсказки запроса" или параметра QUERYTRACEON.

Кроме того, если существует только один запрос, который является проблематичным с CE по умолчанию, вы можете принудительно применить устаревший план CE, хранящийся в хранилище запросов или использовать FORCE_LEGACY_CARDINALITY_ESTIMATION в сочетании с руководством по плану.

Вопрос 8. Если производительность запроса регрессирована из-за изменения плана, связанного с значительным превышением или недостаточной оценкой при использовании CE по умолчанию, проблема будет устранена в продукте?

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

Учитывая неограниченное сочетание схемы клиента, данных и рабочих нагрузок, практически невозможно выбрать модели, которые работают во всех случаях. Хотя некоторые изменения в CE по умолчанию могут содержать ошибки (как и любое другое программное обеспечение) и могут быть исправлены, другие проблемы вызваны изменением модели.

Изменения в версиях CE, особенно начиная с 70 до 120, включают множество различных вариантов использования моделей. Например, при оценке фильтров предполагается, что некоторый уровень корреляции между предикатами, так как, на практике такая корреляция часто существует, и модель CE 70 будет недооценивать результаты в таких случаях. Хотя эти изменения были протестированы для многих рабочих нагрузок и улучшили многие запросы, для некоторых других запросов устаревший CE был лучшим совпадением, и, таким образом, с CE по умолчанию может наблюдаться регрессия производительности.

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

Вопрос 9. Существует ли какой-либо ресурс для получения сведений об изменениях кратности в ce по умолчанию и влиянии на производительность запросов?

Дополнительные сведения см. в статье "Оптимизация планов запросов с помощью средства оценки кратности SQL Server 2014" и см. в разделе "Что изменилось в SQL Server 2014?"