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

Откуда:
Сообщений: 15
Добрый вечер.

Ищу ответ на вроде бы элементарный вопрос:
Есть ли разница в нагрузке на диск / процессор / ОЗУ при использовании полной модели восстановления и простой модели восстановления? Интересует именно процесс работы, а не затрачиваемые ресурсы на регулярный бэкап журнала транзакций.

Может быть есть какая-то ссылка, которую можно изучить?
5 ноя 19, 16:09    [22010060]     Ответить | Цитировать Сообщить модератору
 Re: Разница в нагрузке в зависимости от модели восстановления  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29549
morohon
Есть ли разница в нагрузке на диск / процессор / ОЗУ при использовании полной модели восстановления и простой модели восстановления?
Имеет, для некоторых случаев минимального протоколирования.
Если таких операций вами не предусмотрено, то без разницы.
5 ноя 19, 16:59    [22010107]     Ответить | Цитировать Сообщить модератору
 Re: Разница в нагрузке в зависимости от модели восстановления  [new]
morohon
Member

Откуда:
Сообщений: 15
alexeyvg, А вы не можете кинуть в меня ссылкой, где про это почитать можно подробнее?
6 ноя 19, 09:27    [22010432]     Ответить | Цитировать Сообщить модератору
 Re: Разница в нагрузке в зависимости от модели восстановления  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29549
morohon
alexeyvg, А вы не можете кинуть в меня ссылкой, где про это почитать можно подробнее?
Влияние на загрузку данных: The Data Loading Performance Guide
Влияние на операции с индексами: Выбор модели восстановления для операций с индексами
Вот ещё перевод от Александра Гладченко: Руководство по производительности загрузки данных

Сообщение было отредактировано: 6 ноя 19, 11:12
6 ноя 19, 11:11    [22010555]     Ответить | Цитировать Сообщить модератору
 Re: Разница в нагрузке в зависимости от модели восстановления  [new]
morohon
Member

Откуда:
Сообщений: 15
alexeyvg,

Спасибо огромное!

А подскажите еще момент пожалуйста, касательно как раз второй ссылки: https://docs.microsoft.com/ru-ru/previous-versions/sql/sql-server-2008-r2/ms191484(v=sql.105)

Правильно ли я понимаю, что здесь идет речь о том, что при полной модели восстановления и выполнения процедуры реиндексации (как частный пример) будет резкий рост журнала транзакций и, чтобы этого избежать, рекомендуется на время выполнения этой операции необходимо переводить базу данных в простую модель восстановления, а по окончании обратно? Или я неправильно понимаю?

Цепочка бэкапов при этом не нарушится?
6 ноя 19, 16:54    [22010932]     Ответить | Цитировать Сообщить модератору
 Re: Разница в нагрузке в зависимости от модели восстановления  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29549
morohon
Правильно ли я понимаю, что здесь идет речь о том, что при полной модели восстановления и выполнения процедуры реиндексации (как частный пример) будет резкий рост журнала транзакций и, чтобы этого избежать, рекомендуется на время выполнения этой операции необходимо переводить базу данных в простую модель восстановления, а по окончании обратно? Или я неправильно понимаю?
Да, журнал в случае полного протоколирования заполняется быстрее.

Но выбирать модель нужно исходя из требований к восстановлению, а не для снижения нагрузки на лог.

Если вам нужно иметь возможность восстановить данные на любой момент времени, то выбирайте FULL модель, если достаточно только на момент бакапа - то SIMPLE.

И никак иначе, никаких "переключений на время перестроения".
6 ноя 19, 17:00    [22010940]     Ответить | Цитировать Сообщить модератору
 Re: Разница в нагрузке в зависимости от модели восстановления  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29549
morohon
Цепочка бэкапов при этом не нарушится?
Да, цепочка нарушается, так что переключение туда-обратно - рискованная операция.
Плюс будут тратиться ресурсы DBA на поддержку этого нестандарта, не стоит оно того.
6 ноя 19, 17:02    [22010942]     Ответить | Цитировать Сообщить модератору
 Re: Разница в нагрузке в зависимости от модели восстановления  [new]
morohon
Member

Откуда:
Сообщений: 15
alexeyvg,

И снова спасибо за ответ.

Вопрос касательно заполнения журнала транзакций при перестроении индексов не относился к тематике производительности различных моделей восстановления.

Я просто часто в работе сталкиваюсь с базами MSSQL в полной модели восстановления и огромным журналом транзакций. И в большинстве своем это связано с тем, что после перестроения индексов журнал транзакций вырастает до немыслимых размеров. Может быть Вы в курсе как решать эту проблему правильно? У меня есть примеры, где файл базы данных весит 40-50 гб, а журнал транзакций весит от 250 гб. Хочется и использовать полную модель восстановления и иметь приемлемый размер журнала транзакций с учетом его постоянного резервного копирования (раз в полчаса/час/два).

Сейчас в msdn нашел статью: https://docs.microsoft.com/ru-ru/sql/relational-databases/indexes/transaction-log-disk-space-for-index-operations?redirectedfrom=MSDN&view=sql-server-ver15

и там говорится о сортировке результатов в tempdb для уменьшения нагрузки на журнал транзакций - насколько сильно это помогает? Или тут только тесты?

Может быть есть какие-то полезные статьи, чтобы понять как решаются такие проблемы?
6 ноя 19, 17:05    [22010947]     Ответить | Цитировать Сообщить модератору
 Re: Разница в нагрузке в зависимости от модели восстановления  [new]
Yasha123
Member

Откуда:
Сообщений: 1621
morohon

Правильно ли я понимаю, что здесь идет речь о том, что при полной модели восстановления и выполнения процедуры реиндексации (как частный пример) будет резкий рост журнала транзакций и, чтобы этого избежать, рекомендуется на время выполнения этой операции необходимо переводить базу данных в простую модель восстановления, а по окончании обратно? Или я неправильно понимаю?

Цепочка бэкапов при этом не нарушится?

конечно нарушится.
чтобы не раздувать лог, переводят в bulk logged.
6 ноя 19, 17:08    [22010950]     Ответить | Цитировать Сообщить модератору
 Re: Разница в нагрузке в зависимости от модели восстановления  [new]
morohon
Member

Откуда:
Сообщений: 15
Yasha123,

И Вам спасибо за ответ.

картинка складывается благодаря Вашим ответам, ответам alexeyvg и документации microsoft. Получается общая методика полной модели восстановления такая:
Регулярно с некоторой периодичностью (раз в неделю, месяц, два (в зависимости от требований системы)) делаем полный бэкап базы данных. Постоянно делаем бэкап журнала транзакций (каждые полчаса, час, два), чтобы добиться его усечения. При массовых операциях (таких как перестроение индекса) необходимо перевести модель восстановления базы в полную модель с неполным протоколированием, выполнить операцию и вернуть полную модель восстановления. При таком подходе цепочка бэкапов не будет нарушена?

Или я все же не прав?
6 ноя 19, 17:16    [22010955]     Ответить | Цитировать Сообщить модератору
 Re: Разница в нагрузке в зависимости от модели восстановления  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29549
morohon
Я просто часто в работе сталкиваюсь с базами MSSQL в полной модели восстановления и огромным журналом транзакций. И в большинстве своем это связано с тем, что после перестроения индексов журнал транзакций вырастает до немыслимых размеров. Может быть Вы в курсе как решать эту проблему правильно? У меня есть примеры, где файл базы данных весит 40-50 гб, а журнал транзакций весит от 250 гб. Хочется и использовать полную модель восстановления и иметь приемлемый размер журнала транзакций с учетом его постоянного резервного копирования (раз в полчаса/час/два).

Сейчас в msdn нашел статью: https://docs.microsoft.com/ru-ru/sql/relational-databases/indexes/transaction-log-disk-space-for-index-operations?redirectedfrom=MSDN&view=sql-server-ver15

и там говорится о сортировке результатов в tempdb для уменьшения нагрузки на журнал транзакций - насколько сильно это помогает?
Да, вот можно использовать рекомендации по ссылке.
Про п. 3 как раз написал Yasha123

Ещё можно во время выполнения обслуживания запускать дополнительные бакапы лога.
6 ноя 19, 17:19    [22010960]     Ответить | Цитировать Сообщить модератору
 Re: Разница в нагрузке в зависимости от модели восстановления  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1106
morohon
При массовых операциях (таких как перестроение индекса) необходимо перевести модель восстановления базы в полную модель с неполным протоколированием, выполнить операцию и вернуть полную модель восстановления. При таком подходе цепочка бэкапов не будет нарушена?

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

Сообщение было отредактировано: 6 ноя 19, 17:31
6 ноя 19, 17:29    [22010971]     Ответить | Цитировать Сообщить модератору
 Re: Разница в нагрузке в зависимости от модели восстановления  [new]
morohon
Member

Откуда:
Сообщений: 15
Minamoto,

Спасибо огромное!

Я понимаю, что задаю глупые вопросы и все ответы есть в документации - извините!

Все ответы помогли найти нужные ссылки в документации и понять механизм работы. Еще раз спасибо, будем пробовать и чаще обращаться к документации!
6 ноя 19, 17:34    [22010975]     Ответить | Цитировать Сообщить модератору
 Re: Разница в нагрузке в зависимости от модели восстановления  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29549
morohon
Получается общая методика полной модели восстановления такая:
Регулярно с некоторой периодичностью (раз в неделю, месяц, два (в зависимости от требований системы)) делаем полный бэкап базы данных. Постоянно делаем бэкап журнала транзакций (каждые полчаса, час, два), чтобы добиться его усечения.
Не, раз в месяц - слишком редко. Представляете, сколько будет файлов бакапов логов на один полный бакап, и как всё это нужно будет восстанавливать?

И объём хранения таким образом увеличивается (представляете, сколько будет логов, еслри у вас только от одного обслуживания получилось 250 гигов на 50 гиг базу?)

Нужно делать полный бакап раз в сутки, а бакапы логов чаще (час, полчаса)
morohon
чтобы добиться его усечения
Нет, что бы получить возможность восстановить базу.
Главное для базы, и для DBA - что бы не пропали данные, даже в случае краха серверов!!!

morohon
При массовых операциях (таких как перестроение индекса) необходимо перевести модель восстановления базы в полную модель с неполным протоколированием, выполнить операцию и вернуть полную модель восстановления. При таком подходе цепочка бэкапов не будет нарушена?
В принципе всё правильно.

Вот ещё некоторые ссылки:
Модели восстановления
Резервное копирование с использованием модели восстановления с неполным протоколированием
6 ноя 19, 17:36    [22010980]     Ответить | Цитировать Сообщить модератору
 Re: Разница в нагрузке в зависимости от модели восстановления  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6949
Есть очень простое решение по переиндексации, не вызывающее большого прироста журнала - выполнять растянуто по времени, не в один приём, а в течение недели, скажем.

И лучше не заменять перерасчет статистик переиндексацией.
6 ноя 19, 18:23    [22011022]     Ответить | Цитировать Сообщить модератору
 Re: Разница в нагрузке в зависимости от модели восстановления  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29549
Владислав Колосов
Есть очень простое решение по переиндексации, не вызывающее большого прироста журнала - выполнять растянуто по времени, не в один приём, а в течение недели, скажем.
Да, общее желание выполнить переиндексацию "всего" за один раз вызвано, видимо, инерцией мышления.
6 ноя 19, 20:28    [22011086]     Ответить | Цитировать Сообщить модератору
 Re: Разница в нагрузке в зависимости от модели восстановления  [new]
flexgen
Member

Откуда: Город на песке
Сообщений: 739
alexeyvg
Да, общее желание выполнить переиндексацию "всего" за один раз вызвано, видимо, инерцией мышления.


Насколько помню всегда рекомендовалось выполнять переиндексацию в период минимальной нагрузки на систему, а это, как правило, ночные часы в конце недели. "Размазывать" переиндексацию на неделю наверное можно, но может сложится ситуация, при которой индексам таблиц, перестроенных в понедельник, к субботе снова потребуется переиндексация, при том что останутся таблицы, до которых очередь еще не дошла.
По поводу раздутых логов - если после первой переиндексации размер лога стал, скажем 100 ГБ, в течении недели не изменился, после второй переиндексации все еще 100 ГБ - то, видимо, это размер, необходимый для нормального функционирования базы. Все, что надо сделать - позаботиться, что бы на диске было достаточно свободного места для размещения лога такого размера.
6 ноя 19, 23:50    [22011138]     Ответить | Цитировать Сообщить модератору
 Re: Разница в нагрузке в зависимости от модели восстановления  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29549
flexgen
может сложится ситуация, при которой индексам таблиц, перестроенных в понедельник, к субботе снова потребуется переиндексация, при том что останутся таблицы, до которых очередь еще не дошла.
А если делать переиндексацию по субботам, то к среде для всех таблиц потребуется переинедксация :-)

Тут нужно правильно выбирать период, ИМХО именно он важен, а не день недели.
Вот правильное время минимальной нагрузки действительно может быть возможно выбрать только на определённый день недели, тут уж ничего не поделаешь...

flexgen
По поводу раздутых логов - если после первой переиндексации размер лога стал, скажем 100 ГБ, в течении недели не изменился, после второй переиндексации все еще 100 ГБ - то, видимо, это размер, необходимый для нормального функционирования базы. Все, что надо сделать - позаботиться, что бы на диске было достаточно свободного места для размещения лога такого размера.
В общем то правильно, но обидно же, что из за одной-единственной операции обслуживания придётся покупать в 5 раз больше СХД, тем более быстрого СХД, а не для бакапов.
7 ноя 19, 00:48    [22011152]     Ответить | Цитировать Сообщить модератору
 Re: Разница в нагрузке в зависимости от модели восстановления  [new]
PsyMisha
Member

Откуда: другая столица
Сообщений: 468
alexeyvg, - плюсадин! :)
7 ноя 19, 09:04    [22011205]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить