Предположение о сдерживании соединения в новом оценщике кратности снижает производительность запросов

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

Исходная версия продукта: SQL Server
Исходный номер базы знаний: 3189675

Симптомы

Рассмотрим следующий сценарий:

• Вы используете SQL Server 2014 или более позднюю версию.
• Вы запускаете запрос, содержащий соединения и предикаты фильтра без соединения.
• Вы компилируете запрос с помощью новой оценки кратности (SQL Server) (New CE).

В этом сценарии производительность запросов ухудшается.

Эта проблема не возникает, если вы компилируете запрос с помощью устаревшей ce.

Причина

Из SQL Server 2014 новый оценщик кратности (New CE) был представлен для уровня совместимости базы данных 120 и больше. Новое CE изменяет несколько предположений из устаревшей CE в модели, которая используется оптимизатором запросов при оценке кратности для разных операторов и предикатов.

Одно из этих изменений связано с предположением о сдерживании соединения.

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

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

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

Дополнительные сведения об устранении неполадок, связанных с Новым CE, см. в статье "Снижение производительности запросов после обновления с SQL Server 2012 или более поздней версии до версии 2014 или более поздней версии".

Решение

В SQL Server 2014 и более поздних версиях можно использовать флаг трассировки 9476 , чтобы принудительно sql Server использовать предположение простого сдерживания вместо допущения базового сдерживания по умолчанию. Если вы можете изменить запрос приложения, лучше использовать подсказку ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS запроса после SQL Server 2016 (13.x) с пакетом обновления 1 (SP1). Дополнительные сведения см. в разделе USE HINT. Например:

SELECT * FROM Table1 t1
JOIN Table2 t2
ON t1.Col1 = t2.Col1
WHERE Col1 = 10
OPTION (USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'));

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

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

Этот флаг трассировки можно включить глобально, на уровне сеанса или на уровне запроса.