Обсудим target_recovery_time_in_seconds ?

[Logger]

Привет всем.

Сегодня столкнулся со странной особенностью параметра target_recovery_time_in_seconds - он весьма сильно может влиять на производительность.

Вводные данные.
SQL Server 2016
1. База от ax4 созданная изначально в SQL 2008 и перенесеная через бекап.
2. База от ax2012 созданная из базы ax4 через конвертацию данных.

Есть запрос. Простой джоин двух табличек по 2 млн записей каждая по кластерному ключу (ItemId) с заполнением строкового поля по 10-15 символов. (Идет перекачка значения из одной таблички в другую)

Запрос по базе ax2012 работет 75 секунд.
Запрос по базе ax4 работает 40 секунд.

Структура табличек одинакова.
Почему такая разница?

Выяснилось что в базе 2012 которая создавалось с нуля инсталлятором параметр target_recovery_time_in_seconds имеет дефолтное для SQL 2016 значение - 60 секунд. А для базы 4-ки поднятой через бекап, значение 0, поэтому такая разница.

Если обнулить этот параметр в базе 12-ки, то этот запрос ускоряется в 2 раза.



Вопрос, как правильнее настраивать target_recovery_time_in_seconds для аксапты ?
Для SQL2016 у него дефолтное значение 60 (даже для tempDB). Для более ранних версий - 0

Кто-нибудь тюнинговал этот параметр ?

P.S.
Интересная ссылка в тему:
https://www.mssqltips.com/sqlservert...ver-io-spikes/

[fed]

Можно помедитировать над этой статьей.
Вообще - то что вам в этой конретной ситуации помог переход от множества мелких чекпойнтов к малому количеству больших чекпойнтов, говорит только о том, что для данного железа, данного софта и данного запроса это выгоднее. При этом не факт что другим пользователям в этот момент было так уж удобно работать. Чекпойнт, насколько я понимаю, блокирует на какие-то время многие процессы в системе, и хотя редкие чекпойнты экономят на дисковом обмене, они также снижают интерактивность системы в целом.
Ну то есть - я никогда с настройками checkpoint интервала не заморачивался. На мой взгляд - это имеет смысл только в том случае, если у вас на какое-то время резко меняется сценарий использования БД. Например - если у вас в конце месяца на выходных в БД заливается пара гигабайтов внешних данных, то есть смысл перед этой операции снизить частоту чекпойнтов, а в понедельник утром - вернуть назад в дефолтное значение (чтобы работа интерактивных пользователей не тормозила.

[Logger]

Я не утверждаю, что значение 0 лучше и правильнее. Вполне может оказаться что под oltp нагрузкой это будет хуже. Я просто привел цифры. Интересно, кто как работает.

[Vadik]

Цитата:

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

Вопрос, как правильнее настраивать target_recovery_time_in_seconds для аксапты ?

Не использовал, будет что поизучать на самоизоляции
Не совсем понятен характер нагрузки, это один большой UPDATE на 2М записей, обновление в цикле или что-то еще
КМК полезнее было бы начинать например отсюда и далее по ссылкам, там много интересного и удивительного

[Logger]

Цитата:

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

Не совсем понятен характер нагрузки, это один большой UPDATE на 2М записей, обновление в цикле или что-то еще

Это один большой апдейт на 2 миллиона записей.
Для определенности, вот запрос

PHP код:

update MERINVENTTABLEFEATURE
set NameAliasId =
    (select NameAliasId from INVENTTABLE where INVENTTABLE.ItemId = MERINVENTTABLEFEATURE.ItemId AND INVENTTABLE.dataareaId = MERINVENTTABLEFEATURE.dataareaId)

WHERE
exists ( select 1 from InventTable
    where
    (
        (INVENTTABLE.ITEMID=MERINVENTTABLEFEATURE.ITEMID)
    AND (INVENTTABLE.DATAAREAID=MERINVENTTABLEFEATURE.DATAAREAID)
    AND (INVENTTABLE.NAMEALIASID<>MERINVENTTABLEFEATURE.NAMEALIASID)
    )
) 


таблички кастомизированные. В каждой 2M записей. Кластерный индекс по ItemId.
Если хотите повторить что-то аналогичное, то можно взять вместо MERINVENTTABLEFEATURE таблицу InventTableModule. А вместо NameAliasId поле UnitId, которое изначально было пустым.

[trud]

Цитата:

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

Запрос по базе ax2012 работет 75 секунд.
Запрос по базе ax4 работает 40 секунд.

Структура табличек одинакова.
Почему такая разница?

Так а если эти запросы выполнить в менеджмент студии и включить статистику по ним - там же будет число логических, физических тестов. оно будет одинаково?
Ну и вообще ваш тест показывает по сути что производительность зависит от текущей нагрузке по записи, если вы не хотите этого, по сути надо включать кеш записи на дисковом контроллере

[Vadik]

Цитата:

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

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

Ну СХД я полагаю одна и та же используется, иначе любое сравнение не имеет смысла

Как вариант, indirect в этом случае пишет чаще сбрасывает грязные страницы на диск и к моменту завершения UPDATE тупо записал больше данных (или все), а automatic "сжульничал" и еще держит кучу данных в буфере для отложенной записи. Если СХД не сильно быстрая, эта разница в количестве записанных данных может объяснить большую разницу по времени выполнения

Если помониторить perfmon-ом запись на томе с данными от AX4.0 после '2000000 records affected' в течение минуты-двух, будет видны всплески активности по записи? Если да, то это не разница в производительности как таковая, а просто особенности поведения indirect на этом наборе данных и оборудовании

P.S. повторюсь, предположение что планы исполнения, данные, оборудование и прочие базовые условия для indirect и automatic одинаковые - в силе

[fed]

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

[Logger]

Да, СХД одна и та же. SSD.
Инстанс SQL тот же.
Поле неиндексировано.
Т.е. индекс не обновляется. Ну если только странички кластерного индекса щепятся. Ну это маловероятно. Плюс одинаково в среднем должно быть для обеих баз.

Самый важный вопрос.
Какое значение было бы оптимальным для типичной нагрузки создаваемой аксаптой.
Пойманный мной запрос не отнесешь к типичной нагрузке.
Может есть соображения?

[Vadik]

Цитата:

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

Какое значение было бы оптимальным для типичной нагрузки создаваемой аксаптой

Ну так проверьте, есть ли всплеск отложенной записи при automatic после 'nnn records affected'. Если есть, то часть ввода-вывода просто отложилась на потом и разница в производительности возможно не такая радикальная. Либо все сильно привязано к сценарию (скажем, при 1М обновляемых записей при automatic ВЕСЬ flush произойдет уже после коммита и разница во времени будет еще больше, а при 10М ощутимой разницы не будет вообще)

Как по мне, то indirect производительности не добавляет, а просто уменьшает вероятность непонятной "просадки" времени отклика (скажем, обновляем текст в строке журнала а изменения сохраняются 3 секунды) из-за массивного чекпойнта в фоне. Ну и еще MS упоминает о каких-то феерических сценариях в виде многих терабайт ОЗУ где он помогает, но они типичной аксапте не грозят