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

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

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

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

Estimed Number of Rows

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

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

  • Я обновил статистику всех таблиц (с помощью FULLSCAN), чтобы убедиться, что статистика обновлена.

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

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

Join

Здесь оценка мощности для операторов 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

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