axperf: Important SQL Server Change! - Parameter Sniffing and Query Plan Caching

[Blog bot]

Источник: http://blogs.msdn.com/axperf/archive...n-caching.aspx
==============

Some of you that attended Convergence may have heard about a significant SQL Server change that can result in substantial performance improvements for Dynamics AX customers. You can find details on the change and how to acquire the associated SQL Server...(read more)

Источник: http://blogs.msdn.com/axperf/archive...n-caching.aspx

[gl00mie]

Интересно, а кто-нить уже применил эту новую возможность Ms SQL Server? Или хотя бы начал ее тестирование?..

[Wamr]

честно, что-то я не понял, что изменилось, и как получилось

Цитата:

customer testing have shown dramatic improvements

[Vadik]

Ох уж эти евангелисты.. Изобретена заветная опция /FAST, мы должны поведать об этом миру

Цитата:

Сообщение от Wamr

честно, что-то я не понял, что изменилось

При включенном trace flag оптимизатор при построении плана исполнения параметризованного запроса использует не переданные значения, а усредненную селективность + информацию о размере используемых таблиц + некоторые зашитые в оптимизатор значения селективности операторов сравнения (например мы считаем что условие WHERE MYFIELD = @SOMEVALUE возвращает 10% записей, WHERE MYFIELD > @SOME VALUE - 60% и т.д.)

Пример:
Мы в своей БД ведем учет в нескольких компаниях разного размера - большая 001, средняя 002 и совсем крохотная 003 (70%, 25% и 5% общего объема данных соответственно).
До появления заветной опции при использовании prepared execution попадающий в кэш план сильно зависит от того, с какими параметрами (для какой компании) он был построен. В нашем случае для компании 003 (5%) простой как дверь индекс по DATAAREAID будет иметь хорошую селективность, а в большой 001 для SELECT * FROM MYTABLE WHERE DATAAREAID = '001' будет дешевле использовать table scan.

"Волшебная" опция заставит оптимизатор брать усредненную селективность (в случае трех компаний 1 / 3 = 0.33). Что это дает и ответ на вопрос "как получилось 'customer testing have shown dramatic improvements'" - см. ниже

Пример из жизни (уже из прошлого, пускай и недалекого):
Мы ведем учет в 15 компаниях в AX 4.0, все начинают отсчет от 1 января прошлого года (после миграции и переноса остатков из старой БД), номенклатурные справочники совпадают процентов на 80 во всех компаниях. Единственное отличие - пара крупных компаний (стран) генерирует 5-10 миллионов складских транзакций в год, остальные не в пример меньше, скажем 25 тысяч для самых мелких, итого 20 миллионов складских транзакций по всем компаниям за год. В середине года внезапно (в этой компании все только так и происходит) во всех странах warehouse manager-ов одолевает желание посмотреть складские остатки на январь-февраль. Кто работал с inventory value отчетами, знает, что в них используется "откручивание" всех складских проводок от "сегодня" до отчетной даты. Так вот, если первым отчет запустил менеджер из "маленькой" компании, оптимизатор просканирует некластерный индекс по (DataAreaId, ItemId, FinancialDate), что в зависимости о отчетной даты вернет от 0.01 до 0.1 процентов от общего обхемы данных, после чего по указателям из индекса он метнется к страницам с данными (пример условный, зависит от модификаций и распределения данных). Оптимизатор молодец, отчет работает быстро, менеджер доволен. Следующим отчет запускает менеджер "большой" (нет, лучше так - БОЛЬШОЙ) компании, план уже закэширован, настроение у оптимизатора шапкозакидательское, вот только метаться за данными ему придется на пару порядков раз чаще, отчет работает во столько же раз медленнее, что несколько удручает менеджера БОЛЬШОЙ компании

Теперь о том, что изменилось. При включенном trace flag-е селективность DataAreaId как для маленькой, так и для большой компаний будет считаться как 1 / 15 = 0.06(6) (без него - 25000 / 20000000 = 0.00125 и 10000000 / 20000000 = 0.5 соответственно), что дает чуть более пессимистичный подход при построении плана исполнения в маленькой компании. Стало лучше? Может быть. НО. Если посмотреть на полученную селективность ( 1 / 15 = 0.06(6)), она и рядом не стояла с реальной в случае БОЛЬШОЙ компании ( 0.5 )

Что же касается dramatic improvements, есть некоторые основания полагать, что это не совсем improvements, а скорее переход от состояния "полная Ж" в случаях, подобных рассмотренному, к состоянию "вполне приемлемо".
Так что пробовать, тестировать - да пожалуйста, главное - правильно сформировать ожидания
Как по мне, так просто еще одна полезная опция, как обычно - со своей спецификой, областью применения и ограничениями. Из плюсов - еще одна возможность управлять поведением оптимизатора в случае невозможности управлять поведением приложения, что как бы не совсем наш случай (AX). Из минусов - опять же глобальность (trace flag работает на уровне сервера) и неаккуратность в некоторых случаях.

Я в описанном случае конечно "пощупал" бы этот trace flag, но за неимением такового расставленные в нужных местах forceliterals и принудительный регулярный сброс закэшированных планов (DBCC FREEPROCCACHE) нормально "рассосали" ситуацию

P.S. Финальный штрих - Overcoming parameter sniffing issue in Microsoft Dynamics AX 2012-R2 – CU6