Добрый день.

Помогите составить оптимальный план обслуживания для БД ~ в 1Tb и работающей 24/7
Сервер MS SQL 2012

Полагаю что последовательность желательна такая, но если ошибаюсь, то поправьте:
1. Проверка базы
2. Оптимизация индексов
3. Обновление статистики
4. Чистка процедурного кэша.

Как я понимаю Микрософт не рекомендует использовать
sp_msforeachtable N'DBCC DBREINDEX (''?'')'
но не нашел что они предлагают взамен такое же простое и универсальное, а просто отсылают к ALTER INDEX.

Решение от https://ola.hallengren.com/ приводит к сильному увеличению лога транзакций, сколько не давал, всегда не хватает, последний раз превысил 200Гб. При этом в процессе не дает бэкапить и усекать лог транзакций.
Как вариант конечно можно переводить базу в simple режим, но не хотелось бы это делать, т.к. потом с бэкапами придется возиться.

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

да, это понятно, просто не стал это это расписывать.
Оптимизирую только если размер больше 100 страниц, и при фрагментации >10% реорганизация, при >30% перестроение.

Если бэкап должен делаться, то допускаю что где то накосячил, к сожалению прошло с тех пор достаточно времени и логи потерлись с ошибками.
Попробую повторить процедуру на выходных.

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

И по поводу https://ola.hallengren.com/ все используют это решение или есть что то лучше?

Yasha123можно уменьшить объем того, что уйдет в лог.
переключив базу в bulk logged.
в бэкап лога уйдет полный объем.
Не использовал никогда и забыл про этот режим)
Надо почитать про него.
Спасибо.

Yasha123Гавриленко Сергей Алексеевич,
для человека, у которого перевод в симпл вызывает лишь "дополнительную возню с бэкапами":
авторКак вариант конечно можно переводить базу в simple режим, но не хотелось бы это делать, т.к. потом с бэкапами придется возиться.
потеря данных явно не проблема

Если нравится к словам цепляться, то это на здоровье.
И конечно потеря терабайтной базы совсем не проблема.

А если по делу, то ежедневный ПОЛНЫЙ бэкап базы такого размера не то, что хотелось бы делать по многим причинам.
Поэтому логично что переключать каждый раз simple\full не вариант.

Владислав КолосовЕвгений.,

если Вы переключите в simple, то вообще потеряете 24/7 функционал. Бэкапы будут просто мелочью.

Если можно, чуть подробнее поясните что вы имеете ввиду.

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

так переведя в симпл вы тем более не сможете восстановить базу на все время,
что она пребывала в симпле.
но ведь не по этой причине вы не переводите в симпл...

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

Если не заметили, то ответ был на совершенно другой ваш пост, где речь о bulk logged не велась.
А если конкретно, то это ваши слова, а не мои:
для человека, у которого перевод в симпл вызывает лишь "дополнительную возню с бэкапами"

Про " лишний полный бекап " базы в 1Tb который делается каждый день даже не хочется комментировать.

Ну и незачем мне привязывать как будто я настаиваю на переводе базы в simple, если в первом же посте написал что это не желательно.

Владислав КолосовЕвгений.пропущено...


Если можно, чуть подробнее поясните что вы имеете ввиду.

Вы не сможете восстановить состояние базы на любой момент времени. Пока база будет в простой модели вы рискуете потерять все изменения. Плюс простая модель недопустима для реализации высокой доступности, которая требуется для 24/7.

Это все понятно, спасибо.
О переводе в simple написал лишь потому что встречается как решение данной проблемы на просторах интернета, и что бы сразу отсечь подобные советы.

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

Гавриленко говорит: в балк логгед можно часть данных потерять.
еще раз и не буду больше повторять:
а у вас и так допустимо потерять,
иначе о переводе в симпл речи не было бы вообще.
не "нежелательно", а "категорически нет" было бы про простую модель

Я никогда ничего не исключаю как возможность. Кроме восстановления данных средствами бэкапа sql, есть еще несколько вариантов - не хочу вдаваться в детали системы в целом, поэтому допустимо какое то время отсутствие бэкапа, но как и сказал это не желательно.
Вы просто зацепились зачем то к конкретным словам "нежелательно" "категорически нет", а тема поста была совсем в другом.

Владислав Колосов,

Спасибо.
Попробую снова решение от ola hallengren, вероятно с советом о bulk logged, т.к. иначе размер логов превышает возможности по хранению бэкапов.

Yasha123размер логов о бэкапов лога?
в лог уйдет значительно меньше, но в бэкапе лога будут все ваши перестроенные индексы.
неясно, какая связь между логами и "возможностями по хранению бэкапов"?

Связь вероятно такая, что бэкап лога транзакций делается туда же куда и бэкап базы. Если это не очевидно, то извините.
Yasha123у вас бэкапы хранятся на дисках, куда сложены логи баз?
Если уж вы любите докапываться к словам, то куда простите вы складываете логи баз что бы это ни значило в вашей интерпретации?

Yasha123,

Логи, базы и бэкапы на разных дисках.
Просто ваша фраза " куда сложены логи баз " сбивает с толку.

И да, 2 раза написали и это понятно, но я отвечал на ваш же вопрос о связи логов с бэкапами, и ни слова не сказал что что то не понятно про бэкап лога и сам лог в режиме bulk logged.

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

В общем как я понял нет в MSSQL адекватного способа обслуживать большие базы.

Так же интересная статья попалась, в которой немного говорилось по этой теме https://infostart.ru/public/1081863/
Приведу кусок оттуда:
1С просто и незатейливо советует sp_msforeachtable N'DBCC DBREINDEX (''?'')' (или уже есть более свежая статья?). Microsoft настойчиво рекомендует схему "avg_fragmentation_in_percent > 5% and < = 30% - REORGANIZE, > 30% - REBUILD". На infostart.ru светится "10%-30%", "5%-30%".

И практически на всех рабочих серверах, кои мне доводилось видеть - работает то самое 5%-30% - в виде версии от Микрософт, или скрипты шведского товарища Ola Hallengren, или что-то самописное на том же принципе. Реже встречается sp_msforeachtable. На тестовых серверах, как правило, чаще метод "мы забили на регламентное обслуживание"..

Для мелких баз с хорошими технологическими окнами - Ola Hallengren самое то. Работает по ночам, есть не просит.
Но вот для террабайтных высоконагруженных, работающих "24/7/почти 365" - все далеко не так радужно.
Вот с чем сталкивался:
- Само обращение к sys.dm_db_index_physical_stats даже с ''LIMITED'' - весьма неплохо съедает память (буферпул) sql-сервера. Далее возможны задержки непричастных запросов на физ чтениях, пока буферпул опять наполняется "нормальными" данными
- Ошибки вида Msg 9002, The transaction log for database '...' is full due to 'ACTIVE_TRANSACTION'. - иной раз не лезет индекс в данное админом пространство под лог
- Ошибки вида Msg 9002, The transaction log for database '...' is full due to 'LOG_BACKUP' - и, соответственно, реплики на резервных серверах могут становиться сильно неактуальными... Даже без данных завалов из-за массивных переиндексаций может быть отставание реплик в AlwaysOn

- И, самое болезненное - собственно, блокировки на REBUILD. Особенно для редакций Standart - нет with(online = on) и тем более WAIT_AT_LOW_PRIORITY, сам ребилд идет в один поток (и коль заблокирует кого - то надолго). И даже в версии Enterprise с with(online = on) - блокировки все равно в наличии! Будили ночью, любовался. Собственно, в документации оно вполне отражено.

Обычный сценарий блокировок под для Standart - некий кривоватый запрос (даже вне транзакции, с хинтами with(nolock)) блокирует alter index..rebuild , далее alter index блокирует всех. Пробовал бороться с теми самыми "кривоватыми запросами" - легион их. Пробовал писать джоб, убивающий "кривоватые запросы", мешающие переиндексации - работает.. но однажды оно убило весьма важный процесс - отключил. Перенастроил джоб для "самых важных баз", чтобы он убивал саму реиндексацию - аналог WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF ) - работает... но, понятное дело - не идеал.

Т.е. стандартная модель переиндексации понемногу перестает устраивать.
Вот и Brent Ozar еще в 2012 сомневался - а тогда еще не было широкого распространения ssd.
В настоящий момент думаю в сторону "реиндексации того, что в текущий момент сидит в оперативке (в буферпуле) - с целью минимизации размера индекса (и память освободим, и чтений меньше)".. также понемногу задираю пороги ("5-30" веду к "40-50") и отслеживаю изменение скорости типовых запросов - посмотрим, что будет..

Опять-таки, Микрософт тоже ощущает, видимо, данную проблему.. Появление WAIT_AT_LOW_PRIORITY в MS SQL Server 2014 и RESUMABLE=ON в MS SQL Server 2017 как бы намекает. Жаль только, что все это не для standart (enterprise дорогой и в наличии далеко не на всех серверах).

Тему можно закрыть.
Вывод: большая база? - добро пожаловать в оракл
Если я не прав, то с радостью выслушаю как обслуживать большие базы (от 1Tb) не тратя при этом большие деньги на аренду в ДЦ места с 10-ками терабайт.
Модератор: Для сравнения СУБД есть отдельный раздел форума, тут разводить не надо.

komradВладислав КолосовЕвгений.,

Существуют коммерческие приложения разностного хранения резервных копий. По слухам, хорошо экономят место.
подозреваю, что у автора есть неиспользуемый пока резерв в виде backup compression

Подозрения беспочвенны, компрессия включена.

Владислав КолосовЕвгений.,

Существуют коммерческие приложения разностного хранения резервных копий. По слухам, хорошо экономят место.

Используется, отчасти поэтому и не столь критична данная проблема.