Auto-shrink в MS SQL SERVER 2008R2

Почему и чем "опасен" шринк" ("автошринк")???
По умолчанию сервера автосжатие - True, то есть включено по "заводским" настройкам самого майкрософт я так понимаю.... Но при этом всём в "методичках" и "статьях" (я так понимаю, что они официальные, т.к. размещены на оф. сайте) оно типа как бы не рекомендовано, т.к. частое сжатие (частый шринк) приводит к фрагментации данных в файловой системе. (а фрагментация я так понимаю, когда физический порядок страницы не совпадает с логическим, т.е. в новой базе данных этот порядок соблюдается, логический = физическому, но после удаления таблицы он сразу же нарушается???я правильно понимаю?? но не в этом вопрос).
Так вот, после удаления таблиц, место внутри базы освобождается, но на диске база все также осталась ( сиквел сервер не отдает место файловой системе обратно), я применяю шринк (вручную), место освобождается на диске (сиквел сервер отдает это место файловой системе?). И если я все правильно понимаю здесь две стороны:
1. при распухании базы, следует делать шринк (допустим даже вручную), чтобы после удаления данных, вернуть место файловой системе. Это дает плюс "огромный" , ну типа отдает место свободное и база меньше весит, т.е. база не занимает свободное место (место без данных) на диске. Но после шринка появляется фрагментация
2.при определённых действиях база распухает, например при удалении данных, данные внутри базы меньше, но в файловой системе занимает также. Шринк не производится, база распухает, данные весят к примеру 60 гб, а на диске 80гб, 20 гб это свободное место на диске в файловой системе. И данные не займут больше места, пока не займут всё свободное место, т.е. 20 гб.
Я написал много чего как я понимаю и вопросы, на которые хочется получить ответ. Я начинающий АБД. Время чтобы тестить есть, начальство брало меня чтобы выучить как специалиста, который будет все это в будущем контролировать с но правда с постоянным самообучением. Я про себя сказал к слову о том, что много моментов не пойму в силу не понятия слэнга...

Я задался этим вопросом, т.к. у меня база данных: размер 85303,23 МБ и свободно 17759,58 МБ (20,8%). Стоит автошринк, но в вышенаписанном мною я пояснил что не рекомендовано. Вопрос стоит о оптимизации базы , а именно ее размере, попытки ее уменьшить без вреда данным. Может быть сделать перестройку индексов и в дальнейшем отключить автошринк?? Прошу помочь в очередной раз, я не прошу сразу "все карты на стол" и "как все и сразу" сделать, а советов из своего личного опыта и с опытом в применении всех возможных рекомендаций майкрософта в решении такой проблемы или проблем к ним близким.

Добавлено через 1 минуту
Я хоть и без опыта но баз чтобы протестить все варианты хватит.

0

Может быть разрастается лог транзакций?
Такое бывает. Надо смотреть в свойствах БД, что именно растет.
Сталкивался с тем, что база может быть 10 гб, а журнал 100 гб и вырастает за пару месяцев с 0 до 100.

Вообще если настроить план обслуживания БД, то после полного резервного копирования журнал должен усекаться. И места на диске становится больше. Но сталкивался с тем, что он не усекался, тогда приходилось усекать вручную. Для примера не усекался на одной из БД из-за того, что резервное копирование делалось джобом по расписанию, а не планом обслуживания БД.

0

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

0

А, речь про autogrowth... Честно говоря наверно индивидуально надо думать. А если закончится резко? Какими темпами разрастается?

Сообщение от Dima_ABD размер 85303,23 МБ и свободно 17759,58 МБ (20,8%)

На диске я так понимаю, может памяти увеличить дополнительно?

0

Я имел ввиду что база на диске занимает 85303,23 МБ, но из них свободно 17759,58 МБ (20,8%),а данными занято остальные 79,02%). Как избавится от постоянного роста базы на плюс 20 гигов, если он всегда пустые, а только пару мб из них данными заняты.

0

Понял, значит речь о Autogrowth, можно почитать по теме - Change Autogrowth for Database как менять, как проверить на сколько расширяет и т.п.

0

Сообщение от Dima_ABD По умолчанию сервера автосжатие - True, то есть включено по "заводским" настройкам самого майкрософт я так понимаю

Разве? Да вроде нет

Сообщение от Dima_ABD Я задался этим вопросом, т.к. у меня база данных: размер 85303,23 МБ и свободно 17759,58 МБ (20,8%).

20% свободного места это вполне нормально. Если больше 40-50 тогда можно уже подумать о необходимости разового сжатия.

Сообщение от Dima_ABD Может быть сделать перестройку индексов и в дальнейшем отключить автошринк??

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

0

Не трогайте Auto-shrink (особенно на OLTP и если диски SSD), "первая" же модификация DDL/DML разрушит всю построенную после сжатия структуру.

Шринкануть можно и нужно, когда место заканчивается, либо пошла деградация производительности.

0

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

Авто-шринк "опасен" длительными и непредсказуемыми лагами базы данных во время работы.
Т.е. работаете вы, работаете, и тут бац, серверу приспичило шринкнуть базу.
Всё, сидим, курим.

В хорошо спроектированной базе данных - шринк практически не нужен.
Свободное место в файлах базы должно быть всегда.
Но, для того, чтобы все было в равновесии, должно выполняться некоторое количество условий.
Например, в базе не должно быть куч (heap), таблиц без кластерного индекса, куда производится интенсивная вставка/удаление (в этом случае место, относящееся к удаленным строкам не возвращается в общий оборот) [нужно делать периодическое Alter table rebuild]
Фрагментация кластерного индекса (и некластерных индексов) - тоже приводит к распуханию файла БД и т.д.

Но, в принципе - шринк это экстраординарная операция.
А на лог-файле, особенно если бэкап лога делается регулярно - так вообще в целом вредная.
лог-файл при регулярных бэкапах - нормально записывается по кольцу, и его размер это "то что надо для работы".
Кроме того, лог файл - очень болезненно расширяется. Если для файла БД разрешение в ОС Instant File Initialization (см: https://learn.microsoft.com/en... rver-ver16 ) помогает, то в случае лог по прежнему при расширении - заполняется нулями предварительно.
Поэтому сжимать лог имеет смысл только после того, как вы сделали какой-то массовый импорт (в полной модели сохранения), или промодифицировали "по-записи" много[сот]милионную таблицу и т.д.
Короче сделали что-то такое эксклюзивное, которое в обозримом времени не повторится - ну тогда да.

Где-то так, +-, не претендую на истину в последней инстанции.

0

Все обслуживание базы сделано до меня. Оно и понятно база крутится с 2021 года. Но моя задача как АБД в редких случаях научится в различных ситуациях "выкрутится", а в повседневной работе следить за планом обслуживания (доверяй машине, но проверяй как делаются бэкапы, с ошибками/без ошибок, причины ошибок и иное...), за ростом mdf-файла и log-файла, наличие свободного места на диске. Но снова отошел от темы разговора...
Возможно, в силу того, что я самоучка, то меня сложнее понять, чем кажется
Но что я имею и почему я задался этим вопросом.
У меня, повторюсь, база данных: размер 85303,23 МБ и свободно 17759,58 МБ (20,8%), Автошринк,повторюсь, стоит - True. Autogrow стоит - строки - С шагом по 100 %, без ограничений
- журнал- С шагом по 100% до 20097 МБ.
Была попытка установить автоувеличение базы данных немного подругому, в меньшую сторону, то у пользователей вылетала ошибка в 1с.
Журнал не трогал, т.к. он все равно по плану обслуживания каждый 15 минут бэкапится, потом бэкап старшке 12 часов удаляется.
Пытался делать перестройку/реорганизацию индексов БД (правда на тестовой базе данных) добился уменьшения размера базы данных, хоть и тестовая, но с 53-54гб уменьшил до 43гб.
Так вот вопрос мои таков: если я отключу автошринк: то я добьюсь того, что после моего ребилда/реорганизации индексов у меня лог/база распухнет (понятно почему, индексы перестроены/реорганизованы), но сжав вручную я увижу размер базы "натуральный", который после перестройки/реорганизации и который меньше, чем до этих операций. Но при этом самое главное свойство соблюдается, а именно целостность. Так вот, отключив автошринк я, уменьшу в будущем, посредством отключения автошринка (который каждый раз увеличивал фрагментацию индексов на постоянной основе), увеличение фрагментации индексов. Но здесь такая загвоздка, т.к. мне надо на регулярной основе делать ребилд/реорганизацию индексов, то при распухании лога все понятно, он по плану обслуживания станет "таким как надо для работы", но при распухании базы данных (если это конечно так будет и это "не миновать"), мне прозводить шринк вручную....следовательно.... уменьшив фрагментацию индкесов и этим уменьшив базу, меня не будет ждать подводный камень, что когда-нить база руспухнет и мне придется делать шринк и увеличиьь фрагментацию?? Такое чувство, что "качели". Не пойму этого...
Как АБД, что мне делать в этом случае и вообще что делать с базой данных в плане администрирования???Возможно, действия таковы, для решения моих вопросов?:
1.настроить рациональнее план обслуживания ( ну я уже не знаю как лучше делать... последнее что сделал, это применил параметр "Сжимать копии", уменьшив благодаря этому занятость этими же копиями место на диске. Но при этом глубина рез копий как была 7 дней, так и осталось. 7 копий были 490гб, а стали 49гб. Оптимизацию провел
2.отключить автошринк и следить за размеров бд/логом плюс к этому следить за фрагментацией индексов.
3.на регулярной основе проводить фрагментацию индексов (перестройку/реорганизацию), уменьшив базу, но раздув лог.
4.настроить автоувеличение бд и лога, так чтобы не было ошибок со стороны пользователей 1с. Но как это сделать? Тестировать постоянно разный вид роста либо в абсолютных, либо относительных показателях? Либо установить максимальный "фиксирвоанный размер" при достижении которого, снова увеличить размер максимального на глаз?Но как тогда поступать, когда база данных сама по себе резервирует место на диске больше, чем данные занимают, как я уже описывал "размер 85303,23 МБ и свободно 17759,58 МБ (20,8%), а данные - это все остальное место 67 543,65 МБ ".
5.радикально поступить, разбить таблицы по разным файловым группам??? Просто в моем случае архитектуру БД создала 1с, то есть она сама создает таблицы в БД , когда в 1с создают к примеру справочник. Не понятно как тогда действовать.
6.Старые данные запихнуть в другую файловую группу и перенести на другой диск. Но что при этом меняется? Ничего? Доступ все равно будет ко всем файлам и в этом случае бэкап делается снова же всей БД, то есть всех файловых групп сколько их бы не было. То есть как есть так есть? Как были в файловой группе по умолчанию Primary, так и оставить? Просто в статьях и рекомендациях указывают, что не храните данные в файловой группе по умолчанию!!!Но как тогда поступить???Старые данные нельзя удалить, т.к. архитектуру БД строит 1с, а значит нарушится целостность.
7.купить новый диск с миллионом ГБ ? Я упираюсь в бюджет организации.
Я много чего написал в очередной раз, но я не из тех , кто задал вопрос из трех слов и жду ответа в 100 слов. Я общаюсь "сам с собой при решении вопросов" и решил у Вас у всех уточнить. Прошу отнестись с понимаем, я очень дотошен.

0

Сообщение от Dima_ABD У меня, повторюсь, база данных: размер 85303,23 МБ и свободно 17759,58 МБ (20,8%), Автошринк,повторюсь, стоит - True.

Повторюсь - отключите шринк

Сообщение от Dima_ABD Autogrow стоит - строки - С шагом по 100 %, без ограничений - журнал- С шагом по 100% до 20097 МБ. Была попытка установить автоувеличение базы данных немного подругому, в меньшую сторону, то у пользователей вылетала ошибка в 1с.

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

Сообщение от Dima_ABD Журнал не трогал, т.к. он все равно по плану обслуживания каждый 15 минут бэкапится, потом бэкап старшке 12 часов удаляется.

А как часто у вас делаются полные\дифференциальные бэкапы?

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

Зачем вы так фанатично пытаетесь ужать базу? Какую цель вы преследуете? У вас место на дисках заканчивается? Это решается покупкой дисков большего объема. Направьте вашу бурную энергию в более продуктивное русло, например посмотрите этот курс - https://www.youtube.com/playli... lkh-H74juv

1

1.Автошринк отключу.
2.Какие правильные настройки автоувеличения файла данных и журнала транзакций? Прикинуть "на глаз" сколько она в будущем будем занимать места на диске и установить максимальный размер, который рассчитать = реальный объем базы + 20% от общего объема данных и так вручную добавлять каждые 3-4 месяца либо "отследить" как в динамике растёт файл данных и просто посчитать сколько в день, к примеру, растёт файл данных, потом просто умножить на 100-120 дней и получить итоговый размер "в будущем"? Либо какая-то статья есть, рекомендации, про то как, надо сразу установить размер базы + прикинуть сколько будет через полгода/9месяц/год весить?Про журнал транзакций тоже не стоит забывать мне я так понимаю? Даже если он каждые 15 минут бэкапится + бэкап лога старше 12 часов автоматически удаляется. Не стоит забывать, что некоторые операции могут раздуть лог, да? Поэтому стоит также правильно настроить автоувеличение лога? При этом всём, понимая что "в частности"бэкап лога при импорте больших данных распухает, дорогостоящим (в плане ресурсов сервера, производительности, времени) является параметр шага автоувеличния лога???Так как, при нехватке места для лога он будет автоматом увеличиваться (имеется ввиду параметр автоувеличения/максимального размера) и как раз это будет влиять на потребляемые ресурсы сервера, производительность, время отклика,я правильно понимаю? И поэтому стоит верно установить параметр (а именно шаги роста в абсолютных и относительных величинах) автоувеличения/максимального размера, что для файла данных, что для журнала транзакций, верно да?
3.полный бэкап делается каждый день. Последнее что буду использовать в качестве оптимизации - сжатые резервные копии. Ну и журнал транзакций каждый раз после полного бэкапа и через каждые 15 минут бэкапится + бэкап старше 12 часов удаляется.
4. искал варианты оптимизировать рабочую базу данных. Пытался как-нить "ужать" с целью минимизировать расходы на доп. комплектующие в будущем, грубо говоря "выжать все соки" из всех возможных вариантов оптимизации. За курс отдельное спасибо.
Возвращаюсь к вопросу о шринке . Отключу автошринк - избавлюсь от фрагментации страниц в файлах данных. Но при применении одного из немногих вариантов оптимизации рабочей базы данных, а именно перестройка/реорганизация индексов, я раздую лог или раздую рабочую базу данных? Либо и лог, и рабочую базу данных вместе раздую? Либо только лог раздую? И обычным шринком его приведу в "нормальный для работы" вид?Но вопрос противоположений насчёт шринка и насчёт перестройки/реорганизации индексов? непонятки у меня тогдаПоэтому у меня стоял вопрос автошринка и шринка в общем и в вследствие все вытекающие подводные камни и вопросы!

0

Сообщение от Dima_ABD 2.Какие правильные настройки автоувеличения файла данных и журнала транзакций?

Автоувеличение должно быть ФИКСИРОВАННЫМ, а не процентным.
Установите 512 Мб для файла данных и 64 Мб лог-файла.
Если вы выполняете бэкап лога - то лог будет оставаться в пределах, нужных базе для работы, и никак "шринькать" его не надо, это вредно.
Слишком большой прирост лог-файла - не устанавливайте, т.к. при выделении места под лог-файл оно инициализируется нулями, в отличие от mdf, место под которое может просто выделяться, если у учетки, от имени которой стартует сервис MSSQLSERVER есть привилегия Instant File Initialization.
Слишком маленьким значение прироста ldf тоже делать не надо, т.к. лог-файл приобретает излишнюю внутреннюю фрагментацию (см. например http://adventuresinsql.com/200... ging-vlfs/)

Сообщение от Dima_ABD 3.полный бэкап делается каждый день. Последнее что буду использовать в качестве оптимизации - сжатые резервные копии. Ну и журнал транзакций каждый раз после полного бэкапа и через каждые 15 минут бэкапится + бэкап старше 12 часов удаляется.

Бэкап лога используется для того, чтобы иметь возможность восстановить БД на любой момент времени ПОСЛЕ полного бэкапа. Для того, чтобы это сделать, нужно иметь полный бэкап (не обязательно, кстати, последний полный бэкап, а вообще любой) и ВСЕ файлы бэкапов лога, начиная от момента окончания этого полного бэкапа, до момента времени, по состоянию "на когда" вы хотите восстановиться.
Поэтому делать полный бэкап раз в сутки, а бэкапы лога стирать через 12 часов - бессмысленно.
Если вы хотите гарантированно восстановиться на любой момент времени СЕГОДНЯ, вам нужно хранить ПОЛНЫЙ бэкап, сделанный ВЧЕРА + все бэкапы логов, сделанные ВЧЕРА И СЕГОДНЯ.
Если вы предполагаете восстанавливать базу только по состоянию на момент создания полного бэкапа - переведите БД в simple recovery model.

Сообщение от Dima_ABD 4. искал варианты оптимизировать рабочую базу данных. Пытался как-нить "ужать" с целью минимизировать расходы на доп. комплектующие в будущем, грубо говоря "выжать все соки" из всех возможных вариантов оптимизации. За курс отдельное спасибо.

Если есть возможность остановить базу на обслуживание, например на выходные, сделайте всем таблицам Alter table rebuild (это может быть долго, впрочем у вас база всего 85 Гб, это копейки), а потом сожмите базу (прямо в меню SSMS). Так вы получите минимальный размер базы (при некоторых но, например у некоторых ваших индексов fillfactor может быть не 100% задан и т.д.).

Впрочем, я, честно говоря, не понимаю, зачем это сейчас.
Жесткие диски - дешевле грязи, память тоже. Объем базы - мизерный. Если б у вас терабайт 20 было, тогда б надо было подпрыгивать, а так...
(ИМХО, разумеется).

И... Не храните бэкапы рядом с базой.
Не будьте идиотом.
Бэкап должен хранится физически за пределами любой железяки, имеющей отношение к вашей БД.
Не делить с ней СХД, физическую ноду, виртуальный сервер, и т.д.
:-)

0

1.попробую поставить автоувеличение именно такое. Возможно причина того, что база данных сама "захватывает" свободное пространство (место) на диске и данных там как таковых там нет (в занятом базой данных "захваченном месте" свободном месте), кроется именно в неправильной настройке роста файла данных. Что касаемо лога, то ситуация такая, что он до "конских размеров" не растет, то можно также протестировать. Поиграюсь с этим, особенно с размером автоувеличения лога, вплоть до появления ошибки у пользователей. Ну и расти он не будет в принципе, т.к. бэкапами усекается. В общем прослежу.
2. В силу "упрощения" установлен "такой режим" резервного копирования, а именно раз в день бэкап, так 7 раз в неделю и старый бэкап (старше 1 недели) удаляется. Логи бэкапится каждый 15 минут + плюс удаляется бэкап лога старше 12 часов.
Попробую предложить вариант использования 1 полного бэкапа в неделю и ежедневного 15-минутного бэкапа лога. Понятно что "файликов" больше и заморочек как таковых чуть больше так как файлов больше. Но при этом глубина восстановлния возрастёт?? Ну и перевести базу в "Простую моедль восстановления", делая каждый день только бэкап... Ну это конечно проще некуда, но бухгалтерия отдельное государство, могут потребовать восстановить базу на определенный момент времени (если таковой бэкап лога будет в живых, т.е. младше 12 часов при нынешнем режиме работы). Но за советы спасибо в плане этого момента.
3.Планирую вручную на тестовой базе в очередной раз запустить, но при этом хочу почитать-поискать статью по этой теме, разобрать чужой код по перестройке/реорганизации индексов. Чтобы понять все плюсы и недостатки этого всего, чем чревато, чем опасно, что до этой операции делать/не делать , что после перестройки/реорганизации можно/нельзя делать/ не делать.
4. ежедневная копия бд и 15 минутные копии лога попадают сразу на сервев, а потом на схд. Там порядка 36000 ГБ.
Моя главная задача привести базу в оптимальное состояние.

0

Сообщение от Dima_ABD 2. В силу "упрощения" установлен "такой режим" резервного копирования, а именно раз в день бэкап, так 7 раз в неделю и старый бэкап (старше 1 недели) удаляется. Логи бэкапится каждый 15 минут + плюс удаляется бэкап лога старше 12 часов.

В таком случае ваши бэкапы лога - практически бесполезны. Вы сможете их использовать для восстановления только до того момента, пока не будет стерт первый по времени бэкап лога, который был сделан после полного бэкапа.
Если вы, например, начали делать полные бэкап в 01:00, то после 13:00 любого дня вы использовать бэкапы лога для восстановления не сможете.
Еще раз - не сможете НЕ восстановиться, например, на 09:00 или на 15:00, а не сможете использовать ваши бэкапы логов для восстановления вообще. в 13:00 они превратятся в тыкву все сразу.

Сообщение от Dima_ABD Попробую предложить вариант использования 1 полного бэкапа в неделю и ежедневного 15-минутного бэкапа лога. Понятно что "файликов" больше и заморочек как таковых чуть больше так как файлов больше. Но при этом глубина восстановлния возрастёт??

Что значит "глубина восстановления"?
В общепринятой терминологии - "глубина восстановления" - это тот период времени (+условия), по состоянию на который вы можете восстановить данные.
Например, ежегодно, 1 января вы делаете ежегодный бэкап и храните его вечно. И делаете так 10 лет. Глубина восстановления - 10 лет по состоянию на 1 января.
Или, например, вы делаете полный бэкап ежедневно, храните бэкапы неделю, бэкапы лога - каждый час, и храните их 2 недели - глубина восстановления будет 1 неделя (определяется наличием полного бэкапа), но по состоянию на любой момент времени от самого старого бэкапа, возможно, за исключением последнего прошедшего часа. Или вообще на любой момент, включая текущий, если удастся сделать бэкап хвоста лога (tail log). Т.к. вы храните бэкапы лога 2 недели и ОНИ У ВАС ВСЕ ЕСТЬ!
Время восстановления - да, вырастет. Потому что при восстановлении log-а в базу norecovery mssqlserver как бы прокручивает все операции, следы которых остались в логе. И если вы делали много каких то операций, ну, например, многомиллионную таблицу особоизвращенным методом модифицировали (по записи, например), или грузили также - то и лог, и бэкап лога будут иметь огромный размер, и восстанавливаться он будет сутками.

Сообщение от Dima_ABD 3.Планирую вручную на тестовой базе в очередной раз запустить, но при этом хочу почитать-поискать статью по этой теме, разобрать чужой код по перестройке/реорганизации индексов. Чтобы понять все плюсы и недостатки этого всего, чем чревато, чем опасно, что до этой операции делать/не делать , что после перестройки/реорганизации можно/нельзя делать/ не делать.

Индексы перестраивать не надо. И реорганизовывать - тоже не надо :-)
(Сейчас полетят тапки... но не надо).
Это слабо влияет на производительность и размер базы.
Нет, безусловно влияет, иначе б этого не было, но... обычно не стоит возни.
Сильно влияет статистика, и вот ее как раз нужно обновлять регулярно, не отдавая на откуп автообновлению.
Именно от того, что бонусом к rebuild идет обновление статистики - ребилд индексов волшебным образом и увеличивает производительность.
(Хотя да, можно придумать ситуации, когда ребилд индексов нужен и полезен. Но... это примерно как зубра в подмосковном лесу встретить. Они там есть, и много. Правда-правда)
Воевать с фрагментацией индексов - вообще дело бесполезное и практически вредное.

Сообщение от Dima_ABD 4. ежедневная копия бд и 15 минутные копии лога попадают сразу на сервев, а потом на схд. Там порядка 36000 ГБ.

Главное, чтобы основная база не жила на той же СХД.
Иначе накроется СХД - и всё.
Ищем новую работу.
Потерять [все] бэкапы - больно, но не смертельно, и, обычно никак не влияет на бизнес. И у вас есть основная система.
Потерять основную базу - очень неприятно, бизнес может нести какие-то потери от простоя, но, при ваших мизерных объемах поднять 85 Гб из бэкапа - 20 минут делов.
Но потерять и базу, и бэкап... Ну вы поняли.
:-)
(Дисклаймер - всё ИМХО).

0

Сообщение от uaggster Это слабо влияет на производительность и размер базы.

летят тапки

Сообщение от uaggster но... обычно не стоит возни.

тапки

Сообщение от uaggster Воевать с фрагментацией индексов - вообще дело бесполезное и практически вредное.

+1
но именно что воевать
настроил на выходные реиндекс особо важного и мониторь далее

0

Тогда как объяснить вот такой момент...После перестройки/реорганизации индексов размер базы уменьшается (понятное дело что сразу после перестройки делается шринк). Это и есть "волшебное" действие обновления статистики?

0

Сообщение от Dima_ABD После перестройки/реорганизации индексов размер базы уменьшается (понятное дело что сразу после перестройки делается шринк).

Размер базы уменьшается после шринка, а не после реорганизации индексов

Сообщение от Dima_ABD Это и есть "волшебное" действие обновления статистики?

Нет.
В очередной раз порекомендую этот плейлист для ознакомления. Там есть ответы на все ваши вопросы

0

Сообщение от Dima_ABD Тогда как объяснить вот такой момент...После перестройки/реорганизации индексов размер базы уменьшается (понятное дело что сразу после перестройки делается шринк). Это и есть "волшебное" действие обновления статистики?

Присоединяюсь. Вначале - пройдите ликбез.

1. Размер БД не играет [почти] никакой роли. Процессор все равно работает только с данными, которые находятся в ОЗУ.
Данные с диска попадают в память кусками (страницами), и находятся в памяти так долго, как это возможно.
До тех пор, пока место не понадобится под те данные, которые находятся где-то там, на диске, но которые б надо обработать сейчас.
И вытаскивать страницу из 10 Гб файла или из 1000 Гб - один фиг вытаскивать страницу. Одинаковая работа.
Сервер не просматривает файл последовательно, а выцепляет нужный кусок из нужного места. Сразу.
Поэтому гигабайт там будет или терабайт в этом файле - решительно пофиг.
2. Файл БД - довольно сложен по структуре.
Можете представлять его себе как некий виртуальный диск, поверх файла.
У него внутри - все своё. Своя специализированная файловая система, неуниверсальная, поэтому максимально быстрая, свои принципы размещения информации, удаления, сборки мусора, логгирования, транзакций ввода-вывода и т.д. Короче такая суперNTFS поверх NTFS или линуховых FS.
И там все нормально с т.з. использования свободного места, переиспользования высвободившегося и т.д.
Помогать ему обычно - не надо.
Есть исключения, типа интенсивно используемых куч, но это нюансы.
3. Бороться за размер файла - не надо. Если его поведение остается в рамках приличий. Ну, например он не больше хранящихся в нем данных раз эдак в 10.
4. Фрагментация, и, особенно разделение страниц, page split, могут отрицательно влиять на производительность, но не напрямую, а опосредованно. Они снижают количество доступной памяти для сервера.
Происходит это так.
Вот у вас есть таблица. В ней есть поля переменной длины, типа varchar или varbinary. Вы все это плотно упаковали, сделав Alter table rebuild. Теперь все хранится в минимально возможном объеме на диске, в соответствии с fillfactor для кластерного индекса, обычно по умолчанию 100%. Для кучи - так точно 100%.
Теперь вы делаете update строки, причем в ваше поле varchar записываете значение большей длины. Можете? Конечно!
И теперь страничка на диск в прежнем виде помещена быть не может. varchar то больше стал занимать!
Что делает сервер? Он разрезает страницу. Добавляет еще такую же. И в нее выносит данные изменившейся строки.
Вместо одной страницы получается две, и обе заполнены только частично.
От этого свежесжатый файл - пухнет.
Это катастрофа?
Нет, только мелкая неприятность.
Чем неприятно:
Сервер таскает данные в память с диска в неизменном виде. Т.е. если на диске лежит страница, на которой чуть менее, чем все данные помечены как удаленные - он все это потащит в память и разместит в памяти. В т.ч. и бесполезные куски.
Кроме того, если он раньше тащил одну страницу (1 дисковая операция), то теперь потащит две, и, соответственно эффективная скорость диска сократится.
Соответственно, если у вас в базе все таблицы порезаны в кружева - ОЗУ будет использоваться неэффективно, и диск будет молотить впустую.
Это резон, почему иногда, время от времени, ребилд индексов - все же делать надо.
НО!
В майкрософт, а изначально - в сабейз не дураки сидят.
И дырки в страницах - повторно используются. При этом, правда, растет фрагментация. Это когда данные хранятся в таблицах "не подряд", и для шпинделей это имеет значение, т.к. чтобы считать следующие данные нужно механически подвинуть коромысло на другую дорожку, а не читать все подряд на одной.
НО! Во первых, есть контроллер, с его кэшем, который призван нивелировать это дело, а во-вторых, для ССД - это вообще не играет роли.
И вообще, все устроено так, что для того, чтобы фрагментация у тебя выросла до 30 или более %, когда она начинает хоть как-то влиять на производительность - нужно очень постараться.
Хотя дураки встречаются способы есть, безусловно. Например - кластерный индекс по гуид, причем не по последовательному, а по натуральному гуид. Полный аллес.

Ну, и, самое главное - учет всех этих нюансов, что называется, не вашего ума дело (это шутка).
Заниматься филигранной резьбой по жести приходится, например, когда у вас база на пару терабайт, и ОЗУ гигабайта 32. И даже наиболее используемые индексы в главных таблицах в память не помещаются. PLE уровне эдак 10. А клиент хочет, чтобы это работало.
Вот тут всё вспомнишь, и обо всё постучишься.

А вы - занимаетесь ерундой.
Всё, что может быть решено механическим наращиванием ресурсов - так и должно решаться. Если оптимизация приводит к уменьшению отклика на 1 секунду, при отклике приложения в 10 секунд - в жопу такую оптимизацию.
И даже если считать будет на час меньше, а у вас запрос длится сутки - тоже в жопу.
Минимизироваться должны твои усилия, а не железяки.
:-)
Как то так.

0

Сообщение от uaggster Сервер таскает данные в память с диска в неизменном виде. Т.е. если на диске лежит страница, на которой чуть менее, чем все данные помечены как удаленные - он все это потащит в память и разместит в памяти. В т.ч. и бесполезные куски. Кроме того, если он раньше тащил одну страницу (1 дисковая операция), то теперь потащит две, и, соответственно эффективная скорость диска сократится.

Помимо этого и на планы есть косвенное влияние. Оптимизатор напрямую фрагментацию не видит, разумеется, но зато видит количество страниц. Так что, теоретически, где-то и tipping point может сместиться и пойдут сканы вместо сиков или стоимость запроса перевалит за cost threshold и параллелизм пойдет там, где в общем-то, и не нужно... Так что:

Сообщение от uaggster время от времени, ребилд индексов - все же делать надо.

0