Обслуживание индексов и статистик MS SQL Server

06.02.14

Разработка - Инструментарий разработчика

Готовый и эффективный скрипт для регулярного обслуживания индексов и статистик.

Скачать исходный код

Наименование Файл Версия Размер
Обслуживание индексов.sql
.sql 9,58Kb
541
.sql 9,58Kb 541 Скачать

 

Обслуживание индексов и статистик MS SQL Server

Эта статья написана для администраторов, обслуживающих сервера СУБД MS SQL Server, которые используются вместе с 1С:Предприятием. Статья скорее практическая, чем разъяснительная, но я постарался хотя бы кратко обосновать те или иные решения, хотя большая часть информации дана несколько упрощенно и поверхностно.

Индексы и статистики в MS SQL Server — основа эффективного выполнения запросов. Без них сервер не сможет выполнять запросы за разумное время.

Статистика — небольшая таблица, до 200 строк, в которой хранится обобщенная информация о том, какие значения и как часто встречаются в таблице. На основании статистики сервер принимает решение, какой индекс использовать при выполнении запроса.

Индекс — особым образом структурированные данные (хранящиеся в базе данных), которые позволяют быстро найти нужные записи. Устроен он примерно так, как оглавление в книге или предметный указатель. Большинство баз данных 1С по объёму более чем наполовину состоят из индексов. Для каждого индекса обязательно хранится его статистика.

За подробностями внутреннего устройства, как обычно отсылаю в BOL:

В целом MS SQL Server сам справляется с поддержкой целостности и эффективности статистик и индексов, но если никак ему не помогать, то постепенно накапливаются следующие проблемы:

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

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

Стоит учесть, что 1C для облегчения переносимости архитектуры между разными видами СУБД использует лишь небольшую часть современных возможностей индексирования MS SQL Server. За счет этого обслуживание индексов и статистик несколько упрощается.

Итак, что такое это "обслуживание"? Всё просто.

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

Для обслуживания есть специальные "кирпичики" в планах обслуживания (maitenance plan), которые так и называются:

  • Update Statistics Task
  • Rebuild Index Task
  • Reorganize Index Task

Казалось бы всё просто: накидал кирпичиков, соединил стрелочкой и поехали. Такое решение возможно, но оно очень неэффективно:

  1. Индексы перестраиваются/реорганизуются только все сразу в данной базе. То есть даже если таблица никогда не меняется, её индексы будут перестраиваться. Это очень расточительно, а при полной модели восстановления еще и приводит к огромному росту журналов транзакций.
  2. Статистики тоже перестраиваются вне зависимости от актуальности, причем даже если они были только что обновлены при перестроении индексов.
  3. Нет никаких гарантий, что операция обслуживания завершится за то время, которое вы ей выделили.

Решение очень простое: пишется свой скрипт обслуживания, который убирает эти ограничения. Такой скрипт можно запускать из задания (job) MS SQL Server Agent или из "кирпичика" Execute T-SQL Statement Task в планах обслуживания (кому как удобнее). В интернете можно найти много подобных скриптов (в простейшем виде они даже в документации есть), но мне ни один не подошёл, и поэтому я пользуюсь своим "велосипедом". Этот скрипт и приведён ниже. Он подходит без изменений для большинства баз данных 1С до примерно 0,5-0,7 ТБ (дальше его уже лучше немного доработать, если кому-то интересно/актуально могу пояснить в комментариях).

Особенности скрипта:

  1. Как и в большинстве подобных скриптов, анализируется динамическое представление sys.dm_db_index_physical_stats, по которому выясняется степень фрагментации и заполненности страниц индекса.
  2. Можно задать для обработки лишь часть баз данных, можно, наоборот, исключить некоторые БД из обслуживания.
  3. Контролируется время выполнения скрипта.
  4. Очень грубо, но оценивается размер записи в журналы транзакций.
  5. Есть возможность исключить из обработки совсем небольшие таблицы.
  6. У скрипта есть режим "эмуляции" работы, чтобы оценить то, как он будет работать.
  7. Сначала обрабатываются самые большие таблицы (так как обычно их обслуживание важнее).
  8. Скрипт работает на SQL Server 2008 и более поздних (на 2005 тоже должен работать, но мне уже негде проверить)
  9. Результат вывода в режиме эмуляции сам является корректным TSQL скриптом.
  10. Ну и конечно, при регулярном выполнении этот скрипт на порядок легче, чем стандартные операции плана обслуживания.

С чем нужно быть осторожным при запуске скрипта:

  1. Нежелательно пересечение работы скрипта с интенсивной работой пользователей или с полным резервным копированием.
  2. Чтение из sys.dm_db_index_physical_stats в режиме DETAILED достаточно интенсивно читает с дисков.
  3. Скрипт предназначен для баз 1С или подобных. Не стоит экспериментировать с ним на совсем специфичных базах данных с нестандартными индексами.
  4. Если у вас есть таблицы 100-200 ГБ и больше, то при распараллеливании построения индекса, после перестроения он формально снова может оказаться фрагментированным.
  5. Статистики пересчитываются без полного сканирования. Это заметно быстрее. Если вам нужно полное сканирование каких-то таблиц, то пишите отдельный скрипт.

Рекомендации по запуску:

  1. Никаких регулярных "шринков" на рабочих базах быть не должно. Еще раз: шринкам не место в регулярном обслуживании!
  2. При полной модели восстановления я бы поставил полное резервное копирование после обслуживания индексов. Иначе при необходимости восстановления придётся донакатывать достаточно тяжёлый кусок журналов транзакций после восстановления основного образа. Простую модель восстановления на промышленно используемых БД я считаю либо редким исключением, либо частым недоразумением.
  3. Первый запуск лучше выполнить вручную в SSMS чтобы оценить время работы.

Остальное можно прочитать в коде и в комментариях.

PS: Движок сайта некорректно отобажает текст со знаками больше-меньше, поэтому скрипт приложен файлом, а в статье оставлено только начало скрипта.

 

-- Параметры скрипта
declare @database_names as nvarchar(max) = N''; -- имена баз задавать через запятую, если не заданы, то все несистемные базы
                                          -- пока парсер примитивный - строка просто делится по запятым и обрезаются крайние пробелы
                                          -- (если в имени базы будет запятая или в начале или конце имени пробел, то система не работает)
                                          -- если указано "-ИмяБазы", то база будет исключена, 
declare @index_size_threshhold as int = 1024;   -- минимальный размер в КБ для перестраиваемого индекса. Нет смысла перестраивать индексы на десяток страниц
declare @index_rebuild_threshhold as numeric(5,2) = 25; -- показатель фрагментации, начиная с которого происходит перестроение индекса
declare @index_defrag_threshhold as numeric(5,2) = 12;  -- показатель фрагментации, начиная с которого происходит дефрагментация индекса
declare @index_rebuild_space_used_threshhold as numeric(5,2) = 50; -- процент заполненности страниц меньше которого требуется перестроение индекса
declare @timeout as int = 7200; -- максимальное время работы скрипта
declare @max_size as bigint = 536870912; -- максимальный суммарный обрабатываемый размер в КБ (чтобы не нагенерировать логов на терабайты) -- 512*1024*1024 КБ = 0,5 ТБ
declare @is_emulate as bit = 1; -- 0 - выполнять, 1 - только вывести команды

См. также

Infostart Toolkit: Инструменты разработчика 1С 8.3 на управляемых формах

Инструментарий разработчика Роли и права Запросы СКД Платформа 1С v8.3 Управляемые формы Запросы Система компоновки данных Конфигурации 1cv8 Платные (руб)

Набор инструментов программиста и специалиста 1С для всех конфигураций на управляемых формах. В состав входят инструменты: Консоль запросов, Консоль СКД, Консоль кода, Редактор объекта, Анализ прав доступа, Метаданные, Поиск ссылок, Сравнение объектов, Все функции, Подписки на события и др. Редактор запросов и кода с раскраской и контекстной подсказкой. Доработанный конструктор запросов тонкого клиента. Продукт хорошо оптимизирован и обладает самым широким функционалом среди всех инструментов, представленных на рынке.

10000 руб.

02.09.2020    130066    702    390    

753

Infostart PrintWizard - создание и редактирование печатных форм в 1С 8.3

Пакетная печать Печатные формы Инструментарий разработчика Платформа 1С v8.3 Запросы 1С:Зарплата и кадры бюджетного учреждения 1С:Конвертация данных 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 Платные (руб)

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

18000 руб.

06.10.2023    9122    26    6    

49

Infostart УДиФ: Управление данными и формами 1С

Инструменты администратора БД Инструментарий разработчика Роли и права Платформа 1С v8.3 Конфигурации 1cv8 Россия Платные (руб)

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

10000 руб.

10.11.2023    5279    14    2    

39

SALE! %

PowerTools

Инструментарий разработчика Инструменты администратора БД Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Россия Платные (руб)

Универсальный инструмент программиста для администрирования конфигураций. Сборник наиболее часто используемых обработок под единым интерфейсом.

3600 2880 руб.

14.01.2013    179750    1092    0    

868

OneDebugger - инструмент для отладки кода без использования режима конфигуратора

Инструментарий разработчика Платформа 1С v8.3 Платные (руб)

Инструмент для написания и отладки кода в режиме «1С:Предприятие». Представляет собой консоль кода с возможностью пошаговой отладки, просмотра значений переменных любых типов, использования процедур и функций, просмотра стека вызовов, вычисления произвольных выражений на встроенном языке в контексте точки останова, синтаксического контроля и остановки по ошибке. В консоли используется удобный редактор кода с подсветкой, контекстной подсказкой, возможностью вызова конструкторов запроса и форматной строки.

5400 руб.

17.05.2024    5059    7    12    

36

Бустер Конвертации данных 3 (Infostart Toolkit)

Инструментарий разработчика 8.3.14 1С:Конвертация данных Россия Платные (руб)

Расширение для конфигурации “Конвертация данных 3”. Добавляет подсветку синтаксиса, детальную контекстную подсказку, глобальный поиск по коду.

15000 руб.

07.10.2021    15365    3    12    

38

Многопоточность. Универсальный «Менеджер потоков» 2.1

Инструментарий разработчика Платформа 1С v8.3 Конфигурации 1cv8 Россия Платные (руб)

Восстановление партий или взаиморасчетов, расчет зарплаты, пакетное формирование документов или отчетов - теперь все это стало доступнее. * Есть желание повысить скорость работы медленных алгоритмов! Но... * Нет времени думать о реализации многопоточности? * о запуске и остановке потоков? * о поддержании потоков в рабочем состоянии? * о передаче данных в потоки и как получить ответ из потока? * об организации последовательности? Тогда ЭТО - то что надо!!!

5000 руб.

07.02.2018    100288    240    97    

299

1С HTML Шаблоны / HTML Templates

Инструментарий разработчика Платформа 1С v8.3 Конфигурации 1cv8 Платные (руб)

Быстрая и удобная обработка для работы с шаблонами HTML. Позволяет легко и быстро формировать код HTML.

2040 руб.

27.12.2017    28678    4    10    

17
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. xten 47 06.02.14 10:58 Сейчас в теме
А почему не должно быть шринков?
2. speshuric 1331 06.02.14 12:07 Сейчас в теме
Шринки бывают разные, но все они не являются регулярными операциями. По видам:
  • shrink для журнала транзакций - не нужен, если корректно настроено резервное копирование и обслуживание индексов. Про роль резервного копирования я писал в статье про резервное копирование. А обслуживание индексов, если делать его как в этой статье не приводит к большому росту журналов (если делать штатными средствами, то журналы транзакций будут примерно чуть больше размера базы, но тоже достаточно определённого размера).
  • shrink для файла данных - не нужен, так как увеличивает фрагментацию индексов.
И обычно сразу после шринка файлу приходится снова расти - это лишние дисковые операции, причем такие операции, которые затормозят какую-то операцию изменения данных (а значит будут подвисания на транзакциях пользователей), да еще и фрагментация файлов на уровне ОС.

Единственное корректное применение шринка (и данных и ЖТ) - это после масштабных преобразований БД: разворачивание из dt, после свёртки, после реструктуризации основного регистра бухгалтерии. А такие операции происходят нечасто, планово и можно в план воткнуть еще пару пунктов (шринки с последующим обслуживанием индексов).
37. AlexO 135 26.01.15 10:39 Сейчас в теме
(2)
shrink для журнала транзакций - не нужен
Особенно "не нужен", когда появляется ошибка "Log is full", и её надо срочно исправить.
61. nvv1970 06.08.18 08:46 Сейчас в теме
(37) наверно следует рассказать про бэкапы журналов транзакций, после которых СУБД перезаписывает закомитченные данные. Лог условно не растет. Шринк тут вообще не к месту, только показатель отсутствия знаний и обслуживания.
А если говорить про реальную необходимость шринка после масштабных операций, то он должен быть не до нуля, а до некоего стабильного значения размера журнала от бэкапа до бэкапа журнала.
ПС: отдельный привет передам тем слабоумным, кто переключает для усечения журнала базу в сипмл. Вас так много ((((
3. Новиков 292 06.02.14 12:42 Сейчас в теме
В интернете можно найти много подобных скриптов (в простейшем виде они даже в документации есть), но мне ни один не подошёл, и поэтому я пользуюсь своим "велосипедом"


А почему?
4. speshuric 1331 06.02.14 12:55 Сейчас в теме
(3)
1. Почти нигде нет органичений по длительности и суммарному размеру обработки.
2. Почти нигде нет тестового режима.
3. Часто в скриптах есть странные вещи (то филлфактор не 100, то статистика не пересчитывается при ребилде индекса)
4. Часто в скрипте нет анализа размера таблиц (постоянно переиндексируются мелкие) и заполненности страниц.
5. Часто перестроение индекса отделено от пересчета статистик.

ps: Блин. Заметил, что движок сайта скрипт опять переколбасил (хотя я специально lg и gt ставил). Прикреплю сейчас файликом.
6. Новиков 292 06.02.14 14:54 Сейчас в теме
(4) Ясно. По п.1., наверное соглашусь. Я так полагаю, это все имеет место быть когда объемы баз подходят к космическим масштабам? :) П.2 - п.5 я так полагаю, появились из-за п.1.?

Мне чем стандартный план обслуживания нравится: нарисовал все стрелками. Каждую операцию пометил на неудачу по уведомлениям. Если не произошло то- туда, если это - сюда. И забыл про все это дело как страшный сон :) Полагаю, у вас в скриптах тоже все это можно настроить.
5. rumik007 06.02.14 14:51 Сейчас в теме
скрипт скопировал, но он не до конца получился? не могу проверить в sql 2005

а в 2005 sql надо будет немного переделать декларацию переменных, т.е.

БЫЛО: declare @database_names as nvarchar(max) = N'Analyzerworkstm';

СТАЛО: declare @database_names as nvarchar(max);
set @database_names = N'Analyzerworkstm';
7. speshuric 1331 06.02.14 15:19 Сейчас в теме
(5) Да, движок сайта сожрал половину. Если не получается скачать, то могу выложить/выслать.
(6) Время становится важным даже если суммарный объём баз на сервере 200-300 ГБ. А этот скрипт применялся и для баз 1-2 ТБ (точнее - почти этот, именно этот скрипт написан заново, потому что я там уже не работаю и не стал воровать). Но и дисковое пространство тоже внезапно расходуется при стандартном скрипте:
  • Файлы данных могут вырасти на размер самой большой таблицы
  • Файлы журналов при полной модели могут вырасти на 1-2 объёма файлов данных
  • Файлы журналов при простой модели могут вырасти на размер самого большого индекса в некоторых условиях (но построение индекса - обычно является операцией с минимальным протоколированием, поэтому обычно всё же ЖТ не сильно растёт)
  • Не забываем про бэкапы журналов и разностные бэкапы - они тоже станут большими после переиндексации

Неприятно прийти утром на работу и внезапно узнать, что из-за переиндексации базы упало всё из-за пересечения с загрузкой данных или съедено 200-500 ГБ на серверах.
Собственно, скрипт сейчас написал именно из-за того, что каждые выходные у наших админов журналы транзакций вырастали до двух размеров баз.

Про запуск из планов обслуживания я написал:
Такой скрипт можно запускать из задания (job) MS SQL Server Agent или из "кирпичика" Execute T-SQL Statement Task в планах обслуживания (кому как удобнее)

8. rumik007 06.02.14 15:37 Сейчас в теме
(7) не вижу, где скачать. Мона в личку скинуть
9. speshuric 1331 06.02.14 15:46 Сейчас в теме
user1730557; tuzmich007; user1628996; BoBaH; TormDV; SeTIrk; plevakin; vulli; Talim; portorico; mirco; TempAvtoteh; TeMochkiN; Batman; user774630; sanjabor; Mr Roudyk; michmich; antonio_i; megodeath; Apparix; wolovits; nikcorn; sacred; wbazil; xepsan; AlX0id; +27 Ответить
10. rumik007 06.02.14 16:08 Сейчас в теме
30. pavlo 27.05.14 07:52 Сейчас в теме
(9) Ссылка дохлая на гуглдокс или у меня не пашет? можно куда нить выложить?
11. anig99 2844 08.02.14 02:22 Сейчас в теме
Я пользуюсь вот этим
http://ola.hallengren.com/downloads.html

Первым на скачивание как раз идет скрипт, который создает все нужные jobs
cleaner_it; leonidol; John_Dow; antonio_i; script; sacred; xzorkiix; JohnyDeath; speshuric; +9 Ответить
12. speshuric 1331 08.02.14 20:42 Сейчас в теме
(11) Добротный комбайн. Чересчур униваерсален на мой вкус, но добротный.
23. xzorkiix 34 14.03.14 09:12 Сейчас в теме
(11) anig99, поддержу http://ola.hallengren.com/downloads.html отлично себя оправдывает.
62. nick_e 2 12.06.19 08:35 Сейчас в теме
(11) посмотрел по ссылке https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html скрипты о которых Вы упомянули, не работают почему то они из агента...

пробовал вот так запустить, чтобы увидеть в чем проблема

exec master..xp_cmdshell 'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = ''SYSTEM_DATABASES'', @LogToTable = ''Y''" -b'


получил ответ


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)

Показать


при этом если запускать скрипт вручную то все работает. например вот так

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30
63. Fox-trot 158 12.06.19 09:01 Сейчас в теме
64. nick_e 2 12.06.19 11:51 Сейчас в теме
(63) знать бы где и что! Вторые сутки борюсь не могу запустить Агент сервера, говорит, что учетка неизвестная и имя не выводит ее в лог в не зависимости от того что ставлю. А если в Безопасность - Имена входа пытаюсь что то открыть вылетает ошибка про статистику...
13. dock 44 12.02.14 08:42 Сейчас в теме
Добрый человек... а есть ли у тебя статейка с описанием настроек сервера?
Судя по подходу, есть чем поделиться :)
Статья резервное копирование вообще порадовала своим содержанием!
14. speshuric 1331 12.02.14 08:51 Сейчас в теме
(13) Нет, такой статьи нет, но скорее потому что универсальных советов очень мало. А объяснение неуниверсальных советов выглядит как 1 строчка кода и 3 страницы объяснения, когда можно её выполнять, а когда не очень.
cleaner_it; Aule2; +2 Ответить
15. Andreynikus 1365 12.02.14 12:29 Сейчас в теме
А зачем собственно пересчитывать статистику при ребилде индекса?
TSSV; Savosin; vitaminpro; alexscamp; alk; dmpas; Sergey.Noskov; +7 Ответить
16. speshuric 1331 12.02.14 13:02 Сейчас в теме
(15) При ребилде индекса, если не указать "STATISTICS_NORECOMPUTE" статистика по данному индексу пересчитывается сама, и причем "бесплатно": при ребилде всё равно весь индекс считывается. Но если таблица достаточно большая, а изменения в ней не приводят к фатальной фрагментации, то если мы не выполняем полного перестроения индекса (например, обходимся reorganize), то статистика может устареть.
Теоретически можно их вообще не пересчитывать, MS SQL сам их будет пересчитывать (если ему не запретить явно), но делать он это будет во время пользовательской транзакции, когда каждый тик-так на счету. Ну и для больших таблиц (100+ ГБ) с массовыми изменениями замечено, что лучше статистику пересчитывать в режиме full scan (иначе она бывает неактуальна), но это очень медленно.
Можно, конечно, пересчитать статистику отдельно от обслуживания индексов, но при обслуживании индексов как раз "протухает" много планов запросов, они потребуют перекомпиляции, поэтому достаточно разумно сразу после ребилдов/реорганайзов выполнить и sp_updatestats, чтобы "волн" рекомпиляций была одна, а не две.
sp_updatestats обновляет не все статистики, а только устаревшие, так что её вызов сразу после перестроения индексов не приводит к повторному просмотру только что перестроенных индексов, так что тут нет лишних затрат.
24. Andreynikus 1365 27.03.14 13:28 Сейчас в теме
(16)
Мне не понятно 2 момента:
1.
если мы не выполняем полного перестроения индекса (например, обходимся reorganize), то статистика может устареть.

Как статистика может устареть от дефрагментации? Ведь статистика это распределение данных в таблице. Грубо говоря если имеем таблицу на 1000 записей, то статистика это информация о том, что в этой таблице 100 записей со значением "Стул", 50 со значением "Стол" и 850 со значением "Кресло".
Если мы сделали дефрагментацию, то данные никуда не делись и их распределение никак не поменялось, количество столов и стульев не изменилось, следовательно статистика осталась актуальной. Может я что-то неправильно понимаю?

2.
при обслуживании индексов как раз "протухает" много планов запросов, они потребуют перекомпиляции

По какой именно причине обслуживание индексов вызывает перекомпиляцию плана запроса?
25. speshuric 1331 28.03.14 15:50 Сейчас в теме
(24) Andreynikus,
1. Статистика устаревает не от дефрагментации, а от того, что не обновляется. Просто перестроение индекса статистику обновляет, а дефрагментация не трогает. У автоматического обновления есть своя специфика: оно срабатывает, если я правильно помню на 20% обновления таблицы (что очень немало) и может произойти в "неудобный" для системы момент, затормозив работу текущего запроса.
2. Execution Plan Caching and Reuse. При обновлении статистики (т.е. при перестроении индекса, в частности) запросы потребуют перекомпиляции.
17. dumsik 35 12.02.14 16:39 Сейчас в теме
speshuric, а можно вот это обосновать "Никаких регулярных "шринков" на рабочих базах быть не должно. Еще раз: шринкам не место в регулярном обслуживании!"
20. speshuric 1331 12.02.14 17:08 Сейчас в теме
(17) в (2) еще описал же вроде. Откуда им взяться в регулярном обслуживании?
(18) Фишка в том, что планы обслуживания, как описано в той статье выполняются в разы или десятки раз дольше, чем предлагаемый скрипт. И журналы транзакций на перестроении индексов будут расти до размера базы (если полная модель используется).
18. Созинов 12.02.14 16:49 Сейчас в теме
Спасибо за статью.
Для себя решил сделать планами обслуживания на основе этой статьи (выполняются по ночам), серьезно увеличило скорость формирования отчетов в центральной базе.
dexxxqqq; +1 Ответить
19. dumsik 35 12.02.14 16:57 Сейчас в теме
21. ITEkb 26.02.14 08:26 Сейчас в теме
Сколько тут всего полезного.. админ не знал, подсунул для изучения.
Спасибо!
22. speshuric 1331 26.02.14 09:14 Сейчас в теме
(21) Пожалуйста, для подсовывания админам и написано :)
26. Region102 31.03.14 07:19 Сейчас в теме
У меня каждые 15 минут логи бэкапятся и шринкуются, это правильно?
27. speshuric 1331 10.04.14 22:29 Сейчас в теме
(26) (Был в отпуске) Бэкапятся - правильно. Шринкуются - неправильно.
28. baa50 12 11.04.14 00:46 Сейчас в теме
(27) объясните почему Шринкуются - неправильно?
29. speshuric 1331 11.04.14 01:03 Сейчас в теме
(27) В целом описано в комментарии (2). Если не согласны, могу подробнее и с цифрами.
31. VVi3ard 52 07.07.14 17:01 Сейчас в теме
(29)
Спасибо за то что поделились опытом, в отличии от технических статей статьи с опытом получаются наиболее концентрированными.
Есть пара вопросов:

Если у вас есть таблицы 100-200 ГБ и больше, то при распараллеливании построения индекса, после перестроения он формально снова может оказаться фрагментированным.


Как вы боретесь с этим? Насколько сильная фрагментация получается?

Судя по
При полной модели восстановления я бы поставил полное резервное копирование после обслуживания индексов.

У вас подобный скрипт выполняется раз в неделю?
33. speshuric 1331 11.07.14 00:08 Сейчас в теме
(31)
Как вы боретесь с этим? Насколько сильная фрагментация получается?

Не сильная, не боремся
У вас подобный скрипт выполняется раз в неделю?

Нет, хоть каждый день. Тут скорее "я бы не ставил полное и разностное резервное копирование прямо перед индексированием, а поставил бы сразу после, чтобы при сбое иметь копию сразу после обслуживания, а не трахаться с последующим восстановлением больших журналов транзакций"
(32)
Возможно, дисковая посистема такая, возможно статистик много нагенерировано. Тут надо предметно смотреть.

38. Eduard66 03.02.15 10:05 Сейчас в теме
(33) А что нужно дорабатывать при больших базах?
Какие особенности при администрировании?
39. AlexO 135 03.02.15 10:29 Сейчас в теме
(38) Eduard66,
А что нужно дорабатывать при больших базах?
То же, что и "при маленьких".
Тут нужно умение оценить текущее состояние базы в SQL, оценить степень проблемы, и применить нужное средство в нужное время.
Например, в данной статье рассматривается проблема большой/дефрагментированной таблицы индексов, и смежная проблема статистики. А статистика может быть неактуальна и в маленькой базе.
Другое дело, что чем меньше данных обрабатывается - тем меньше влияние неактуальной статистики, и поэтому её обновлением пренебрегают в таком случае.
40. Eduard66 03.02.15 10:48 Сейчас в теме
(39) AlexO, я имел в виду что автор указывает на доработку скрипта при работе с большими базами.
Он подходит без изменений для большинства баз данных 1С до примерно 0,5-0,7 ТБ (дальше его уже лучше немного доработать, если кому-то интересно/актуально могу пояснить в комментариях).

Вопрос собственно про это
41. AlexO 135 03.02.15 10:50 Сейчас в теме
(40) Eduard66, Скорей всего, речь идет о более мелких выборках при террабайтных базах.
Т.е. нужно разбить блок обрабатываемых данных на пакеты поменьше.
32. Painted 49 08.07.14 08:15 Сейчас в теме
Перестроение — полное построение индекса. При этом обычно станицы становятся максимально плотно забиты данными, а статистика обязательно обновляется (всё равно все данные читать). Обычно данные индексированные данные полностью недоступны при перестроении индекса.
Rebuild index у меня проходит за час, сбор статистики за два. Возникают два вопроса, почему перестройка индекса идет быстрее сбора статистики и можно ли вообще отказаться от сбора статистики и делать по ночам только ребилд индекс?
34. Tavalik 3368 23.07.14 10:19 Сейчас в теме
Спасибо за статью, и за подробные комментарии к вопросам.

Для меня остался невыясненным один вопрос:
Надо ли делать обновление статистики после перестроения индекса? Ведь обновление и так происходит автоматически при ребилде. И если все же надо, то почему?
35. NNomad 17.09.14 11:36 Сейчас в теме
Еще один вопрос.
Нужно ли производить очистку процедурного КЭШа с помощью DBCC FREEPROCCACHE после обновления статистики?
И после выполнения этого скрипта в частности?
36. LexSeIch 211 22.01.15 07:31 Сейчас в теме
Мир этому дому!
С большим удовольствием прочитал статью и обсуждения - информация полезная особенно для тех, для кого MS SQL - черный ящик. Автору спасибо.
На курсах по оптимизации внимание на индексы и статистику обращали в первую очередь.
42. torg1c 36 19.03.15 19:50 Сейчас в теме
Почему если сделать переиндексацию то какие то индексы все равно показываются фрагментированными в sys.dm_db_index_physical_stats?
43. Painted 49 20.03.15 08:09 Сейчас в теме
(42) torg1c, Процент фрагментации какой при этом? fragment_count? Если меньше 10, то забей. Так и должно быть. )))
44. AlexO 135 20.03.15 09:24 Сейчас в теме
(42) torg1c, потому что база постоянно меняется.
45. torg1c 36 23.03.15 17:35 Сейчас в теме
(44) AlexO,

Нет! Останавливаюю серевер 1с, делаю переиндексацию, смотрю индексы и вижу что часть так и непереиндексировалась.
Процент и 80 и 90 есть )
46. Painted 49 23.03.15 22:13 Сейчас в теме
(45) torg1c,
Процент и 80 и 90 есть
Еще page_count надо смотреть. Если меньше 10 или проценты меньше 10, то это нормально. Дефрагментатор, он не мелочится. Для 100% дефрагментации нужно делать rebuild index.
47. AlexO 135 23.03.15 22:26 Сейчас в теме
(46) Painted, дефрагментация индекса вообще уже не рекомендуется - бессмысленная операция для баз 1С.
48. Painted 49 27.03.15 08:26 Сейчас в теме
(46) Painted,
Еще page_count надо смотреть. Если меньше 10
Все еще круче, оказывается. Если page_count < 100, считается слабо фрагментированный индекс.
49. wbazil 138 14.05.15 13:29 Сейчас в теме
скажите пожалуйста, можно ли использовать Ваш скрипт для 7.7?
на 8.2 все отработало отлично
50. speshuric 1331 20.05.15 19:48 Сейчас в теме
(49) от версии 1с не зависит.
51. Kondusov_Dmitij 02.09.15 12:24 Сейчас в теме
Подскажите, пожалуйста, есть ЗУП КОРП 2,5 база стала сииильно пухнуть, каждый день выполняется переиндексация, обновление статистики. Нашел таблицу в ней всего 2 индекса, но они занимают 45Гб, это как-то не нормально. Что делать то?
52. speshuric 1331 03.09.15 00:48 Сейчас в теме
(51) Kondusov_Dmitij,
Мало данных.
2 индекса, но они занимают 45Гб
-это как именно посчитали? И что это за таблица? Может это регистр накопления и итоги не закрываются?
53. SnakePlisskin 3 14.09.16 15:52 Сейчас в теме
Под SQL 2000 работает ? Где вообще есть нормальная инфа как обслуживать базу на SQL 2000 ?
54. speshuric 1331 14.09.16 18:32 Сейчас в теме
(53) Под SQL 2000 не работает: там нет нужных DMV и приходилось подобное делать через кучу приседаний (см. DBCC SHOWCONTIG)
Я бы настоятельно рекомендовал уже уйти с 2000 - слишком много накопилось исправленных проблем. Если речь про 7.7, то это немного сложнее, но эти сложности того стоят. Если речь про 1С8, то безусловно переходить.
56. SnakePlisskin 3 15.09.16 18:30 Сейчас в теме
(54) речь идет от 7.7. пока на 8 переходить в планах нет совсем, и скуля 2000 лицушная. пока делаем выгрузку/загрузку, надеясь на то что по время этой процедуры все таблицы пересоздаются по новой.
57. speshuric 1331 15.09.16 19:06 Сейчас в теме
(56) alex_gus,
Смотрите на перевод 7.7 на новую версию. Ссылку я дал.
58. SnakePlisskin 3 19.09.16 09:19 Сейчас в теме
(57) я понял, но как то "секретный релиз" не шибко внушает доверия если честно...а так конечно надо попробовать.
Да и есть ли у Вас мысли, по поводу выгрузки/загрузки при этой процедуре база очищается или данные поверх пишутся ?
59. Drizer2000 14 23.09.16 13:20 Сейчас в теме
(57) подскажите, я сделал свертку базы sql 2008 1c 7.7. Удалял данные прямо из таблиц sql, удалилось несколько миллионов строк из проводок и регистров,но размер базы не изменился. Для сжатия базы запустил отдельно регламентную процедуру на ночь, но размер базы не уменьшился, ужался только лог. Модель базы данных "простая".
55. speshuric 1331 14.09.16 18:34 Сейчас в теме
60. Crushl 18.11.16 05:14 Сейчас в теме
Попробовал вашу скрипт. На небольших базах до 100Гб, работает замечательно время обслуживания раза в три уменьшилось. А вот на большой базе 300Гб проблема. Перестроение индексов делается достаточно быстро, а вот обновление статистики занимает часов 15. Опять же скрипт не прекращает работу по таймауту. Не могли бы подсказать куда копать, где может быть проблема?
65. Fox-trot 158 12.06.19 13:18 Сейчас в теме
ежели по-быстрому :)
заведи локального пользователя винды, добавь его в админы
агента запускай под его учеткой
в скуле дай права ему же
66. ЕСТЬNULL 207 07.10.19 11:58 Сейчас в теме
Что надо поменять в скрипте, чтобы он молотил все несистемные базы, которые не содержат "_copy" в конце?
67. user645801_yyyuuu123q 24.07.20 02:57 Сейчас в теме
Друзья, всем привет подскажите пожалуйста.
Резервнованое копирование Мне самому настраивать? Проверка целостности тоже не проходит инструкция "DBCC FREEPROCCACHE" не выполняется. Будет ли корректно добавить это все самому? Скажем так сначала целостность базы, потом скрипт потом DBCC FREEPROCCACHE дальше резервное копирование -> очистка журналов ?
68. z8491 05.11.20 14:28 Сейчас в теме
Подскажите как этот скрипт настроить из задания Агента, тип указываю сценарий T-SQl? какую указывать базу в настройках задания? И как часто настраивать запуск скрипта ?
69. estima-1c 08.08.23 11:41 Сейчас в теме
Планируется ли обновление скрипта связанное с изменениями в платформе 8.3.22
70. Gvenor 128 16.11.23 12:36 Сейчас в теме
(69) Себе, чтобы работало в 8.3.22, сделал так:
Заменил

select 
 'ALT ER   INDEX ' + i.index_name + ' ON [' + i.db_name + '].[' + i.schema_name + '].[' + i.table_name + '] ' + 
 case 
  when @index_rebuild_threshhold <= i.avg_fragmentation_in_percent then 'REBUILD ' + @WithOptionsRebuild
  when @index_rebuild_space_used_threshhold >= i.avg_page_space_used_in_percent then 'REBUILD ' + @WithOptionsRebuild
  when @index_defrag_threshhold <= i.avg_fragmentation_in_percent then 'REORGANIZE '
 end sql_command,

на
select 
	case 
		when @index_rebuild_threshhold <= i.avg_fragmentation_in_percent then 'ALT ER   INDEX ' + i.index_name + ' ON [' + i.db_name + '].[' + i.schema_name + '].[' + i.table_name + '] REBUILD ' + @WithOptionsRebuild
		when @index_rebuild_space_used_threshhold >= i.avg_page_space_used_in_percent then 'ALT ER   INDEX ' + i.index_name + ' ON [' + i.db_name + '].[' + i.schema_name + '].[' + i.table_name + '] REBUILD ' + @WithOptionsRebuild
		when @index_defrag_threshhold <= i.avg_fragmentation_in_percent then 
		'ALT ER   INDEX ' + i.index_name + ' ON [' + i.db_name + '].[' + i.schema_name + '].[' + i.table_name + '] SET (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON);
		ALT ER   INDEX ' + i.index_name + ' ON [' + i.db_name + '].[' + i.schema_name + '].[' + i.table_name + '] REORGANIZE;
		ALT ER   INDEX ' + i.index_name + ' ON [' + i.db_name + '].[' + i.schema_name + '].[' + i.table_name + '] SET (ALLOW_PAGE_LOCKS = OFF, ALLOW_ROW_LOCKS = ON);'
	end sql_command,
Показать


ALT ER - надо слитно написать. И между ALT ER и INDEX один пробел Не дает по другому вставить.
71. green35 02.04.24 22:25 Сейчас в теме
Здравствуйте.

Запуск скрипта завершился с ошибкой:

Сообщение
Выполняется от имени пользователя: NT Service\SQLSERVERAGENT.Программа выполнения пакетов Microsoft ® SQL Server Version 14.0.2047.8 for 64-bit © Майкрософт (Microsoft), 2017. Все права защищены. Начало: 22:15:56 Выполнение: 2024-04-02 22:15:57.61 Источник: {BA2F0FA8-92EE-4EA6-8596-9CCA2F66962C} Выполнение запроса "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% завершено Конец выполнения Ошибка: 2024-04-02 22:18:36.90 Код: 0xC002F210 Источник: Задача "Выполнение инструкции T-SQL" Задача "Выполнение SQL" Описание: Сбой выполнения запроса "-- Параметры скрипта declare @database_names as n..." со следующей ошибкой: "У пользователя нет разрешения на выполнение этого действия. Контекст базы данных изменен на "master". -- 2024-04-02 22:15:58.020 -- Поиск баз данных для обслуживания -- 2024-04-02 22:15:58.023 -- найдено 30 баз данных для обслуживания -- 2024-04-02 22:15:58.023 -- Поиск индексов для обслуживания". Возможные причины сбоя: проблемы с этим запросом, свойство "ResultSet" установлено неправильно, параметры установлены неправильно или соединение было установлено неправильно. Конец ошибки Предупреждение: 2024-04-02 22:18:36.90 Код: 0x80019002 Источник: ВложенныйПлан_1 Описание: Код предупреждения служб SSIS: DTS_W_MAXIMUMERRORCOUNTREACHED. Метод Execution завершен успешно, но число возникших ошибок (1) достигло максимально допустимого (1), что привело к сбою. Это происходит, когда количество ошибок достигает значения, определенного в свойстве MaximumErrorCount. Измените свойство MaximumErrorCount или устраните ошибки. Конец предупреждения DTExec: завершено исполнение пакетаDTSER_FAILURE (1). Начало: 22:15:56 Готово: 22:18:37 Прошло:160.594 секунд. Не удалось выполнить пакет. Шаг завершился с ошибкой.
Оставьте свое сообщение