Работа с базами данных объемом в несколько сотен гигабайт (в нашем случае — 800 ГБ) накладывает на разработчика и администратора 1С особую ответственность. Когда конфигурация «1С:Комплексная автоматизация 1.0» (КА 1.0) начинает вести себя нестабильно при массовом проведении документов, например, «Отчета о розничных продажах», это часто сигнализирует о достижении критического порога производительности. Рассмотрим подробнее ситуацию, когда «простой» запрос к регистру сведений ЦеныАТТ становится причиной длительных блокировок и зависаний всей системы.
В процессе массового проведения документов система обращается к виртуальной таблице СрезПоследних регистра сведений ЦеныАТТ. Проблема проявляется нестабильно: иногда запрос отрабатывает мгновенно, а иногда вызывает «мертвое» зависание транзакции. При анализе через мониторинг MS SQL Server выясняется, что виновником является обращение к таблице InfoRg19056 (соответствует регистру «Цены АТТ»). Выясним причину такого поведения, проанализировав объем данных и состояние базы.
Для начала оценим масштаб. Если в регистре более 16 миллионов записей, а общий объем индексов (3.2 ГБ) в два раза превышает объем данных (1.6 ГБ), это явный признак того, что таблица перегружена и требует обслуживания. При этом база работает без свертки более 5 лет, а регламентные операции по дефрагментации и реиндексации не проводились из-за отсутствия «технологического окна».
Первым делом нам необходимо понять, в каком состоянии находятся индексы нашей таблицы. Высокая фрагментация приводит к тому, что SQL-серверу приходится считывать гораздо больше страниц данных, чем необходимо, что фатально сказывается на операциях Index Scan. Разберем по шагам, как проверить степень фрагментации с помощью SQL-запроса:
SELECT TOP 100
DatbaseName = DB_NAME(),
TableName = OBJECT_NAME(s.[object_id]),
IndexName = i.name,
[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2),
page_count,
'alter index [' + i.name + '] on [' + sh.name + '].['+ OBJECT_NAME(s.[object_id]) + '] REBUILD' [sql]
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes as i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
LEFT JOIN sys.objects o on s.[object_id] = o.[object_id]
LEFT JOIN sys.schemas as sh on sh.[schema_id] = o.[schema_id]
WHERE s.database_id = DB_ID() AND i.name IS NOT NULL AND page_count > 1000
ORDER BY avg_fragmentation_in_percent desc
Обратите внимание на поле page_count. Если количество страниц мало (менее 1000), фрагментация может не оказывать существенного влияния. Но для регистра с 16 млн строк количество страниц будет огромным, и фрагментация выше 25–30% уже начнет серьезно замедлять работу.
Часто нестабильность запроса (то быстро, то медленно) связана с механизмом Parameter Sniffing. SQL Server кэширует план выполнения запроса на основе первых переданных параметров. Если первый запрос был по одной номенклатуре, а второй — по списку из 1000 позиций, закэшированный план может оказаться крайне неэффективным. Проанализируем ситуацию с точки зрения кода 1С:
Функция СформироватьЗапросПоПродажнымЦенам(ДатаЦен, СписокСкладов, СписокНоменклатуры) Экспорт
Запрос = Новый Запрос;
Запрос.УстановитьПараметр("Дата", ДатаЦен);
Запрос.УстановитьПараметр("СписокСкладов", СписокСкладов);
Запрос.УстановитьПараметр("СписокНоменклатуры", СписокНоменклатуры);
Запрос.Текст = "
|ВЫБРАТЬ
| ЦеныПродажные.Склад КАК Склад,
| ЦеныПродажные.Номенклатура КАК Номенклатура,
| ЦеныПродажные.Цена КАК Цена
|ИЗ
| РегистрСведений.ЦеныАТТ.СрезПоследних(&Дата, Склад В (&СписокСкладов)
| И Номенклатура В (&СписокНоменклатуры)) КАК ЦеныПродажные";
Возврат Запрос.Выполнить();
КонецФункции
Посмотрим на пример: использование виртуальной таблицы СрезПоследних с условиями внутри параметров (в фигурных скобках или в качестве параметров ВТ) — это стандартный подход. Однако на огромных таблицах SQL Server может ошибиться в выборе между Index Seek и Index Scan. Если обновление статистики (UPDATE STATISTICS) не помогает, стоит рассмотреть вариант переписывания запроса на прямую выборку из физической таблицы с использованием вложенного запроса для получения максимальной даты.
Если администратор утверждает, что «техноокна нет», вспомним о возможностях MS SQL Server Enterprise Edition. Эта версия позволяет выполнять Online Index Rebuild. Это означает, что перестроение индексов происходит в фоновом режиме, не блокируя работу пользователей. Рассмотрим подробнее, что нужно сделать:
FULLSCAN для наиболее нагруженных таблиц.Никакие настройки SQL не помогут, если база растет бесконечно. В нашем случае регистры накопления Продажи и ТоварыВРознице содержат более 100 миллионов строк каждый. Проанализируем стратегию «выживания» для таких баз:
Для регистра ЦеныАТТ часто характерна избыточность. В КА 1.0 при каждом поступлении товаров могут плодиться записи с одной и той же ценой. Рекомендуется запустить обработку, которая удалит «дубли» (записи, где цена не менялась по сравнению с предыдущим периодом для той же номенклатуры и склада). Это может сократить объем регистра в разы.
Также неизбежным решением становится свертка базы. Поскольку база не сворачивалась более 5 лет, накопленные итоги и объемы таблиц начинают тормозить даже самые простые операции. Выясним, что можно сделать прямо сейчас:
ЦеныАТТ, оставив только актуальные срезы на начало года.Для решения проблемы зависаний мы должны действовать комплексно. Рассмотрим финальный чеклист:
InfoRg19056 и связанных с ней индексов.FullScan.TempDB: файлы временной базы должны располагаться на максимально быстрых дисках (SSD/NVMe), а их количество должно соответствовать числу ядер процессора (но не более 8).СрезПоследних только необходимый минимум номенклатуры, избегая передачи пустых списков или слишком больших массивов.Соблюдение этих правил позволит стабилизировать работу «1С:Комплексная автоматизация 1.0» даже на таких внушительных объемах данных до момента перехода на новую платформу или проведения полной свертки базы.