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

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

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

Примечание.

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

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

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

1. Выберите запрос, который выполняется медленнее после обновления.
2. Выполните запрос и соберите план выполнения.
3. В окно свойств плана выполнения проверка CardinalityEstimationModelVersion.
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 и базы данных Azure SQL.

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

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

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

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

Важно!

CE по умолчанию — это main путь к коду, который будет получать будущие инвестиции и более глубокое тестирование в долгосрочной перспективе, поэтому не планируйте использование устаревшей 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;

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

Вопрос 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 query hint argument in 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 является сложной задачей, и алгоритмы полагаются на менее совершенные данные, доступные для оценки, такие как статистика для таблиц и индексов. Отсутствуют сведения о некоторых внешних конструкциях, таких как функции с табличным значением (TVF) и модели, основанные на множестве предположений (например, корреляция или независимость предикатов и столбцов, равномерное распределение данных, содержание и т. д.).

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

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

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

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

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