Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 sql 2008 r2 vs sql 2014  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Занялся сравнительным тестированием производительности двух версий сиквела. На одном сервере установлены 2 инстанса 2008 и 2014. Память между инстансами поделена поровну. Диски для хранения файлов пользовательской БД и tempdb одинаковые.
Версия 2014: 12.0.2000.8 (x64)
Версия 2008: 10.50.4000.0

На оба сервера являются подписчиками транзакционной репликации с главного сервера.
Тестирование производилось путем проигрывания записанного трейса (шаблон Replay) с фильтром по основной интересной мне процедуре с аналогичного сервера . Сторонней нагрузки кроме репликации никакой нет. Почему то 2008 делает 2014 в 2 - 2.5 раза по времени выполнения. Мне казалось, что новые версии серверов должны показывать большую или сравнимую производительность, но в моем случае это не так. Подскажите пожалуйста, что я сделал не так, в какую сторону смотреть?

PS планы выполнения процедуры на 2008 и 2014 не отличались
28 июл 14, 18:41    [16368000]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 r2 vs sql 2014  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4900
Знаете, у нас после переезда на SQL 2014 тоже производительность просела.

Одна из вещей, которую надо сделать сразу после переезда -- это полностью обновить статистику.

Второй аспект -- это то, что утверждение "новые версии серверов должны показывать большую или сравнимую производительность" может быть неверным. Но новой версии возможно запрос можно написать быстрее, но не факт, что старые запросы должны работать с той же скоростью. См. последнее заявление в этом абзаце:

The cardinality estimation logic, called the cardinality estimator, is re-designed in SQL Server 2014 to improve the quality of query plans, and therefore to improve query performance. The new cardinality estimator incorporates assumptions and algorithms that work well on modern OLTP and data warehousing workloads. It is based on in-depth cardinality estimation research on modern workloads, and our learnings over the past 15 years of improving the SQL Server cardinality estimator. Feedback from customers shows that while most queries will benefit from the change or remain unchanged, a small number might show regressions compared to the previous cardinality estimator.

http://msdn.microsoft.com/en-us/library/dn600374.aspx
28 июл 14, 18:59    [16368074]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 r2 vs sql 2014  [new]
Crimean
Member

Откуда:
Сообщений: 13147
а можно вопросов?

- "делает" - можно расшифровать? "общее время" выполнения теста разное или "каждое выполнение" медленнее?
- планы НЕ отличались - а стоимости в cpu / reads / writes - отличались?
- пробовали задушить 14 сервер трасфлагами / уровнем совместимости БД - поведение выравнивалось?
28 июл 14, 19:57    [16368262]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 r2 vs sql 2014  [new]
aleks2
Guest
Я фсегда говорил: MS Office 95 в разы быстрее MS Office 2010.
29 июл 14, 05:47    [16369219]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 r2 vs sql 2014  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Crimean
а можно вопросов?

- "делает" - можно расшифровать? "общее время" выполнения теста разное или "каждое выполнение" медленнее?
- планы НЕ отличались - а стоимости в cpu / reads / writes - отличались?
- пробовали задушить 14 сервер трасфлагами / уровнем совместимости БД - поведение выравнивалось?

1) Замерялось общее время выполнение реплея трейса. В трейс был отфильтрован по одной основной поисковой процедуре. Соответственно трейс включал в себя вызов этой хп с различныи наборами параметров (порядка 500 вызовов). Соответсвенно совокупное выполнение медленнее.
2) io и cpu я не смотрел , полагая что на одинаковых репликах они по сути должны быть приблизительно одинаковыми, но проверю.
3) Не пробовал, проверю. А подскажите насчет трасфлагов, что надо включать?
29 июл 14, 10:31    [16369732]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 r2 vs sql 2014  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
a_voronin
Знаете, у нас после переезда на SQL 2014 тоже производительность просела.

Одна из вещей, которую надо сделать сразу после переезда -- это полностью обновить статистику.

статистика обновляется регулярно по расписанию.
a_voronin
Второй аспект -- это то, что утверждение "новые версии серверов должны показывать большую или сравнимую производительность" может быть неверным. Но новой версии возможно запрос можно написать быстрее, но не факт, что старые запросы должны работать с той же скоростью. См. последнее заявление в этом абзаце:

The cardinality estimation logic, called the cardinality estimator, is re-designed in SQL Server 2014 to improve the quality of query plans, and therefore to improve query performance. The new cardinality estimator incorporates assumptions and algorithms that work well on modern OLTP and data warehousing workloads. It is based on in-depth cardinality estimation research on modern workloads, and our learnings over the past 15 years of improving the SQL Server cardinality estimator. Feedback from customers shows that while most queries will benefit from the change or remain unchanged, a small number might show regressions compared to the previous cardinality estimator.

http://msdn.microsoft.com/en-us/library/dn600374.aspx

Это очень печально, поскольку не оставляет аргументов для менеджеров для обоснования покупки лицензий.
29 июл 14, 10:53    [16369842]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 r2 vs sql 2014  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
aleks2
Я фсегда говорил: MS Office 95 в разы быстрее MS Office 2010.

пуля дура, штык молодец
29 июл 14, 10:54    [16369847]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 r2 vs sql 2014  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Мистер Хенки
Это очень печально, поскольку не оставляет аргументов для менеджеров для обоснования покупки лицензий.


оффтоп, конечно, но

"отклик" на моей памяти только падал от версии к версии и - да - это печально
а помню я еще 6.5 в продуктиве, с фильтрованными репликами, которые снапшотились всю ночь на приличных массивах
если ТОГДА кто-то сказал бы, что без проблем будут обрабатываться лярдовые таблички - я бы не поверил
а сейчас bigint ПК - норма ибо int не хватает и это уже не парит
вывод - отклик подопрется железом, а добавленные в версиях "нямки" в большинстве своем стоят того
да и манагерам проще оперировать понятиями доступности, типа % аптайма, чем долями секунд ожидания формочки
"тяжелые" запросы, насколько я помню, за редким исключением, хуже не становились
беды случались где было много мелких запросов - но все лечилось
29 июл 14, 11:04    [16369901]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 r2 vs sql 2014  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8819
aleks2
Я фсегда говорил: MS Office 95 в разы быстрее MS Office 2010.

Дык более процедурно-императивное программирование по определению быстрее объектного, но намного медленнее в разработке и частично медленнее в сопровождении.
29 июл 14, 11:21    [16369990]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 r2 vs sql 2014  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
В общем попробовал записать новый трейс - тестирование времени прогонки реплея стало ожидаемо практически одинаковым у 2008 и 2014. Видимо я неправильно записал прошлый трейс. В прошлый раз ставил фильтр на название процедуры по полю TextData и видимо не все нужные события записались из шаблона Replay. В этот раз фильтровал по базе и пользователю. В любом случае спасибо за интересные советы.
29 июл 14, 14:25    [16371248]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 r2 vs sql 2014  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8819
Я бы первым делом пересчитал статистики и перестроил индексы, потом бы сравнивал.
Да, и alter table rebuild присовокупил.
29 июл 14, 14:36    [16371324]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 r2 vs sql 2014  [new]
ScareCrow
Member

Откуда: Белый город
Сообщений: 17423
Владислав Колосов
Я бы первым делом пересчитал статистики и перестроил индексы, потом бы сравнивал.
Да, и alter table rebuild присовокупил.


алё, гараж. народ РЕПЛИКАЦИЮ тестирует
29 июл 14, 15:39    [16371825]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 r2 vs sql 2014  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8819
ScareCrow,
алё, гараж
автор
В трейс был отфильтрован по одной основной поисковой процедуре. Соответственно трейс включал в себя вызов этой хп с различныи наборами параметров

где здесь репликация?
29 июл 14, 15:48    [16371884]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 r2 vs sql 2014  [new]
EvAlex
Member

Откуда: Israel
Сообщений: 1001
из за изменений в оптимайзере в 2012 и нового Cardinality Estimator в 2014, надо менять некоторые индексы и проверять успевает ли оптимайзер найти "лучший план", а не good enough.

У нас ухудщения заметны практически по всех апгрейдах, а не "a small number might show regressions compared to the previous cardinality estimator.".

можно проверить используя trace flag 9481 (использовать старый CE)
http://support.microsoft.com/kb/2801413

А потом добавить trace flag 8780 (больше времени на поиск плана) и попробовать жить с ним.

здесь рассказ хороший про CE
http://milossql.wordpress.com/category/sql-server-2014/
29 июл 14, 16:01    [16371977]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 r2 vs sql 2014  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
ScareCrow
Владислав Колосов
Я бы первым делом пересчитал статистики и перестроил индексы, потом бы сравнивал.
Да, и alter table rebuild присовокупил.


алё, гараж. народ РЕПЛИКАЦИЮ тестирует

не, репликацию я не тестирую. Она присутствует на двух подопытных серверах, но ее работа меня вполне удовлетворяет. Я тестирую поисковый запрос к БД, в которую реплицируются данные.
29 июл 14, 16:19    [16372119]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 r2 vs sql 2014  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Ни в коем случае не включайте глобально флаг 8780 на реальном сервере, если только не хотите умышленно тратить на компиляцию гораздо больше ресурсов. Этот флаг не "больше времени на поиск плана", это немного другое. Мне хотелось бы посмотреть на реальный пример, в котором этот флаг действительно являлся бы последним оставшимся спасением, т.е. когда ничего другое не помогает - пока такого случая, в моей практике не было.

Вы пишете, что у вас одинаковые планы на разных версиях сервера 2008R2 и 2014, которые представляют собой вызов ХП с различными параметрами. Вы сравнивали планы 2008R2 и 2014 для всех типичных значений параметров и они получились одинаковые?

1) Если да, то это наводит на мысль о том, что используется одинаковая логика для оценки, это наводит на мысль, что БД на инстансе 2014 находится в режиме совместимости с предыдущей версией. В таком случае, используется "старый" механизм оценки и планы будут получаться похожими на 2008R2 в большинстве своем (не все).

Дальше есть два момента:

1.1 Выяснить, почему при одинаковых планах (БД я так понял тоже одинаковые) сильно отличается время. Я бы начал с того, что замерил ожидания. Тем более это просто сделать, если нет другой нагрузки. Делаете слепок sys.dm_os_wait_stats до прогона трассы, прогоняете трасу, делаете после - смотрите разницу, оставляете наиболее высокие. Потом то же самое на втором сервере. Сравниваете получившиеся таблицы, смотрите, чего дольше ждал один из серверов.

1.2 Протестировать новую логику CE. Для этого, не надо кидаться обновлять все индексы, достаточно просто поменять уровень совместимости "alter database ... set compatibility_level = 120". Убедиться что сервер использует новую логику вычисления оценок можно, проверив в плане запроса свойство корневого (зелененького) элемента CardinalityEstimationModelVersion. 120 - новый, 70 - старый.

2) Если используется последний уровень совместимости и новый механизм оценки, а планы тем не менее одинаковые (мало вероятно, но теоретически может быть, особенно, если это только одна процедура), то я бы вернулся к пункту 1.1 и посмотрел опять таки ожидания, т.е. если дело не в разных планах - то в чем.

3) Если планы все-таки разные, то это логически объясняет разное время. В таком случае, нужно выяснить, почему планы получились хуже на 2014-м сервере. Для этого, собственно, ищете что у вас в трассе по времени стало дольше выполняться. Сравниваете планы.

Далее смотрите, чем вызван регресс плана:

3.1 стандартные проблемы: прослушивание параметров/устаревшая статистика и т.д. боремся как обычно. Может быть любопытно, почему старый механизм при этом справлялся лучше - такое бывает, когда неправильные оценки приводят к более "удачному" плану, просто в силу статистической природы алгоритмов оценки и простому "везению".

3.2 проблемы новой модели: можем точечно воздействовать в конкретном запросе при помощи уже упомянутого флага 9481 и указания уровня запроса QUERYTRACEON 9481.

3.3 можем воздействовать в обратную сторону, т.е. понизить уровень совместимости всей БД и выборочно включать новую логику только в интересующих нас запросах 2312.

Кстати, было бы интересно, если бы вы смогли привести репро и поделиться кейсами из п. 3.1-3.2. Где новая логика работает хуже, чем старая.
29 июл 14, 18:35    [16372838]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 r2 vs sql 2014  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
всем спасибо за ответы, особенно за развернутую инструкцию SomewhereSomehow .
Проблема была видимо в методологии тестирования и в трейсе который я первоначально записал для реплея (в фильтрующих условиях трейса). При изменении фильтрующих условий на записываемом трейсе , тестирование производительности методом реплея показало сходные результаты по производительности с 2008 сервером - чего я и ожидал. Резюме: в моем частном случае производительность на 2008 и 2014 сопоставима.

ЗЫ Действительно в 2014 сервере в тестовой Бд уровень совместимости был для 2008 , но изменение на 2014 не повлияло на планы в тех случаях, которые я изучал. Реплей с установкой совместимости 2014 не дал каких то кардинальных изменений во времени его выполнения по сравнению с установкой совместимости 2008.
30 июл 14, 11:48    [16375885]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 r2 vs sql 2014  [new]
EvAlex
Member

Откуда: Israel
Сообщений: 1001
SomewhereSomehow,
я бы всё равно сравнил бы количество Good Enough планов между 2008 и 2014.
30 июл 14, 14:12    [16377059]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 r2 vs sql 2014  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
EvAlex,

Сравнивайте, пожалуйста, непонятно только, какие выводы из результатов сравнения можно сделать?
Кроме того, почему именно Good Enough Plan, а не Timeout? С флагом 8780 - также может быть Good Enough Plan...

Я вот тут когда-то разбирался с отключением порогов оптимизатора, если вам будет интересно, почитайте. Вдруг после прочтения, вы измените свое мнение.
Оптимизатор без границ (ч.1)
Оптимизатор без границ (ч.2)
Good Enough Plan

ИМХО, сравнивать имеет смысл не количество планов, а общую нагрузку/быстродействие. Из того, что сервер потратит много ресурсов и времени и найдет план который выполнится на 10 мс быстрее - не следует что это наилучшая стратегия для сервера в целом.
30 июл 14, 15:30    [16377609]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 r2 vs sql 2014  [new]
EvAlex
Member

Откуда: Israel
Сообщений: 1001
SomewhereSomehow,
тю - Timeout конечно-же имел в виду.
30 июл 14, 18:31    [16378770]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 r2 vs sql 2014  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
EvAlex
SomewhereSomehow,
тю - Timeout конечно-же имел в виду.

Эксперимент становится осмысленне в плане флага, но сути дела не меняет.

Впрочем, вы попробуйте.

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

Предлагаю вам протестировать 4 варианта:
- старый CE
- старый CE с флагом 8780
- новый CE
- новый CE с флагом 8780

Собирайте счетчики производительности, ожидания, не забудьте про нагрузку: число батчей в секунду/компиляций/рекомпиляций.
Также важно огласить условия теста, чтобы было понятно как вы тестировали. Ведь в синтетическом тесте - можно подбрать любые условия и подогнать под результат - максимум сложных запросов, минимум рекомпиляций и - вуаля, когда "идеальные" планы будут закешированны, ничего менятся не будет, то выигрыш даже возможно будет заметен. Но тогда вопросы к тесту =)

Если сделаете - это будет тянуть на серьезную статью, там уже появится предмет для обсуждения.
Это было бы интересно, без всяких шуток.

Я бы и сам занялся, только в смысле флага 8780 доверяю МС, а в смысле новый/старый СЕ - доверяю тестам TPC и тем же тестам МС, которые они опубликовали на прошлогоднем SIGMOD. Не вижу смысла тратить время - но если кто-то сомневается (а как еще делаются открытия) - то вперед! =)
30 июл 14, 19:13    [16378928]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить