Шринки бывают разные, но все они не являются регулярными операциями. По видам:
shrink для журнала транзакций - не нужен, если корректно настроено резервное копирование и обслуживание индексов. Про роль резервного копирования я писал в статье про резервное копирование. А обслуживание индексов, если делать его как в этой статье не приводит к большому росту журналов (если делать штатными средствами, то журналы транзакций будут примерно чуть больше размера базы, но тоже достаточно определённого размера).
shrink для файла данных - не нужен, так как увеличивает фрагментацию индексов.
И обычно сразу после шринка файлу приходится снова расти - это лишние дисковые операции, причем такие операции, которые затормозят какую-то операцию изменения данных (а значит будут подвисания на транзакциях пользователей), да еще и фрагментация файлов на уровне ОС.
Единственное корректное применение шринка (и данных и ЖТ) - это после масштабных преобразований БД: разворачивание из dt, после свёртки, после реструктуризации основного регистра бухгалтерии. А такие операции происходят нечасто, планово и можно в план воткнуть еще пару пунктов (шринки с последующим обслуживанием индексов).
(37) наверно следует рассказать про бэкапы журналов транзакций, после которых СУБД перезаписывает закомитченные данные. Лог условно не растет. Шринк тут вообще не к месту, только показатель отсутствия знаний и обслуживания.
А если говорить про реальную необходимость шринка после масштабных операций, то он должен быть не до нуля, а до некоего стабильного значения размера журнала от бэкапа до бэкапа журнала.
ПС: отдельный привет передам тем слабоумным, кто переключает для усечения журнала базу в сипмл. Вас так много ((((
В интернете можно найти много подобных скриптов (в простейшем виде они даже в документации есть), но мне ни один не подошёл, и поэтому я пользуюсь своим "велосипедом"
(3)
1. Почти нигде нет органичений по длительности и суммарному размеру обработки.
2. Почти нигде нет тестового режима.
3. Часто в скриптах есть странные вещи (то филлфактор не 100, то статистика не пересчитывается при ребилде индекса)
4. Часто в скрипте нет анализа размера таблиц (постоянно переиндексируются мелкие) и заполненности страниц.
5. Часто перестроение индекса отделено от пересчета статистик.
ps: Блин. Заметил, что движок сайта скрипт опять переколбасил (хотя я специально lg и gt ставил). Прикреплю сейчас файликом.
(4) Ясно. По п.1., наверное соглашусь. Я так полагаю, это все имеет место быть когда объемы баз подходят к космическим масштабам? :) П.2 - п.5 я так полагаю, появились из-за п.1.?
Мне чем стандартный план обслуживания нравится: нарисовал все стрелками. Каждую операцию пометил на неудачу по уведомлениям. Если не произошло то- туда, если это - сюда. И забыл про все это дело как страшный сон :) Полагаю, у вас в скриптах тоже все это можно настроить.
(5) Да, движок сайта сожрал половину. Если не получается скачать, то могу выложить/выслать.
(6) Время становится важным даже если суммарный объём баз на сервере 200-300 ГБ. А этот скрипт применялся и для баз 1-2 ТБ (точнее - почти этот, именно этот скрипт написан заново, потому что я там уже не работаю и не стал воровать). Но и дисковое пространство тоже внезапно расходуется при стандартном скрипте:
Файлы данных могут вырасти на размер самой большой таблицы
Файлы журналов при полной модели могут вырасти на 1-2 объёма файлов данных
Файлы журналов при простой модели могут вырасти на размер самого большого индекса в некоторых условиях (но построение индекса - обычно является операцией с минимальным протоколированием, поэтому обычно всё же ЖТ не сильно растёт)
Не забываем про бэкапы журналов и разностные бэкапы - они тоже станут большими после переиндексации
Неприятно прийти утром на работу и внезапно узнать, что из-за переиндексации базы упало всё из-за пересечения с загрузкой данных или съедено 200-500 ГБ на серверах.
Собственно, скрипт сейчас написал именно из-за того, что каждые выходные у наших админов журналы транзакций вырастали до двух размеров баз.
Про запуск из планов обслуживания я написал:
Такой скрипт можно запускать из задания (job) MS SQL Server Agent или из "кирпичика" Execute T-SQL Statement Task в планах обслуживания (кому как удобнее)
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HResult 0x35, уровень 16, состояние 1
Поставщик именованных каналов: Не удалось открыть соединение с SQL Server [53].
Sqlcmd: ошибка - Microsoft SQL Server Native Client 10.0: При установлении соединения с сервером SQL Server произошла ошибка, связанная с сетью или с определенным экземпляром. Сервер не найден или недоступен. Убедитесь, что имя экземпляра указано правильн
о и на сервере SQL Server разрешены удаленные соединения. Дополнительные сведения см. в электронной документации по SQL Server..
Sqlcmd: ошибка - Microsoft SQL Server Native Client 10.0: Время ожидания входа в систему истекло.
NULL
(строк обработано: 6)
Показать
при этом если запускать скрипт вручную то все работает. например вот так
(63) знать бы где и что! Вторые сутки борюсь не могу запустить Агент сервера, говорит, что учетка неизвестная и имя не выводит ее в лог в не зависимости от того что ставлю. А если в Безопасность - Имена входа пытаюсь что то открыть вылетает ошибка про статистику...
Добрый человек... а есть ли у тебя статейка с описанием настроек сервера?
Судя по подходу, есть чем поделиться :)
Статья резервное копирование вообще порадовала своим содержанием!
(13) Нет, такой статьи нет, но скорее потому что универсальных советов очень мало. А объяснение неуниверсальных советов выглядит как 1 строчка кода и 3 страницы объяснения, когда можно её выполнять, а когда не очень.
(15) При ребилде индекса, если не указать "STATISTICS_NORECOMPUTE" статистика по данному индексу пересчитывается сама, и причем "бесплатно": при ребилде всё равно весь индекс считывается. Но если таблица достаточно большая, а изменения в ней не приводят к фатальной фрагментации, то если мы не выполняем полного перестроения индекса (например, обходимся reorganize), то статистика может устареть.
Теоретически можно их вообще не пересчитывать, MS SQL сам их будет пересчитывать (если ему не запретить явно), но делать он это будет во время пользовательской транзакции, когда каждый тик-так на счету. Ну и для больших таблиц (100+ ГБ) с массовыми изменениями замечено, что лучше статистику пересчитывать в режиме full scan (иначе она бывает неактуальна), но это очень медленно.
Можно, конечно, пересчитать статистику отдельно от обслуживания индексов, но при обслуживании индексов как раз "протухает" много планов запросов, они потребуют перекомпиляции, поэтому достаточно разумно сразу после ребилдов/реорганайзов выполнить и sp_updatestats, чтобы "волн" рекомпиляций была одна, а не две.
sp_updatestats обновляет не все статистики, а только устаревшие, так что её вызов сразу после перестроения индексов не приводит к повторному просмотру только что перестроенных индексов, так что тут нет лишних затрат.
если мы не выполняем полного перестроения индекса (например, обходимся reorganize), то статистика может устареть.
Как статистика может устареть от дефрагментации? Ведь статистика это распределение данных в таблице. Грубо говоря если имеем таблицу на 1000 записей, то статистика это информация о том, что в этой таблице 100 записей со значением "Стул", 50 со значением "Стол" и 850 со значением "Кресло".
Если мы сделали дефрагментацию, то данные никуда не делись и их распределение никак не поменялось, количество столов и стульев не изменилось, следовательно статистика осталась актуальной. Может я что-то неправильно понимаю?
2.
при обслуживании индексов как раз "протухает" много планов запросов, они потребуют перекомпиляции
По какой именно причине обслуживание индексов вызывает перекомпиляцию плана запроса?
(24) Andreynikus,
1. Статистика устаревает не от дефрагментации, а от того, что не обновляется. Просто перестроение индекса статистику обновляет, а дефрагментация не трогает. У автоматического обновления есть своя специфика: оно срабатывает, если я правильно помню на 20% обновления таблицы (что очень немало) и может произойти в "неудобный" для системы момент, затормозив работу текущего запроса.
2. Execution Plan Caching and Reuse. При обновлении статистики (т.е. при перестроении индекса, в частности) запросы потребуют перекомпиляции.
speshuric, а можно вот это обосновать "Никаких регулярных "шринков" на рабочих базах быть не должно. Еще раз: шринкам не место в регулярном обслуживании!"
(17) в (2) еще описал же вроде. Откуда им взяться в регулярном обслуживании?
(18) Фишка в том, что планы обслуживания, как описано в той статье выполняются в разы или десятки раз дольше, чем предлагаемый скрипт. И журналы транзакций на перестроении индексов будут расти до размера базы (если полная модель используется).
Спасибо за статью.
Для себя решил сделать планами обслуживания на основе этой статьи (выполняются по ночам), серьезно увеличило скорость формирования отчетов в центральной базе.
(29)
Спасибо за то что поделились опытом, в отличии от технических статей статьи с опытом получаются наиболее концентрированными.
Есть пара вопросов:
Если у вас есть таблицы 100-200 ГБ и больше, то при распараллеливании построения индекса, после перестроения он формально снова может оказаться фрагментированным.
Как вы боретесь с этим? Насколько сильная фрагментация получается?
Судя по
При полной модели восстановления я бы поставил полное резервное копирование после обслуживания индексов.
Как вы боретесь с этим? Насколько сильная фрагментация получается?
Не сильная, не боремся
У вас подобный скрипт выполняется раз в неделю?
Нет, хоть каждый день. Тут скорее "я бы не ставил полное и разностное резервное копирование прямо перед индексированием, а поставил бы сразу после, чтобы при сбое иметь копию сразу после обслуживания, а не трахаться с последующим восстановлением больших журналов транзакций"
(32)
Возможно, дисковая посистема такая, возможно статистик много нагенерировано. Тут надо предметно смотреть.
То же, что и "при маленьких".
Тут нужно умение оценить текущее состояние базы в SQL, оценить степень проблемы, и применить нужное средство в нужное время.
Например, в данной статье рассматривается проблема большой/дефрагментированной таблицы индексов, и смежная проблема статистики. А статистика может быть неактуальна и в маленькой базе.
Другое дело, что чем меньше данных обрабатывается - тем меньше влияние неактуальной статистики, и поэтому её обновлением пренебрегают в таком случае.
(39) AlexO, я имел в виду что автор указывает на доработку скрипта при работе с большими базами.
Он подходит без изменений для большинства баз данных 1С до примерно 0,5-0,7 ТБ (дальше его уже лучше немного доработать, если кому-то интересно/актуально могу пояснить в комментариях).
(40) Eduard66, Скорей всего, речь идет о более мелких выборках при террабайтных базах.
Т.е. нужно разбить блок обрабатываемых данных на пакеты поменьше.
Перестроение — полное построение индекса. При этом обычно станицы становятся максимально плотно забиты данными, а статистика обязательно обновляется (всё равно все данные читать). Обычно данные индексированные данные полностью недоступны при перестроении индекса.
Rebuild index у меня проходит за час, сбор статистики за два. Возникают два вопроса, почему перестройка индекса идет быстрее сбора статистики и можно ли вообще отказаться от сбора статистики и делать по ночам только ребилд индекс?
Спасибо за статью, и за подробные комментарии к вопросам.
Для меня остался невыясненным один вопрос:
Надо ли делать обновление статистики после перестроения индекса? Ведь обновление и так происходит автоматически при ребилде. И если все же надо, то почему?
Еще один вопрос.
Нужно ли производить очистку процедурного КЭШа с помощью DBCC FREEPROCCACHE после обновления статистики?
И после выполнения этого скрипта в частности?
Мир этому дому!
С большим удовольствием прочитал статью и обсуждения - информация полезная особенно для тех, для кого MS SQL - черный ящик. Автору спасибо.
На курсах по оптимизации внимание на индексы и статистику обращали в первую очередь.
Еще page_count надо смотреть. Если меньше 10 или проценты меньше 10, то это нормально. Дефрагментатор, он не мелочится. Для 100% дефрагментации нужно делать rebuild index.
Подскажите, пожалуйста, есть ЗУП КОРП 2,5 база стала сииильно пухнуть, каждый день выполняется переиндексация, обновление статистики. Нашел таблицу в ней всего 2 индекса, но они занимают 45Гб, это как-то не нормально. Что делать то?
(53) Под SQL 2000 не работает: там нет нужных DMV и приходилось подобное делать через кучу приседаний (см. DBCC SHOWCONTIG)
Я бы настоятельно рекомендовал уже уйти с 2000 - слишком много накопилось исправленных проблем. Если речь про 7.7, то это немного сложнее, но эти сложности того стоят. Если речь про 1С8, то безусловно переходить.
(54) речь идет от 7.7. пока на 8 переходить в планах нет совсем, и скуля 2000 лицушная. пока делаем выгрузку/загрузку, надеясь на то что по время этой процедуры все таблицы пересоздаются по новой.
(57) я понял, но как то "секретный релиз" не шибко внушает доверия если честно...а так конечно надо попробовать.
Да и есть ли у Вас мысли, по поводу выгрузки/загрузки при этой процедуре база очищается или данные поверх пишутся ?
(57) подскажите, я сделал свертку базы sql 2008 1c 7.7. Удалял данные прямо из таблиц sql, удалилось несколько миллионов строк из проводок и регистров,но размер базы не изменился. Для сжатия базы запустил отдельно регламентную процедуру на ночь, но размер базы не уменьшился, ужался только лог. Модель базы данных "простая".
Попробовал вашу скрипт. На небольших базах до 100Гб, работает замечательно время обслуживания раза в три уменьшилось. А вот на большой базе 300Гб проблема. Перестроение индексов делается достаточно быстро, а вот обновление статистики занимает часов 15. Опять же скрипт не прекращает работу по таймауту. Не могли бы подсказать куда копать, где может быть проблема?
67.
user645801_yyyuuu123q
24.07.20 02:57 Сейчас в теме
Друзья, всем привет подскажите пожалуйста.
Резервнованое копирование Мне самому настраивать? Проверка целостности тоже не проходит инструкция "DBCC FREEPROCCACHE" не выполняется. Будет ли корректно добавить это все самому? Скажем так сначала целостность базы, потом скрипт потом DBCC FREEPROCCACHE дальше резервное копирование -> очистка журналов ?
Подскажите как этот скрипт настроить из задания Агента, тип указываю сценарий T-SQl? какую указывать базу в настройках задания? И как часто настраивать запуск скрипта ?