В процессе эксплуатации информационных систем на базе 1С:Предприятия 8.3, работающих с СУБД MSSQL Server, специалисты и пользователи нередко сталкиваются с критической проблемой — хаотичной или постоянной 100% загрузкой процессора сервера. Это явление приводит к значительному замедлению работы, снижению общей производительности и негативно сказывается на продуктивности всех пользователей. Давайте подробно разберем, как мы можем подойти к диагностике этой проблемы, выявить ее корневые причины и применить эффективные методы устранения.
Представим типовую ситуацию: мощный сервер, оснащенный процессором Intel Xeon CPU E5 - 2680 v4 и 512 ГБ оперативной памяти, обслуживает около 200 пользователей 1С 8.3, работающей на MSSQL 2016. В обычных условиях загрузка процессора находится на приемлемом уровне — 20-30%. Однако периодически, без видимых причин, нагрузка на процессор резко возрастает до 100%, что приводит к полной остановке работы. Стандартные процедуры, такие как очистка кэша или реструктуризация баз данных, не приносят ожидаемого результата.
Первостепенной задачей является точное определение источника высокой загрузки ЦП. Часто бывает так, что не сам SQL Server, а другие процессы или службы на сервере становятся причиной проблемы. Мы тщательно проверим операционную систему и затем перейдем к анализу внутри SQL Server.
Мы будем использовать этот встроенный инструмент для сбора подробной информации о загрузке процессора. Нас интересуют следующие счетчики:
Processor: % Processor Time: Этот счетчик покажет нам общую загрузку всех ядер процессора.Processor: % User Time: Мы анализируем время, которое процессор тратит на выполнение кода пользовательских приложений. Если это значение высокое, скорее всего, проблема в приложениях, к которым относится и SQL Server.Processor: % Privileged Time: Этот счетчик демонстрирует время, затрачиваемое процессором на выполнение кода в привилегированном режиме, то есть ядра операционной системы и драйверов. Если % Privileged Time постоянно превышает 90%, это может сигнализировать о проблемах с драйверами устройств, работой антивирусного программного обеспечения, системами резервного копирования или другими компонентами операционной системы, которые вносят значительный вклад в общую загрузку ЦП. В таком случае, прежде всего, мы займемся оптимизацией или выявлением конфликтов этих сторонних служб.Эти инструменты позволят нам быстро идентифицировать, какой именно процесс в операционной системе потребляет наибольшее количество ресурсов ЦП в моменты пиковой нагрузки. Мы сосредоточимся на процессах, потребляющих более 80-90% ЦП, и особенно на sqlservr.exe.
После того как мы подтвердим, что процесс SQL Server (sqlservr.exe) является основным потребителем ресурсов, мы перейдем к более глубокому анализу внутри самой СУБД. В свежих версиях SQL Server Management Studio доступен стандартный отчет "панель мониторинга производительности". Мы воспользуемся им, чтобы провалиться в детали и найти конкретные ресурсоемкие запросы по ЦП, которые вызывают проблему.
Для более детального анализа запросов, потребляющих наибольшее количество ресурсов ЦП, мы будем использовать мощный инструмент — динамические административные представления (DMV). Например, sys.dm_exec_query_stats предоставляет агрегированную статистику использования ресурсов для всех выполненных запросов. Мы можем использовать следующий запрос, чтобы получить список топ-10 запросов по среднему времени использования ЦП:
SELECT TOP 10
qt.text AS QueryText,
qs.total_worker_time / qs.execution_count AS AvgCPUTime_ms,
qs.total_logical_reads / qs.execution_count AS AvgLogicalReads,
qs.execution_count,
qp.query_plan
FROM
sys.dm_exec_query_stats qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY
sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY
AvgCPUTime_ms DESC;
Этот запрос поможет нам выявить "тяжелые" запросы, которые требуют дальнейшей оптимизации.
Функция Query Store, появившаяся в SQL Server 2016, действует как "черный ящик" для производительности запросов. Мы обязательно включим ее для проблемной базы данных. Query Store фиксирует статистику выполнения запросов с течением времени, что позволяет нам легко выявлять запросы, которые постоянно потребляют много ресурсов ЦП, анализировать изменения их планов выполнения и понимать их поведение при различных нагрузках.
Вместо устаревшего SQL Server Profiler, который может значительно нагружать сервер, мы будем использовать более легковесный и настраиваемый механизм — Extended Events. Он позволяет трассировать различные события, включая время ЦП и продолжительность выполнения запросов в продакшн-средах, с минимальным влиянием на производительность системы.
После того как мы определили потенциальных "виновников" среди запросов и подтвердили, что проблема исходит от SQL Server, мы перейдем к его системным настройкам. Многие случаи высокой загрузки ЦП можно значительно улучшить путем правильной конфигурации параметров SQL Server.
Параметр MAXDOP определяет максимальное количество логических процессоров, которые SQL Server может использовать для выполнения одной операции (запроса, перестроения индекса и т.д.) в параллельном режиме. Слишком высокое значение может привести к чрезмерной утилизации ЦП, особенно для запросов, которые выигрывают от параллелизма, но при этом способны перегружать систему множеством конкурирующих потоков. В качестве примера из практики 1С, такая ситуация часто наблюдается при расчете зарплаты в конфигурации 1С:Зарплата и Управление Персоналом (ЗУП), где одна операция может активно задействовать все доступные ядра процессора.
Мы можем настроить MAXDOP на различных уровнях, что дает нам гибкость:
MAXDOP для конкретной базы данных, если это необходимо.OPTION (MAXDOP n), мы можем указать требуемую степень параллелизма для конкретного, особенно ресурсоемкого запроса, не меняя общие настройки.Рекомендации по установке MAXDOP для SQL Server 2016 и более новых версий:
MAXDOP равным или меньшим этому количеству логических процессоров.MAXDOP равным половине от этого числа, но не более 16. Это позволяет сбалансировать параллелизм с избеганием чрезмерной конкуренции за ресурсы.Чтобы изменить этот параметр на уровне сервера, мы используем следующие T-SQL команды:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'max degree of parallelism', 8; -- Пример: устанавливаем значение 8
RECONFIGURE WITH OVERRIDE;
Мы заменим 8 на значение, которое соответствует рекомендациям и архитектуре нашего сервера, а также результатам нашего мониторинга.
Если в нашей системе 1С активно используется большое количество одноразовых или "ad hoc" пакетов запросов, это может привести к неэффективному использованию кэша планов SQL Server. Кэш быстро заполняется уникальными планами, которые редко используются повторно, вытесняя действительно нужные планы.
В SQL Server 2016 и более новых версиях мы можем установить опцию optimize for ad hoc workloads в значение 1. Это значительно улучшит эффективность кэша планов, поскольку для первого выполнения "ad hoc" запроса будет кэширована только небольшая "заглушка" (SQL handle), а полный план запроса будет скомпилирован и кэширован только после повторного выполнения этого же запроса. Это снижает давление на кэш планов и потребление памяти.
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE WITH OVERRIDE;
Хотя недостаток оперативной памяти для SQL Server напрямую не является причиной 100% загрузки ЦП, неправильно выделенная память может привести к увеличению дискового ввода-вывода (из-за активного сброса страниц данных на диск и чтения с диска), что, в свою очередь, косвенно увеличивает загрузку ЦП, так как SQL Server тратит больше времени на ожидание данных с диска и обработку этих данных.
Мы тщательно проверим настройки min server memory (MB) и max server memory (MB). Наша цель — убедиться, что SQL Server имеет достаточно памяти для эффективной работы, но при этом оставляет достаточно свободной памяти для операционной системы и других критически важных процессов. Обычно рекомендуется оставлять от 4 до 8 ГБ для операционной системы и ее служебных нужд, а остальную доступную память выделять SQL Server.
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'min server memory (MB)', 8192; -- Пример: минимум 8 ГБ
EXEC sp_configure 'max server memory (MB)', 491520; -- Пример: 512 ГБ - 20 ГБ для ОС = 492 ГБ
RECONFIGURE WITH OVERRIDE;
Самой распространенной причиной высокой загрузки ЦП в системах 1С являются неэффективные SQL-запросы, а также проблемы с индексацией и актуальностью статистики в базах данных. Мы детально проанализируем эти аспекты и применим меры по их оптимизации.
Запросы, которые внезапно начинают работать некорректно или имеют изначально неоптимальные планы выполнения, могут вызывать значительный рост загрузки ЦП. Это проявляется в долгом времени выполнения, увеличении использования рабочих потоков и большом количестве логических чтений данных с диска или из кэша. Мы будем использовать инструменты Query Store или DMV, как мы обсуждали ранее, чтобы выявить такие запросы.
Решение: Мы будем проводить целенаправленную оптимизацию выявленных ресурсоемких запросов. Это включает:
JOIN).Отсутствие необходимых индексов является одной из наиболее частых причин высокой загрузки ЦП. Без соответствующих индексов SQL Server вынужден выполнять полное сканирование таблиц (table scan) вместо быстрого и эффективного поиска (index seek), что требует значительно больше ресурсов ЦП и дискового ввода-вывода.
Решение: Мы будем регулярно проверять и обслуживать индексы:
WHERE, JOIN, ORDER BY и GROUP BY. Мы рассмотрим создание как покрывающих, так и включающих индексов (INCLUDE).ALTER INDEX REORGANIZE) подходит для индексов с фрагментацией от 5% до 30%, а перестроение (ALTER INDEX REBUILD) — для индексов с фрагментацией более 30%, так как это значительно улучшает производительность операций чтения.sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups и sys.dm_db_missing_index_columns. Они предоставляют ценные рекомендации от оптимизатора запросов о том, какие индексы могли бы улучшить производительность запросов.Оптимизатор запросов SQL Server полагается на актуальную статистику по распределению данных в таблицах для построения наиболее оптимальных планов выполнения. Устаревшая статистика может привести к созданию неоптимальных планов, поскольку оптимизатор будет принимать неверные решения о количестве строк, которые будут возвращены, что, в свою очередь, увеличивает логические чтения и загрузку ЦП.
Решение: Мы будем обеспечивать регулярное обновление статистики. Для больших баз данных, особенно на SQL Server 2016 и более новых версиях, рекомендуется следующий подход:
AUTO_UPDATE_STATISTICS и AUTO_CREATE_STATISTICS (внимательно!): Для очень больших таблиц автоматическое обновление статистики может быть слишком медленным и может вызывать блокировки. В таких случаях мы можем отключить автоматические обновления и взять процесс под полный контроль.WITH SAMPLE 10 PERCENT или 20 PERCENT) в рабочие дни для наиболее часто изменяемых таблиц, и полный скан (WITH FULLSCAN) по выходным для всех таблиц, чтобы обеспечить максимальную точность статистики.Пример команд для обновления статистики:
UPDATE STATISTICS [ИмяТаблицы] [ИмяСтатистики] WITH FULLSCAN;
-- или для более быстрого, но менее точного обновления
UPDATE STATISTICS [ИмяТаблицы] [ИмяСтатистики] WITH SAMPLE 10 PERCENT;
-- Для обновления статистики по всем индексам таблицы
EXEC sp_updatestats; -- Обновляет статистику по всем таблицам и индексам в базе данных
Эти проблемы возникают, когда оптимизатор запросов создает план выполнения, который является оптимальным для первого набора параметров, с которыми был выполнен запрос, но который оказывается крайне неэффективным для последующих вызовов того же запроса с другими значениями параметров. Например, первый параметр мог быть очень селективным (возвращать мало строк), а последующий — совсем нет (возвращать много строк), и оптимизатор ошибочно выбрал неоптимальный план.
Решение:
DBCC FREEPROCCACHE. Однако это затронет всю систему, вызовет повторную компиляцию всех запросов и может привести к временному всплеску загрузки ЦП и задержкам.OPTION (OPTIMIZE FOR UNKNOWN), OPTION (RECOMPILE) для конкретных запросов), изменение кода запросов для избегания "нюхания" параметров или использование более продвинутых функций SQL Server 2017+ (таких как Adaptive Query Processing).Важно помнить, что высокая загрузка ЦП не всегда обусловлена только настройками SQL Server. Мы также должны тщательно рассмотреть факторы, связанные с операционной системой и аппаратным обеспечением, которые могут влиять на производительность и вызывать дефицит ресурсов.
Рабочие нагрузки SQL Server могут испытывать значительное снижение производительности и вызывать высокую загрузку ЦП, если операционная система Windows настроена на "сбалансированный" план электропитания. В этом режиме система может динамически снижать частоту процессора для экономии энергии, что абсолютно недопустимо для высоконагруженных серверов.
Решение: Мы обязательно переключим план электропитания в операционной системе Windows Server на "Высокая производительность". Это обеспечит постоянную работу процессора на максимальной частоте, что критически важно для производительности SQL Server.
Медленное хранилище (будь то традиционные жесткие диски, SAN-хранилище или сетевое хранилище) приводит к медленным операциям чтения и записи данных. Это, в свою очередь, вызывает длительное выполнение запросов и, как следствие, может приводить к высокой загрузке ЦП. SQL Server вынужден компенсировать узкие места ввода-вывода, работая интенсивнее на уровне ЦП в ожидании поступления данных или их записи на диск.
Решение: Мы проведем тщательный анализ производительности дисковой подсистемы с помощью Perfmon (счетчики диска, такие как PhysicalDisk: % Disk Time, Avg. Disk Queue Length, Disk Reads/sec, Disk Writes/sec). При необходимости мы оптимизируем дисковую подсистему, что может включать:
Мы убедимся, что никакие другие приложения или службы Windows, установленные на сервере, не потребляют значительные ресурсы ЦП, конкурируя с SQL Server. Сюда входят:
В высококонкурентных системах, особенно с большим количеством логических процессоров, высокая загрузка ЦП может быть вызвана так называемым "состязанием за спинлоки" (spinlock contention). Это происходит, когда множество потоков ЦП конкурируют за внутренние структуры данных SQL Server, вызывая циклы ожидания. Это проявляется в специфических условиях очень высокой нагрузки.
Решение: Microsoft активно выпускала обновления для SQL Server 2016 и 2017, направленные на устранение или смягчение этой проблемы. Мы убедимся, что на нашем SQL Server установлены последние накопительные обновления (Cumulative Updates), так как они часто содержат важные исправления производительности.
Курсоры, особенно при обработке больших объемов данных или при использовании маленького буфера выборки (fetch buffer size), могут быть чрезвычайно ресурсоемкими. Они приводят к частым контекстным переключениям и повторным запросам к диску/памяти, что значительно увеличивает загрузку ЦП. Мы будем стараться избегать использования курсоров там, где это возможно, предпочитая наборные операции (set-based operations), которые SQL Server обрабатывает гораздо эффективнее.
Частая компиляция запросов — процесс создания плана выполнения — потребляет значительные ресурсы ЦП. Это может быть вызвано различными факторами: изменением схем таблиц, частым обновлением статистики (если не отключено автообновление), активным использованием временных таблиц, или динамическим SQL без должной параметризации. Каждый раз, когда SQL Server компилирует запрос, он тратит ЦП.
Решение: Мы будем стремиться к максимальной параметризации запросов в приложениях 1С (если это возможно через код 1С или через настройку SQL Server). Также мы будем минимизировать изменения в схеме таблиц и поддерживать актуальную, но не чрезмерно часто обновляемую статистику, как было описано выше.
Решение проблемы высокой загрузки ЦП сервера MSSQL, особенно в сочетании с информационной системой 1С, требует комплексного, системного подхода. Мы начали с тщательной диагностики, чтобы точно определить источник проблемы, затем перешли к детальной оптимизации настроек SQL Server, включая степень параллелизма и управление памятью. Далее мы углубились в работу с запросами, индексами и статистикой, что является краеугольным камнем производительности базы данных. Наконец, мы рассмотрели важные факторы на уровне операционной системы и аппаратного обеспечения, которые также могут влиять на общую производительность.
Помните, что регулярный мониторинг, проактивная диагностика и постоянная оптимизация — это непрерывный процесс. Только такой подход поможет поддерживать оптимальную производительность вашей системы 1С и MSSQL, обеспечивая стабильную и комфортную работу пользователей.