Вопрос или проблема
так как я разработчик, я являюсь пользователем SqlServer на своей машине для разработки и редко вижу, что происходит на сервере.
Но в этом проекте клиент также управляет собственным производственным SqlServer, и иногда мы видим, что машина “занята”.
И под “занятой” я имею в виду, что она более 30 секунд работает при 100% загрузке ЦП.
Так вот, прежде всего, это нормальное поведение, когда сервер, работающий в обычном режиме, иногда устанавливает все ядра на 100%?
Так как мы (как не эксперты по sql) пришли к выводу, что это ненормально, мы хотели проверить, ‘что делает sql сервер?’
Возможно, на этом сервере работает 6 приложений, некоторые из них запускаются каждые 5 минут, одно – это вебсайт, который подключен постоянно, одно рассчитывает данные каждую минуту и т. д.
Так что, когда ЦП загружен на 100%, как мы можем проверить, какие приложения это вызывают?
Конечно, мы можем использовать профайлер SQL, чтобы проверить, что отправляется на sql сервер, но это содержит столько строк, что мы не знаем, с чего начать анализ.
Есть ли способ помочь нам начать?
(это как когда у вас дома на рабочем столе: вы ничего не делаете, ЦП загружен на 50% и вы слышите, как гремит жесткий диск, и вы думаете: что ты делаешь!?)
сервер является частью кластера из 2 машин (одна используется, одна резервная), обе с четырьмя ядрами и 16 ГБ оперативной памяти.
Так вот, прежде всего, это нормальное
поведение, когда сервер, работающий в
обычном режиме, иногда устанавливает
все ядра на 100%?
Как разработчик, вы бы и должны знать, что единственный ответ на этот вопрос – “это зависит”. возможно, процесс использует весь ЦП в течение значительного времени – угадайте, что, ответ – да.
Итак, вопрос в основном заключается в том, что делает SQL Server в этот момент (и есть инструменты, чтобы это выяснить). Подключитесь, посмотрите, какие SQL-запросы выполняются и как долго.
Так что, когда ЦП загружен на 100%, как мы
можем проверить, какие приложения это вызывают?
Подключитесь, используя учетную запись SA, посмотрите на динамические представления, чтобы узнать, чем занят SQL Server в данный момент. Возможно, подключите трассировку.
Но сначала убедитесь, что это действительно SQL Server. Довольно редко SQL Server ограничен по ЦП. Первое узкое место обычно это дисковая подсистема.
С помощью SQL Profiler можно удобно отфильтровать шум – добавьте фильтр, чтобы показывались только запросы, выполнение которых заняло больше x секунд. Таким образом, вы можете заметить более длительные операции и исключить все транзакции, которые завершаются без проблем.
Еще более простой подход в многих случаях – это использовать SQL Activity Monitor (или выполнить sp_who2), когда вы находитесь в одном из таких периодов 100% загрузки ЦП. Просмотрите соединения, которые перечислены, чтобы определить, что активно. Видите ли вы, что что-то ждет? Есть ли блокировки, и если да, то что их вызывает?
Если вы не можете определить проблему для конкретного приложения или набора запросов, то можно начать исследовать работу монитора производительности, чтобы просматривать некоторые счетчики и попытаться обнаружить аппаратное узкое место или возможную неисправность.
Динамические представления также представляют удобный способ исследования того, что происходит.
Тогда, возможно, взгляните на некоторые советы здесь.
Если вы используете SQL Server 2005 или более позднюю версию (что я предполагаю на основе вашего тега), вы можете использовать SQL Server Data Management Views (часто называемые DMVs).
Они дадут вам целый ряд представлений о том, что происходит в вашей системе. В вашем конкретном случае, возможно, вам следует выполнить следующий запрос (или его вариацию):
SELECT total_physical_reads
,total_logical_reads
,total_logical_writes
, execution_count
, total_worker_time
, total_elapsed_time
, total_elapsed_time / execution_count avg_elapsed_time
, creation_time
, last_execution_time
, st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;
-- "order by" упорядочит вывод по наибольшему среднему времени выполнения в первую очередь.
Возможно, это маловероятно, но у MySQL есть функция, называемая журналом медленных запросов, которая регистрирует любые запросы, выполнение которых занимает более 1 секунды (настраивается). Вероятно, SqlServer имеет аналогичную функцию?
С помощью журнала медленных запросов вы могли бы определить, есть ли у вас запросы, которые выполняются медленно в вашей системе, или, по крайней мере, вы могли бы проверить, что их нет.
Ответ или решение
Как IT-эксперт, вы хотите понять, что именно происходит с SQL Server, когда сервер сталкивается с высокой нагрузкой на CPU, доходящей до 100%, особенно когда это происходит в продакшн-среде вашего клиента. Это важная задача, так как высокая загрузка CPU может значительно влиять на производительность всех приложений, зависящих от базы данных. Давайте рассмотрим, как вы можете следить за состоянием SQL Server и определять причины такой нагрузки.
Теория
Прежде всего, давайте рассмотрим теоретические аспекты, которые необходимо учитывать. SQL Server может использовать значительные объемы ресурсов CPU по разным причинам: неэффективные запросы, проблемы с индексацией, конкуренция за ресурсы, блокировки и прочее. Это может быть нормальным явлением для некоторых сценариев, например, долгих аналитических запросов или при выполнении сложных расчетов. Однако в большинстве повседневных случаев такая высокая загрузка CPU сигнализирует о проблемах.
Пример
Представьте ситуацию, когда на сервере работают несколько бизнес-приложений, каждое из которых выполняет множество транзакций и запросов. Враспыление ресурсов может вызвать ситуации, когда одно или несколько из этих приложений начинают “забивать” процессор из-за неэффективного или закольцованного запроса.
Применение
Теперь перейдем к практическому подходу для мониторинга и диагностики.
1. Использование SQL Server Profiler
SQL Server Profiler — это мощный инструмент для отслеживания всех запросов, выполняющихся в вашем сервере базы данных. Однако, как вы отметили, профайлер может создавать огромные объемы логов. Поэтому рекомендуется настроить фильтры для отслеживания только тех запросов, которые потребляют значительное количество ресурсов или выполняются долгое время. Установите фильтр, например, на запросы, которые выполняются дольше 5 секунд.
2. SQL Server Activity Monitor и sp_who2
Activity Monitor — это встроенный инструмент, который предоставляет быстрый обзор текущей активности SQL Server. Он покажет активные процессы, запросы, ожидающие выполнения, и текущую загрузку ресурсов. Это хорошая отправная точка для определения, какие приложения или запросы потребляют больше всего ресурсов. Аналогом Activity Monitor является команда sp_who2, которая дает схожую информацию в текстовом виде.
3. Использование динамических управляемых представлений (DMVs)
DMVs предоставляют обширный набор данных о текущем состоянии системы. Например, вы можете воспользоваться следующим запросом для анализа наиболее ресурсоемких операций:
SELECT total_physical_reads,
total_logical_reads,
total_logical_writes,
execution_count,
total_worker_time,
total_elapsed_time,
total_elapsed_time / execution_count as avg_elapsed_time,
creation_time,
last_execution_time,
st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;Этот запрос позволит вам увидеть, какие запросы занимают больше всего времени в среднем.
4. Performance Monitor
Запустите "Performance Monitor" на Windows, чтобы проверить счетчики процессора, памяти и ввода-вывода. Это поможет вам определить, существует ли аппаратное ограничение или узкое место в системе.
5. Логи медленных запросов
SQL Server предоставляет возможность регистрации медленных запросов (в MySQL это называется slow query log), которые выполняются дольше определенного порога. Настройка этой функции позволит вам сосредоточиться на оптимизации только наиболее проблематичных запросов.
Вывод
Высокая загрузка CPU на SQL Server редко является нормой и часто указывает на потенциальные проблемы с производительностью. Используя различные инструменты мониторинга и диагностики, вы можете определить, какие приложения или запросы вызывают проблему, и начать работу по их оптимизации. В условиях продакшн-среды это знание интегрально для обеспечения стабильной работы системы и минимизации простоев. Выявление и устранение проблемных запросов и процессов позволит вам улучшить общее состояние сервера и удовлетворенность конечных пользователей.