Новая оценка мощности (SQL Server 2014) ушла

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

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

Вот пример плана выполнения запроса (оператор верхнего левого угла) в SQL 2012 и SQL 2014:

Некоторые сведения:

Мои запросы - это типичные запросы загрузки таблицы фактов в хранилище данных. Я запрашиваю транзакционную таблицу и присоединяюсь к множеству (15-20) таблиц измерений (всегда есть 0 или 1 запись, которая соединена с таблицей размеров).
Я обновил статистику всех таблиц (с помощью FULLSCAN), чтобы убедиться, что статистика обновлена.
Бизнес-ключи таблиц измерений индексируются (уникальный индекс без кластеров). Мне кажется, что из-за уникальности этого индекса старая оценка мощности (SQL 2012) правильно предполагает, что там макс. 1, которая объединяется (оценочное количество записей не изменяется в плане выполнения).

Я попытался сузить проблему до простейшего примера - SELECT с двумя соединениями:

Здесь оценка мощности для операторов 1 и 2 в SQL 2012 по сравнению с SQL 2014:

           | Est.rows - SQL2012 | Est.rows - SQL2014
Operator 1 |               7653 |               7653
Operator 2 |               7653 |              10000

Как вы можете видеть, SQL Server 2014 пропускает оценку более чем на 30% (10000 против 7653). Потому что у меня есть. 15-20 присоединяется к типичному запросу, окончательная оценка уходит.

Я могу поместить базу данных в более низкий режим совместимости (110), и он отлично работает (аналогично SQL Server 2012), но мне бы очень хотелось узнать, в чем причина такого поведения. Почему результат оценки мощности SQL Server 2014 неверен?

Ответ 1

Я думаю, что сегодня нет простого ответа на этот интересный вопрос. Лучший ответ, который я знаю, - это следующее видео: http://channel9.msdn.com/events/TechEd/NorthAmerica/2014/DBI-B331#fbid=. В нем много примеров новых и старых оценок. Видеоролик длится около 50+ минут, но стоит того.

Сводка видео, относящегося к этому вопросу:

Старые предположения оценок мощности:

Однородность - данные равномерно распределены.
Независимость - столбец 1 не имеет отношения к столбцу 2.
Containment - если два атрибута могут быть одинаковыми, они считаются одинаковыми.
Включение - должно быть совпадение.

Для использования оценки мощности SQL SERVER 2012 в SQL SERVER 2014 используйте следующую опцию:

Вариант (querytraceon 9481) - вернуться к 2012

Что делает новая оценка (на основе видео):

SQL Server использует среднюю селективность в индексе и оценки количество строк путем умножения плотности ключа на общее число строк в индексе.
Новая оценка не очень хорошо работает с зубчатыми дистрибутивами.
Большинство различий между оценками основано на предложении WHERE.
Оценщик новой мощности считает, что существует корреляция между таблицами.
Вы можете создать фильтрованную статистику для улучшения запросов. (http://msdn.microsoft.com/en-us/library/ms188038.aspx)

Сделать/контрольный список:

1. Auto Create / Update Stats
2.  Check database compatibility mode (120/110)
3.  Test using query trace flags
4.  XML showplan

Обновление Что нового в оценке мощности (SQL Server 2016)

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

Подробнее:

https://docs.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server

https://www.sqlshack.com/query-optimizer-changes-in-sql-server-2016-explained/

Ответ 2

Интересно, сталкиваетесь ли вы с этим вопросом в отношении оценок многоколоночной селективности:

http://www.sqlskills.com/blogs/kimberly/multi-column-statistics-exponential-backoff/

кажется, что есть еще некоторые причуды, когда новый CE попытается также использовать TF 4137, как указано, и посмотрите, поможет ли это.

наконец-то убедитесь, что вы находитесь в последнем CU и работаете с TF 4199, чтобы включить все исправления оптимизатора запросов, как всегда проверяйте это в непроизводственной среде, если это возможно, и помните о регрессиях в других запросах, когда разрешаете настройки по всему миру

Ответ 3

Это не прямой ответ на этот вопрос, но он может помочь тем, кто сталкивается с аналогичной проблемой производительности, связанной с этой базой данных SCCM (aka ConfigMgr), относящейся к изменениям оценки стоимости (CE). SQL-запросы могут быть тайм-аутом, или ваша консоль ConfigMgr может работать медленно из-за изменений в стандарте Cardinality Estimator (CE) SQL Server 2014 и SQL Server 2016. Microsoft предоставила решение этой проблемы здесь, в котором предлагается применить соответствующий уровень совместимости с оценками SQL (EN), как показано в таблице ниже:

SQL Server version    Supported compatibility       Recommended compatibility   
                      level values                  level for ConfigMgr

SQL Server 2016       130, 120, 110, 100            130 

SQL Server 2014       120, 110, 100                 110

Надеюсь, это поможет!