У этой задачи есть неприятная особенность: тот, кто видит проблему, обычно не имеет доступа туда, где лежит ответ. Пользователи жалуются 1С-нику, а половина причин живёт на SQL Server, куда 1С-нику доступа не дают. Администратор баз данных доступ имеет, но не знает, что для 1С нормально, а что нет: MAXDOP = 0 для него — заводская настройка, а не источник очередей.
Чек-листов по настройке SQL Server под 1С написано много, и почти все они упираются в одно и то же: чтобы ими воспользоваться, нужно попасть в SSMS, вспомнить два десятка системных представлений и самому решить, что из увиденного норма. Я пошёл другим путём: собрал набор скриптов, которые только читают и печатают итог в виде КЛЮЧ=значение, и прогнал их на двух рабочих базах — на боевой с примерно 580 сеансами и на малонагруженной, куда унёс тяжёлые запросы, чтобы никому не мешать.
Ниже — что этот прогон показал, какие выводы из чисел следуют, и три грабли, на которых спотыкается почти любой самописный диагностический скрипт. Скрипты в статье рабочие, копируются в SSMS как есть. Имя базы везде заменено на BASE_1C.
Находка первая: статистике 97 дней
Самая старая статистика на таблицах от ста тысяч строк не обновлялась 97 дней. Статистик старше месяца оказалось 21 — и все они же старше трёх месяцев.
Это тот самый случай «вчера работало, сегодня висит» при том, что данных прибавилось немного. Оптимизатор строит план по статистике: если она говорит «в таблице 200 тысяч строк, значение встречается дважды», а на деле строк 50 миллионов, — он честно выберет вложенные циклы вместо хеш-соединения, и запрос будет выполняться на два порядка дольше. План при этом будет выглядеть разумно: он разумен для той картины мира, которую ему показали.
Ключевая причина, по которой это доживает до 97 дней: автообновление статистики на больших таблицах почти не срабатывает. Его порог — примерно 20 % изменённых строк, а на таблице в 50 миллионов это 10 миллионов изменений. Регистр накопления такого объёма может не набрать их за квартал, и «АВТОСТАТИСТИКА=ВКЛ» в настройках базы будет всё это время создавать ложное ощущение, что вопрос закрыт.
Скрипт, который это показывает. Он намеренно построен на STATS_DATE, а не на sys.dm_db_stats_properties: второе появилось только в SQL Server 2012, а вот это работает начиная с 2005-го.
-- Устаревшая статистика на крупных таблицах SELECT TOP 30 OBJECT_NAME(s.object_id) AS таблица, s.name AS статистика, STATS_DATE(s.object_id, s.stats_id) AS обновлена, DATEDIFF(day, STATS_DATE(s.object_id, s.stats_id), GETDATE()) AS дней_назад, p.rows AS строк FROM sys.stats AS s JOIN sys.objects AS o ON o.object_id = s.object_id JOIN (SELECT object_id, SUM(rows) AS rows FROM sys.partitions WHERE index_id IN (0,1) GROUP BY object_id) AS p ON p.object_id = s.object_id WHERE o.type = 'U' AND p.rows > 100000 AND STATS_DATE(s.object_id, s.stats_id) IS NOT NULL ORDER BY дней_назад DESC;
Лечится регулярным UPDATE STATISTICS ... WITH FULLSCAN по горячим таблицам. И да — это скучнее, чем перестроение индексов, и почти всегда важнее его.
Находка вторая: 598 запросов со сканами
Запросов, читающих больше миллиона страниц за один вызов, на боевом сервере оказалось 598. Самый дорогой запрос израсходовал 781 386 секунд процессорного времени за 104 760 выполнений — это 7,5 секунды на вызов, и так больше ста тысяч раз.
Здесь важно не только само число, но и то, как его читать. Кэш планов копится с последнего перезапуска сервера: если экземпляр подняли час назад, статистика ничего не значит, а если он работает 7 022 часа (как в нашем случае — почти десять месяцев), то перед вами картина за всё это время, а не за последний час.
Вторая тонкость: опасен не только «запрос на сорок секунд». Запрос на 20 миллисекунд, выполненный два миллиона раз, съедает больше — и почти всегда это признак запроса внутри цикла. В нашем прогоне таких, с миллионом и более выполнений, нашлось 261.
-- Топ тяжёлых запросов: сначала по чтениям за вызов, потом по суммарному CPU SELECT TOP 25 qs.execution_count AS выполнений, qs.total_logical_reads / qs.execution_count AS чтений_за_раз, qs.total_worker_time / 1000000 AS cpu_сек_всего, qs.total_worker_time / qs.execution_count / 1000 AS cpu_мс_за_раз, SUBSTRING(t.text, (qs.statement_start_offset / 2) + 1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(t.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS запрос FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t WHERE qs.execution_count > 0 ORDER BY чтений_за_раз DESC;
Имена вида _Reference123 и _AccumRgTn456 переводятся в объекты 1С через структуру хранения базы данных — платформа отдаёт это соответствие штатным методом, лезть в служебные таблицы не нужно.
Находка третья: журнал транзакций — 73 % от размера данных
Данные — 15 624 МБ, журнал — 11 464 МБ. Семьдесят три процента.
Само по себе это ещё не диагноз, а симптом с двумя разными причинами, и лечатся они противоположным образом. Отвечает на вопрос колонка log_reuse_wait_desc:
LOG_BACKUP— база в полной модели восстановления, а копии журнала не делаются. Журнал будет расти, пока не кончится диск, и при этом восстановиться на точку всё равно не получится: без копий журнала полная модель даёт минусы обеих схем и плюсы ни одной. Либо настраиваем копии журнала, либо честно переводим базу в простую модель.ACTIVE_TRANSACTION— виновата конкретная незакрытая транзакция, и чинить надо её, а не журнал. Никакой backup не поможет: пока транзакция открыта, её часть журнала не переиспользуется.
В нашем прогоне на разных базах встретились обе: у одной LOG_BACKUP, у tempdb — ACTIVE_TRANSACTION.
-- Что держит журнал и какие транзакции открыты дольше всех SELECT DB_NAME(database_id) AS база, recovery_model_desc AS модель, log_reuse_wait_desc AS держит_журнал FROM sys.databases WHERE database_id > 4; SELECT TOP 20 at.transaction_id, DATEDIFF(second, at.transaction_begin_time, GETDATE()) AS возраст_сек, s.session_id AS spid, s.program_name AS приложение, s.login_name AS логин FROM sys.dm_tran_active_transactions AS at JOIN sys.dm_tran_session_transactions AS st ON st.transaction_id = at.transaction_id JOIN sys.dm_exec_sessions AS s ON s.session_id = st.session_id ORDER BY возраст_сек DESC;
Ориентир для 1С простой: транзакции живут секунды. В прогоне активных транзакций было 83, старейшей — 69 секунд, и пришла она от 1CV83 Server. Минута открытой транзакции в 1С — это почти всегда либо проведение документа с длинным циклом внутри, либо отчёт, случайно оказавшийся в той же транзакции.
Находка четвёртая: tempdb в один файл
На второй базе — той, что поменьше, — файл данных tempdb оказался один. Это классика: при установке SQL Server долго создавал tempdb с одним файлом, и на многоядерном сервере это выливается в конкуренцию за служебные страницы (в топе ожиданий она видна как PAGELATCH_UP).
Ориентир — от четырёх до восьми файлов данных одинакового размера с одинаковым приростом. «По файлу на ядро» — устаревший совет: на 64 ядрах он даёт больше вреда, чем пользы.
Заодно полезно понимать, кто вообще занимает tempdb, потому что причины разные:
-- Из чего состоит занятое место в tempdb SELECT SUM(user_object_reserved_page_count) * 8 / 1024 AS врем_таблицы_МБ, SUM(internal_object_reserved_page_count) * 8 / 1024 AS сортировки_хеши_МБ, SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_МБ, SUM(unallocated_extent_page_count) * 8 / 1024 AS свободно_МБ FROM tempdb.sys.dm_db_file_space_usage;
Большие временные таблицы — чьи-то пакетные запросы с ПОМЕСТИТЬ на миллионы строк. Большие сортировки и хеши — отчёты без подходящих индексов. А растущий version store означает, что включён RCSI и живёт длинная транзакция, из-за которой версии строк не очищаются, — и лечится это транзакцией, а не размером tempdb.
Находка пятая, самая поучительная: 81 % ожиданий — и чинить нечего
Топ ожиданий боевого сервера выглядел так: CXCONSUMER — 38,2 %, CXSYNC_PORT — 26,0 %, CXPACKET — 17,0 %. Восемьдесят один процент времени сервер провёл в ожиданиях, связанных с параллелизмом.
По любому чек-листу отсюда следует вывод «крутите MAXDOP». Но MAXDOP на этом сервере уже был равен 4, а порог параллелизма — 100. Это ровно то, что для 1С считается правильным. Крутить нечего.
Правильное чтение этой картины такое: CX-ожидания в топе на сервере с включённым параллелизмом — норма, а не диагноз. Они показывают, что параллельные планы вообще существуют, и не более того. Если настройки параллелизма уже в рабочих значениях, следующий шаг — не трогать их, а найти конкретные тяжёлые запросы, на которых этот параллелизм расходуется. То есть вернуться к находке номер два.
Вывод, который я считаю главным во всей этой истории: вердикт обязан учитывать контекст. Отчёт, который в одной строке пишет «CXPACKET 38 % — чините параллелизм», а в соседней «MAXDOP = 4 — норма», противоречит сам себе и обесценивает все остальные свои выводы. Автоматическая диагностика без этого правила приносит больше вреда, чем пользы: администратор идёт крутить настройку, которая и так была верной.
Три грабли, на которых спотыкается самописный скрипт
Всё, что выше, — про содержание. Ниже — про форму, и это те вещи, из-за которых диагностический скрипт падает на чужом сервере, хотя у автора работал.
1. Отсутствующая колонка роняет весь пакет
Самая обидная. Колонка total_physical_memory_kb появилась в sys.dm_os_sys_info только в SQL Server 2012. На сервере 2008 R2 запрос с ней не просто вернёт NULL — он не скомпилируется, и вместе с ним не выполнится весь пакет, включая те два десятка проверок, которые к памяти отношения не имеют. Сообщение будет лаконичным:
Сообщение 207, уровень 16, состояние 1 Invalid column name 'total_physical_memory_kb'.
TRY/CATCH вокруг такого запроса не спасает: ошибка возникает на этапе компиляции пакета, до того как выполнение вообще началось. Работает только вынос версионно-зависимой части в динамический SQL — он компилируется отдельно, и вот там TRY/CATCH уже ловит:
BEGIN TRY EXEC sp_executesql N' INSERT #o(s) SELECT ''ПАМЯТЬ_МБ='' + CAST(total_physical_memory_kb / 1024 AS nvarchar(20)) FROM sys.dm_os_sys_info;'; END TRY BEGIN CATCH INSERT #o(s) SELECT 'ПАМЯТЬ_МБ=нет данных (SQL Server до 2012)'; END CATCH;
Попутно: накопитель результатов должен быть временной таблицей #o, а не табличной переменной @o. Табличная переменная не видна внутри sp_executesql — это другая область видимости.
2. Ошибка 451: конфликт сортировок в UNION ALL
Скрипт, который собирает итог из системных представлений и текстовых литералов, на базе с русской сортировкой падает так:
Сообщение 451, уровень 16, состояние 1 Cannot resolve collation conflict between 'Cyrillic_General_CI_AS' and 'Latin1_General_CI_AS_KS_WS' in UNION ALL operator.
Причина в том, что системные представления отдают строки в своей сортировке (Latin1_General_CI_AS_KS_WS), а литералы наследуют сортировку базы. В INSERT конфликта нет, поэтому проблема всплывает не сразу — только когда результат собирается через UNION ALL. Лечится явным COLLATE DATABASE_DEFAULT на каждой ветке и на каждом обращении к строковой колонке представления (wait_type, program_name, log_reuse_wait_desc):
SELECT CAST('ЦЕПОЧЕК=' + CAST(COUNT(*) AS nvarchar(20)) AS nvarchar(500)) COLLATE DATABASE_DEFAULT AS [Итог] FROM sys.dm_exec_requests WHERE blocking_session_id <> 0 UNION ALL SELECT CAST('ТИП_ОЖИДАНИЯ=' + wait_type COLLATE DATABASE_DEFAULT AS nvarchar(500)) COLLATE DATABASE_DEFAULT FROM sys.dm_os_wait_stats WHERE wait_type = 'PAGEIOLATCH_SH';
3. Фоновые ожидания вытесняют настоящие
Если взять sys.dm_os_wait_stats как есть, первые строки топа займут ожидания, которые к производительности не относятся вовсе: планировщик спит, брокер ждёт сообщений, AlwaysOn ждёт уведомлений. На боевом сервере SOS_WORK_DISPATCHER легко забирает 39 % и выталкивает из топа то, ради чего вы туда смотрели.
Минимальный список исключений, который стоит держать в любом таком скрипте:
WHERE wait_type NOT IN ( 'CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK', 'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR','LOGMGR_QUEUE', 'CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT', 'BROKER_TO_FLUSH','BROKER_TASK_STOP','BROKER_TRANSMITTER', 'BROKER_RECEIVE_WAITFOR','CLR_MANUAL_EVENT','CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT','XE_DISPATCHER_JOIN','PREEMPTIVE_XE_DISPATCHER', 'ONDEMAND_TASK_QUEUE','BROKER_EVENTHANDLER','SLEEP_BPOOL_FLUSH', 'DIRTY_PAGE_POLL','SP_SERVER_DIAGNOSTICS_SLEEP','HADR_WORK_QUEUE', 'HADR_TIMER_TASK','HADR_NOTIFICATION_DEQUEUE','HADR_CLUSAPI_CALL', 'PARALLEL_REDO_WORKER_WAIT_WORK','PARALLEL_REDO_DRAIN_WORKER', 'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP','QDS_ASYNC_QUEUE','QDS_SHUTDOWN_QUEUE', 'PWAIT_ALL_COMPONENTS_INITIALIZED','PREEMPTIVE_OS_FLUSHFILEBUFFERS', 'VDI_CLIENT_OTHER','SLEEP_DBSTARTUP','SLEEP_MASTERDBREADY') AND wait_time_ms > 0
И ещё одно, о чём легко забыть: счётчики ожиданий копятся с последнего перезапуска сервера. Прежде чем делать выводы, посмотрите на аптайм.
В каком порядке всё это чинить
Порядок важен не меньше самого списка, потому что первые два пункта дают почти весь эффект, а последний — тот, с которого обычно начинают.
- Статистика. Дёшево, безопасно, даёт результат сразу. Регулярное обновление по горячим таблицам с
FULLSCAN. - Запросы со сканами. Дороже по трудозатратам, но именно здесь лежат секунды пользовательского ожидания.
- Журнал и резервные копии. Это не про скорость, это про то, сколько работы вы потеряете. Считать надо не «когда была последняя полная копия», а «сколько часов работы пропадёт, если сервер умрёт сейчас».
- tempdb. Разложить на 4–8 файлов, если он один. Требует перезапуска, поэтому планируется отдельно.
- Фрагментация индексов. Последней — и с полным пониманием побочек: REBUILD полностью логируется (журнал распухнет), в редакции Standard блокирует таблицу, а после него резко вырастают разностные копии.
Настройки параллелизма в этом списке нет намеренно. Если MAXDOP и порог уже в рабочих значениях — трогать их не надо, сколько бы CX-ожиданий ни было в топе.
Если не хочется собирать это руками
Всё описанное я собрал в одну внешнюю обработку: она печатает скрипты, разбирает их результат и выносит вердикт по каждому показателю — плюс проверяет вторую половину со стороны самой 1С (режим блокировок, режим совместимости, упавшие фоновые задания, таймауты и взаимоблокировки в журнале регистрации). К СУБД обработка не подключается вообще: ни строки подключения, ни пароля — только текст скрипта туда и текст результата обратно.
Публикация — «Чек-ап СУБД под 1С», там же полный список из семнадцати скриптов для MS SQL Server и PostgreSQL и описание того, что разбор говорит по каждому из них.
Если у вас другой подход к диагностике — расскажите в комментариях, что смотрите первым делом. Список «с чего начинать» у каждого свой, и это самое интересное в таких обсуждениях: у меня он выстроился как «статистика → сканы → журнал», но я видел вполне рабочие практики, где первым идёт tempdb.
Вступайте в нашу телеграмм-группу Инфостарт