В процессе эксплуатации высоконагруженных систем на базе 1С:Предприятие 7.7 и MS SQL Server 2000/2008 системные администраторы часто сталкиваются с проблемой внезапного падения производительности. База начинает «тупить» на ровном месте, хотя нагрузка на сервер не менялась. В большинстве случаев причиной является устаревшая статистика (stale statistics). Оптимизатор SQL Server, опираясь на неверные данные о распределении значений в таблицах, строит неоптимальные планы запросов.
Особенно остро проблема стоит для баз объемом от 500 Гб и выше, где полное обновление статистики (UPDATE STATISTICS) с параметром FULLSCAN может занимать более суток. В этой статье мы разберем, как определить момент, когда статистика «протухла», и как организовать её выборочное обновление.
Рассмотрим стандартный механизм AUTO_UPDATE_STATISTICS. В SQL Server 2000/2008 порог срабатывания автоматического обновления фиксирован и составляет 500 строк + 20% изменений в таблице. Проанализируем ситуацию: если в таблице проводок или периодических реквизитов (например, _1SCONST) содержится 10 миллионов строк, то автоматика сработает только после изменения 2 миллионов записей. К этому моменту план запроса может стать катастрофически медленным.
Выясним причину, по которой это критично для 1С 7.7. Данная платформа активно использует индексы по датам и составным полям. При добавлении новых документов (так называемый «возрастающий ключ») статистика по последним датам просто отсутствует в гистограмме, и SQL Server «не видит» актуальные данные, считая, что строк с сегодняшней датой в базе почти нет.
Для начала разберем, как быстро получить список всех статистик в базе и узнать, когда они обновлялись в последний раз. Это поможет нам выявить таблицы, которые не обслуживались долгое время. Воспользуемся системным представлением sys.stats (для SQL 2005/2008) или sysindexes (для SQL 2000).
Проанализируем следующий скрипт, который выводит количество дней, прошедших с момента последнего обновления:
USE [ИмяВашейБазы];
SELECT
obj.name AS TableName,
st.name AS StatisticsName,
STATS_DATE(st.object_id, st.stats_id) AS LastUpdate,
DATEDIFF(day, STATS_DATE(st.object_id, st.stats_id), GETDATE()) AS DaysOld
FROM sys.stats AS st
JOIN sys.objects AS obj ON st.object_id = obj.object_id
WHERE obj.type = 'U' -- Только пользовательские таблицы
ORDER BY DaysOld DESC;
Если мы видим значения DaysOld более 1–2 дней для активно меняющихся таблиц документов (DH*, DT*) или движений (RG*, RA*), это явный сигнал к действию.
Более точный метод — это проверка количества реально измененных строк с момента последнего обновления. Для этого в системной таблице sysindexes существует поле rowmodctr. Оно накапливает количество вставок (insert), удалений (delete) и обновлений (update).
Разберем запрос, который покажет нам наиболее «грязные» таблицы:
SELECT
OBJECT_NAME(id) AS TableName,
name AS IndexName,
rowmodctr AS ModifiedRows
FROM sysindexes
WHERE id > 100 -- Исключаем системные таблицы
AND rowmodctr > 0
ORDER BY rowmodctr DESC;
Посмотрим на результат: если ModifiedRows исчисляется десятками тысяч, а автообновление еще не сработало (так как не достигнут порог 20%), нам необходимо инициировать пересчет вручную.
Теперь рассмотрим, как автоматизировать процесс. Нам не нужно обновлять всё подряд. Мы создадим курсор, который будет перебирать таблицы, в которых были изменения, и выполнять UPDATE STATISTICS только для них. Это значительно экономит время на базах в полтерабайта.
Проанализируем пример кода для регламентного задания:
DECLARE @tablename VARCHAR(1000);
DECLARE @command NVARCHAR(4000);
DECLARE tab_cursor CURSOR FOR
SELECT DISTINCT OBJECT_NAME(si.id)
FROM sysindexes si
JOIN sysobjects so ON si.id = so.id
WHERE so.type = 'U'
AND si.rowmodctr > 1000 -- Порог срабатывания: более 1000 изменений
AND OBJECT_NAME(si.id) NOT LIKE 'sys%';
OPEN tab_cursor;
FETCH NEXT FROM tab_cursor INTO @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Используем FULLSCAN для точности или SAMPLE 20 PERCENT для скорости
SET @command = 'UPDATE STATISTICS dbo.' + @tablename + ' WITH FULLSCAN';
PRINT 'Обновление: ' + @tablename;
EXEC(@command);
FETCH NEXT FROM tab_cursor INTO @tablename;
END;
CLOSE tab_cursor;
DEALLOCATE tab_cursor;
Часто тормоза вызваны не только статистикой, но и фрагментацией индексов. В MS SQL Server 2000 использовалась команда DBCC SHOWCONTIG. Разберем более продвинутый подход, который совмещает проверку фрагментации и обновление статистики в рамках одной процедуры.
Посмотрим на алгоритм:
DBCC SHOWCONTIG для получения данных о плотности страниц.DBCC INDEXDEFRAG (онлайн-дефрагментация, не блокирует базу).UPDATE STATISTICS, так как дефрагментация могла изменить распределение данных.При использовании DBCC INDEXDEFRAG крайне важно помнить, что этот процесс не блокирует таблицу полностью, в отличие от DBCC DBREINDEX, что позволяет запускать его даже в рабочее время (с учетом нагрузки на дисковую подсистему).
Если ваша база 1С 7.7 работает на SQL Server 2008 R2 (или более поздней версии), обязательно проанализируйте возможность включения Флага трассировки 2371. Этот флаг делает порог автоматического обновления статистики динамическим: чем больше таблица, тем меньше процентов изменений требуется для старта обновления. Для базы в 500 Гб это может стать «серебряной пулей», решающей проблему актуальности данных без написания сложных скриптов.
Также обратите внимание на параметр AUTO_UPDATE_STATISTICS_ASYNC. Включив его (ALTER DATABASE ... SET AUTO_UPDATE_STATISTICS_ASYNC ON), вы позволите серверу не «подвешивать» запрос пользователя в ожидании пересчета статистики. SQL выполнит запрос по старому плану, но запустит фоновое обновление, чтобы следующие запросы уже летали.
Подводя итог, скажем: для больших баз 1С 7.7 мониторинг поля rowmodctr и точечное обновление статистики — это не роскошь, а необходимость. Регулярное (раз в несколько часов) выполнение скрипта по наиболее изменчивым таблицам (таким как _1SCONST, _1SJOURN) позволит избежать «писем счастья» от пользователей о внезапных тормозах системы.