Медленная работа - работа над ошибками

Продолжение...

Получили следующие рекомендации:
1. Выполнять пересчет статистики.
2. Очищать рабочую базу от устаревшей подробной истории всех действий пользователей (чистить "сеансы").
3. Очищать рабочую базу от устаревшей подробной информации о ходе производства.
4. Установить для базы данных свойство TRUSTWORTHY

При выполнении рекомендаций выяснились некоторые подробности, которыми хочу поделиться с сообществом.
1.Пересчет статистики – нужная и полезная операция. Ее полезно выполнять по планировщику, а не вручную. У нас при установке Vogbit в Maintenance Plans на сервере SQL был добавлен план под названием «VOGBIT defrag». План выполняет ребилд индексов базы данных Vogbit с коэффициентом заполнения 80%, попутно и статистика пересчитывается и данные пересортировываются в соответствии с кластерными индексами. Не помню, откуда он взялся (внедрение было сумбурным), вроде бы был создан автоматически, но может я и сам его сделал. В любом случае план полезен, рекомендую его включить или создать и выполнять раз в месяц. Надо только проследить, чтобы на сервере SQL Agent запускался автоматически. Однако выяснились и некоторые жуткие подробности:

a. По умолчанию после установки в свойствах базы данных Vogbit отключены автосоздание и автообновление статистики. Зачем это сделано совершенно не понятно. Но зато это как раз и приводит к заметному снижению производительности, если по какой-либо причине вышеуказанный ребилд индексов не выполняется. Настоятельно рекомендую включить эти опции и настроить по планировщику либо ребилд индексов 1 раз в месяц, либо пересчет статистики 1 раз в неделю (а хоть и каждую ночь).

b. По умолчанию после установки в свойствах базы данных Vogbit включено автоматическое сжатие (auto shrink) базы данных. Разработчик рекомендует выполнять эту операцию время от времени и в ручном режиме для улучшения производительности. Плохая рекомендация. Никогда так не делайте на промышленном развертывании!!! Сжатие базы данных, особенно если на ней не настроен правильный FillFactor, приводит только к фрагментации файла базы данных по физическому диску и к фрагментации страниц данных внутри файла базы данных. Такая двойная фрагментация может только замедлить работу, причем значительно, особенно, если сервер баз данных совмещает в себе прочие службы, например файловый сервер. Рекомендую:
    1) Отключить auto shrink (и забыть эту практику)
    2) Включить автосоздание и автообновление статистики. Выполнять ребилд индексов либо пересчет статистики по расписанию.
    3) Настроить для сервера FillFactor (хотя бы 80%) и выполнить ребилд индексов Vogbit. После этого FillFactor сервера можно вернуть к прошлым значениям. В дальнейшем индексы Vogbit будут использовать настроенный FillFactor, если явно его не менять.
    4) Установить для файла данных Vogbit размер с учетом прироста производственных данных + логи сеансов из расчета на период эксплуатации + FillFactor.
    5) Настроить для файла данных Vogbit ограничение на размер файла данных и размер автоприращения (лучше в мегабайтах, чем в процентах, лучше в размере месячного прироста данных + сеансы).

2.Чистить сеансы надо, но тут нам еще предстоит с разработчиком поломать копья.
  a. Во первых, я уже поднимал вопрос о правах SA для этой операции. Почему-то для администратора Vogbit эта функция закрыта и работает только для SA.
  b. Во вторых, нам бы хотелось автоматизировать данную процедуру. Какие для этого имеются возможности?
  c. В-третьих, на нашей базе сеансы занимают какое-то значительное место. Из 2.6 Гб после очистки половины сеансов освободилось 800 мб. Получается, что данные сеансов занимают в базе более половины. Хотелось бы понять, что такое пишется в эти сеансы в таком количестве, ведь это не производственные данные. Можно ли как-то настраивать уровень логирования сеансов?

3.Очистка базы от устаревшей производственной информации – тот же вопрос, как автоматизировать?

4.По поводу включения небезопасной и не рекомендуемой Microsoft опции TRUSTWORTHY я создам отдельную тему, но пока рекомендация вызывает сомнения и вопросы.

Константин, я вижу, что ваша база данных хорошо структурирована и обвязана хранимыми процедурами. Процедуры даже избыточны, например некоторые из блока Health дублируют функционал инструментов MSSQL. Это говорит о том, что вы заложили в систему хорошие возможности автоматизации, наверное не хотели привязываться к версии сервера баз данных и упростить требования к квалификации персонала. Хочется этими возможностями пользоваться, поэтому я и задаю свои вопросы.

Логика подробного логирования понятна.
Уточню, что говоря ниже про лог я имею в виду внутреннее логирование Vogbit, а не лог SQL.
Не понятно влияние объема лога на производительность. Я не знаю деталей вашей реализации, поэтому могу только предполагать.
По идее лог - это такая структура, в которую данные много и последовательно пишутся и редко, только в случае инцидентов, читаются. Для того, чтобы он быстро отрабатывал запись, ему нужно упростить структура (возможно за счет избыточности уменьшить количество взаимосвязанных таблиц) и минимизировать количество индексов, триггеров и правил. Пусть уж чуть медленнее происходят редкие разборы полетов, чем тормозит основной функционал. В любом случае набор таблиц для логирования не пересекается же с таблицами для производственных данных. Как же его объем может влиять на скорость манипуляций с производственными данными? На мой взгляд в двух случаях:
1) Лог сам по себе так реализован, что медленно отрабатывает запись, поэтому тормозять и прочие операции, которые ждут записи в лог. Тут уж надо что-то в системе кардинально менять...
2) Лог занимает в базе значительное место, а база сильно фрагментирована. В нашем случае, как я предполагаю, это и было основной причиной. Про исправление данной ситуации я все подробно написал выше.

Давайте теперь порассуждаем про обслуживание лога. Тут, как мне кажется у вас есть перекос в логике.
С одной стороны вы говорите, что без человека не обойтись при очистке. С другой стороны функционал очистки вешаете на SA. У него может быть в обслуживании несколько десятков серверов и сотня баз данных. Что этот человек должен знать о сеансах Vogbit? Как принимать решение об очистке? Придется пригласить кого-нибудь из пользователей Vogbit, того, кто в Vogbit является администратором, но почему-то как раз сеансы чистить и не может, хотя это полностью внутренняя кухня Vogbit. Это мне напоминает анекдот на тему "Сколько программистов нужно, чтобы выкрутить лампочку". Такой подход провоцирует на удаление всего закрытого без разбора при возникновении тормозов в работе. Ну и в чем тогда ценность лога?

На мой взгляд, если уж в систему заложена возможность автоматизации, то замечательно работала бы схема:
1) Администратор Vogbit имеет право чистить сеансы
2) Можно настроить задание SQL Agent на запуск хранимой процедуры (у вас там даже есть какие-то заглушки General.CleanEvents, General.CleanObject), которая бы удаляла данные из лога, например так: все закрытые операции, дата закрытия которых произошла более 1 года назад. Срок давности можно задавать параметром.

В сочетании с правильно настроенным размером базы, FillFactor-ом и ребилдом индексов это создаст хорошо обслуживаемую, стабильную систему.

Очистку устаревших производственных заданий можно реализовать по этой же схеме.

Для особых случаев можно очищаемые данные не просто удалять, а выгружать в архив (хоты бы простой CSV) для тех, кому и очистить базу для производительности надо и данные необходимо хранить лет 10 по каким-либо требованиям законодательства.

Мне кажется, что возможности к автоматизации вы заложили, но сами же их и отключили своим завышенным требованием к работе от SA.

Можно конечно и бэкапами на безрыбье пользоваться для архивирования, но попутно в бэкап попадет куча ненужной информации типа сеансов и это может сильно увеличить объем. Все-таки архив и бэкап - это разные вещи. Ну да ладно, об архиве я просто к слову упомянул, как вариант развития на будущее.

А вообще печально - если сказать коротко, то на все свои вопросы я получил ответ "может быть сделаем, когда-нибудь".

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

Если клиент на сервере установить - ускорится работа?

Поставили последнее обновление - стало ощутимее быстрее работать.
В режиме Новые задания.

Совет: в первую очередь почистите задания ненужные в базе. У вас там таких много. Может заметно ускорить.