IE DEV 2016. Оптимизируй это! Или MS SQL и Экспертный подход творят чудеса!
База зависает намертво несколько раз в день, помогает только перезапуск сервера 1С. Знакомо?! До боли.
Царенко Роман из компании "Инфософт" рассказал о своем опыте борьбы с такой бедой.
По совершенно непонятным причинам для ИТ-службы клиента база оперативного учёта по одному из основных видов деятельности вдруг стала зависать вплоть до полного паралича системы, который лечился только перезапуском сервера 1С.
Входные данные:
- 90% запросов делают выборку из одного регистра.
- Этот регистр накопления содержит 1 000 000 000+ записей.
- Итоги включены.
- Регламентные операции MS SQL настроены.
- Индексируется вроде тоже то, что должно.
Анализ выявил, что:
- Около 70% записей в регистре находятся в будущем от текущей даты И это не ошибка - это логика бизнес-процесса.
- Итоги включены, но не рассчитаны. Последняя дата, на которую итоги рассчитаны, более 2-х лет назад.
- Регламентные операции MS SQL настроены, но не успевают выполняться за 4 часа технологического окна сервера 1С. В частности, полное обновление статистики.
Итак решение:
Отключать текущие итоги
Для отключения итогов пришлось очистить текущие итоги прямым запросом к базе данных. Но 1С не просто так запрещает прямые запросы к БД. Можно очень сильно накосячить.
Поэтому таблицу не просто очистили, а создали её пустую копию, а сам оригинал переименовали. Также переписали запросы на неиспользование текущих запросов.
Плюс к этому был написан отдельный скрипт для обновления статистики, который производит обновление статистики анализируя были ли изменения в таблице после предыдущего полного обновления статистики (его текст предлагается в конце материала).
Также необходимо контролировать, чтобы после пересчета итогов обновлялась статистика SQL.
Материал подготовлен по докладу Царенко Романа,
руководителя отдела автоматизации производственного учёта
компании «ИнфоСофт», на конференции Infostart Event 2016 Developer.
http://event.infostart.ru/2016/
Полные версии докладов:
http://event.infostart.ru/2016/#price
Приложение 1
В докладе предлагается простой и достаточный для большинства случаев текст настроек технологического журнала:
Приложение 2
Текст скрипта "Гибкое UPDATE STATISTICS"
USE master
set nocount on
Declare @NameBD varchar(50)
Declare @Skript nvarchar(2000)
Declare spaceused cursor local for
select imena.name
from sysdatabases as imena
where imena.name in (‘1cbd')
open spaceused
Fetch next from spaceused into @NameBD
while @@fetch_status=0
Begin
set @Skript = 'use ['+@NameBD+'] '
set @Skript = @Skript + '
Declare @SkriptUS nvarchar(2000)
set @SkriptUS = ''''
Declare @NameTab varchar(50)
Declare tableName cursor local for
select distinct Object_Name(ss.id) from
sys.sysindexes as ss
where 1=1
and ss.rowmodctr > 1
and left(Object_Name(ss.id),3) <>
''sys''
and ss.name is not
open tableName
Fetch next from tableName into
@NameTab
while @@fetch_status=0
Begin
set @SkriptUS = ''use ['+@NameBD+']
''
+ ''UPDATE STATISTICS ''+@NameTab+''
WITH FULLSCAN''
EXEC(@SkriptUS)
fetch next from tableName into
@NameTab
end
Close tableName
Deallocate tableName
'
EXEC(@Skript)
--print @Skript
fetch next from spaceused into
@NameBD
end
Close spaceused
Deallocate spaceused
set nocount off 10
DBCC FREEPROCCACHE