Индексы и статистика в базе 1С: что можно трогать, а что сломает поддержку

Соблазн зайти в SQL Server, посмотреть рекомендации по недостающим индексам и создать их все — велик. Иногда это даже помогает. Чаще создаёт проблему, которая всплывёт при следующем обновлении конфигурации. Разберём, кто на самом деле управляет индексами в базе 1С, в каких случаях ручное вмешательство оправдано и как отличить полезный индекс от вредного.

Ключевой факт, который определяет всё остальное: структуру таблиц и индексов в базе создаёт и поддерживает платформа, а не администратор СУБД.

Платформа генерирует индексы на основании метаданных конфигурации: измерения регистров, реквизиты с признаком индексирования, ключевые поля справочников и документов. Имена таблиц и индексов при этом служебные — _Document123, _AccumRg456, и человеку они ни о чём не говорят.

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

Отсюда главное правило: индекс, созданный вручную в SQL Server, не переживёт реструктуризацию затронутой таблицы. Он исчезнет молча, и обнаружится это по возврату старых тормозов через месяц после обновления.

Правильный способ добавить индекс — не в SQL Server, а в конфигураторе: поставить реквизиту признак индексирования, добавить измерение в регистр, настроить индексирование по значению. Тогда платформа создаст индекс сама и будет поддерживать его при всех изменениях структуры.

Это медленнее, требует программиста и окна работ. Зато это единственный устойчивый вариант.

Исключения есть, и их стоит знать.

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

Таблицы, которые платформа не реструктурирует. Некоторые служебные таблицы стабильны между релизами. Риск ниже, но не нулевой.

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

Во всех случаях обязательны две вещи. Первая — запись в документации по клиенту: какой индекс, на какой таблице, зачем, когда создан. Вторая — проверка после каждого обновления конфигурации, что индекс на месте.

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

SELECT t.name AS tbl, i.name AS idx, i.type_desc
FROM sys.indexes i JOIN sys.tables t ON t.object_id = i.object_id
WHERE i.name LIKE 'itf_%'   -- наши индексы с общим префиксом
ORDER BY t.name;

Префикс в имени — простой приём, который сильно упрощает жизнь: сразу видно, что создано нами, а что платформой.

SQL Server ведёт статистику по запросам, которым, по мнению оптимизатора, помог бы индекс. Смотреть её полезно, следовать буквально — нет.

SELECT TOP 15
       ROUND(s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans), 0) AS score,
       d.statement AS tbl, d.equality_columns, d.inequality_columns, d.included_columns,
       s.user_seeks, s.user_scans
FROM sys.dm_db_missing_index_group_stats s
JOIN sys.dm_db_missing_index_groups g ON g.index_group_handle = s.group_handle
JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
ORDER BY score DESC;

Четыре причины не создавать всё подряд из этого списка.

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

Не учитывается стоимость записи. Каждый индекс — это дополнительная работа при каждой вставке и обновлении. В учётной системе с интенсивным вводом лишние индексы замедляют проведение документов.

Статистика сбрасывается при перезапуске. Список после ночного рестарта отражает несколько часов, а не типичную нагрузку.

Разовый тяжёлый отчёт формирует высокий score. Индекс под отчёт, который строят раз в квартал, будет удорожать записи ежедневно.

Правильное использование: воспринимать список как подсказку, где искать. Дальше смотреть конкретный запрос, его план и решать — а решение чаще всего реализовывать через метаданные конфигурации.

Обратная задача встречается реже, но бывает полезной: найти индексы, которые только удорожают запись.

SELECT OBJECT_NAME(i.object_id) AS tbl, i.name AS idx,
       s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s
       ON s.object_id = i.object_id AND s.index_id = i.index_id
       AND s.database_id = DB_ID()
WHERE i.type_desc = 'NONCLUSTERED' AND i.is_primary_key = 0
  AND ISNULL(s.user_seeks, 0) + ISNULL(s.user_scans, 0) + ISNULL(s.user_lookups, 0) = 0
  AND ISNULL(s.user_updates, 0) > 1000
ORDER BY s.user_updates DESC;

Индексы в результате обновлялись тысячи раз и ни разу не использовались для чтения — чистый расход.

Но в базе 1С удалять их нельзя. Они созданы платформой по метаданным, и удаление вернётся при ближайшей реструктуризации.

Что с этим делать: посмотреть, откуда индекс взялся. Если это признак индексирования у реквизита, поставленный когда-то «на всякий случай», его снимают в конфигураторе — и индекс уходит корректно.

Оговорка: статистика использования тоже сбрасывается при перезапуске службы. Прежде чем делать выводы, убедитесь, что инстанс работает достаточно долго — минимум пару недель, лучше месяц с закрытием периода, чтобы захватить квартальную отчётность.

Про периодичность обновления мы говорили в статье про обслуживание. Здесь — про качество.

Полное сканирование против выборки. По умолчанию SQL Server строит статистику по выборке, размер которой зависит от объёма таблицы. Для больших таблиц выборка может быть меньше процента строк.

Для баз 1С это часто даёт плохие оценки. Причина в характере данных: регистры накопления содержат сильно неравномерные распределения — по одной организации миллион записей, по другой сто. Выборка легко промахивается мимо этой неравномерности.

UPDATE STATISTICS [dbo].[_AccumRgTotals123] WITH FULLSCAN;

Асинхронное автообновление. Включать. Иначе первый запрос после порога изменений ждёт пересчёта статистики.

Инкрементная статистика имеет смысл только на секционированных таблицах, которых в типовых базах 1С обычно нет.

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

-- посмотреть, по какой доле строк построена статистика
SELECT s.name, sp.rows, sp.rows_sampled,
       100.0 * sp.rows_sampled / NULLIF(sp.rows, 0) AS pct_sampled,
       sp.last_updated
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE sp.rows > 100000
ORDER BY pct_sampled ASC;

Строки с долей выборки в единицы процентов на крупных таблицах — первые кандидаты на обновление с полным сканированием.

Явление, которое регулярно выглядит мистикой: один и тот же отчёт вчера строился 3 секунды, сегодня — 4 минуты, при том что ничего не менялось.

Механика такая. SQL Server кэширует план выполнения, построенный под конкретные значения параметров первого вызова. Если первый вызов пришёл с параметром, дающим десять строк, план будет оптимален для десяти строк. Следующий вызов с параметром, дающим миллион строк, использует тот же план — и работает катастрофически медленно.

В базах 1С это встречается часто из-за неравномерности данных: отчёт по маленькой организации и по большой формируют одинаковый запрос с разными параметрами.

Признак: проблема появляется после перезапуска службы или после обновления статистики (оба события чистят кэш планов) и держится до следующей такой чистки.

Что можно сделать администратору:

• убрать конкретный план из кэша и дать ему перестроиться;
• обновить статистику по задействованным таблицам — это тоже инвалидирует планы;
• в тяжёлых случаях — включить Query Store и форсировать хороший план.

-- найти дорогие планы и сбросить конкретный
SELECT TOP 10 qs.plan_handle, qs.total_worker_time / qs.execution_count AS avg_cpu,
       qs.execution_count, SUBSTRING(t.text, 1, 150) AS txt
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
ORDER BY avg_cpu DESC;

DBCC FREEPROCCACHE (0x0600...);  -- только конкретный план, не весь кэш

Полную очистку кэша командой без параметров на боевом сервере лучше не делать: все запросы разом начнут компилироваться заново, и вы получите провал производительности на несколько минут.

Клиент — компания по аренде спецтехники, 31 рабочее место, УТ на MS SQL 2017.

Обратились с формулировкой, которая сразу показалась интересной: «отчёт по загрузке техники тормозит, но не всегда — примерно раз в квартал начинает, потом само проходит».

«Само проходит» насторожило больше всего. Проблемы производительности сами не проходят.

Замеры подтвердили жалобу: отчёт строился 68 секунд. Двумя неделями ранее, по словам сотрудников, — за 4 секунды.

План выполнения показал сканирование таблицы регистра сведений на 8,4 миллиона строк там, где должен был идти поиск по индексу.

Проверили список индексов на таблице. Нужного не было. Проверили метаданные конфигурации — признак индексирования у реквизита не стоял.

Разгадка нашлась в переписке годичной давности. Предыдущий подрядчик при похожей жалобе создал индекс напрямую в SQL Server. Отчёт ускорился, все были довольны, задачу закрыли.

Дальше начался цикл. Раз в квартал компания обновляла конфигурацию до нового релиза. Обновление задевало эту таблицу, платформа выполняла реструктуризацию, пересоздавала таблицу по метаданным — и созданный вручную индекс исчезал. Отчёт начинал тормозить. Кто-то из ИТ замечал, лез в SQL, видел рекомендацию в missing index DMV, создавал индекс заново. Отчёт ускорялся. Через три месяца всё повторялось.

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

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

Отчёт строится за 3,8 секунды и продолжает строиться за столько же после четырёх последующих обновлений.

Что мне нравится в этом случае: техническая причина была тривиальной и лежала на поверхности. Незаметной её делала организация — отсутствие журнала изменений, из-за которого каждый следующий человек начинал разбор с нуля и приходил к тому же временному решению.

Когда приходит задача «оптимизировать запросы базы 1С», мы идём так.

1. Убедиться, что статистика свежая. Обновить с полным сканированием, если сомнения есть. Половина «проблем с индексами» на этом заканчивается.
2. Найти конкретные тяжёлые запросы — через технологический журнал 1С или через dm_exec_query_stats. Не абстрактно «база медленная», а список из пяти запросов.
3. Посмотреть фактические планы этих запросов. Сравнить оценочное и фактическое число строк.
4. Понять, что именно дорого: сканирование вместо поиска, лишнее соединение, сортировка, spill в tempdb.
5. Проверить missing index DMV по этим конкретным таблицам — как подсказку, не как инструкцию.
6. Реализовать через конфигурацию: признак индексирования, изменение состава измерений, правку запроса.
7. Замерить повторно и записать результат.

Пункт первый стоит первым не случайно. По нашей статистике за последние два года примерно в 40 % обращений «нужны индексы» проблема решалась обновлением статистики, ещё в 20 % — правкой конкретного запроса, и только оставшееся действительно требовало изменения структуры индексов.

Индексы — последнее, что стоит трогать, а не первое. Они кажутся простым решением именно потому, что их легко создать. Но в базе 1С «легко создать» и «правильно создать» — разные вещи, и вторая проходит через конфигуратор.

Можно ли создавать индексы напрямую в SQL Server для базы 1С?

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

Стоит ли создавать индексы по рекомендациям missing index DMV?

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

Почему один и тот же отчёт то быстрый, то медленный?

Обычно это parameter sniffing: SQL Server закэшировал план, построенный под параметры первого вызова, и использует его для вызовов с совсем другими объёмами данных. Помогает обновление статистики, сброс конкретного плана из кэша или форсирование плана через Query Store.

Зачем обновлять статистику с FULLSCAN, если есть автообновление?

Автообновление строит статистику по выборке, которая на больших таблицах может быть меньше процента строк, и промахивается мимо неравномерных распределений — а в регистрах 1С они типичны. Полное сканирование даёт оптимизатору честную картину.