Вопрос или проблема
У нас есть сервер Windows 2008 R2, работающий на SQL Server 2008. Вдруг процесс SQLServer отказывается использовать более 20% ЦП. На прошлой неделе, при выполнении тяжелого запроса к базе данных, загрузка поднималась до 100%, как и ожидалось. У нас есть этот сервер уже некоторое время, и странно, что он вдруг стал иметь такое ограничение. Это ограничение заставляет наши запросы занимать гораздо больше времени, чем обычно. Никто (по крайней мере, сознательно) не вносил изменения в конфигурацию сервера.
После небольшого расследования я обнаружил представление sys.dm_os_sys_memory. Оно показывает, что ‘доступная физическая память высокая’, но в то же время доступная физическая память составляет 339552 КБ, в то время как всего – 4193848 КБ. Стоит отметить, что это виртуальный сервер, работающий на VMware.
Существует ли где-нибудь в SQL Server установка, устанавливающая максимальное использование ЦП? Я нашел настройки в resource governor, хотя в настоящее время они отключены, как и всегда были.
Мы недавно начали использовать Spotlight для SQL Server от Quest Software. Его база данных воспроизведения находилась на этом сервере недолго сегодня утром, я впервые заметил проблему вскоре после этого, хотя я до этого ничего не запрашивал, так что не знаю, была ли это точка, с которой началась проблема, однако база данных работала, как ожидалось, в пятницу после обеда. Журнал Windows показывает, что следующие настройки были применены к SpotlightPlaybackDatabase, когда она была создана.
- 02/21/2011 08:45:02,spid60,Неизвестно, Установка параметра базы данных TORN_PAGE_DETECTION в ВКЛ для базы данных SpotlightPlaybackDatabase.
- 02/21/2011 08:45:02,spid60,Неизвестно, Установка параметра базы данных MULTI_USER в ВКЛ для базы данных SpotlightPlaybackDatabase.
- 02/21/2011 08:45:02,spid60,Неизвестно, Установка параметра базы данных READ_WRITE в ВКЛ для базы данных SpotlightPlaybackDatabase.
- 02/21/2011 08:45:02,spid60,Неизвестно, Установка параметра базы данных AUTO_UPDATE_STATISTICS в ВКЛ для базы данных SpotlightPlaybackDatabase.
- 02/21/2011 08:45:02,spid60,Неизвестно, Установка параметра базы данных AUTO_CREATE_STATISTICS в ВКЛ для базы данных SpotlightPlaybackDatabase.
- 02/21/2011 08:45:02,spid60,Неизвестно, Установка параметра базы данных ANSI_WARNINGS в ВЫКЛ для базы данных SpotlightPlaybackDatabase.
- 02/21/2011 08:45:02,spid60,Неизвестно, Установка параметра базы данных CONCAT_NULL_YIELDS_NULL в ВКЛ для базы данных SpotlightPlaybackDatabase.
- 02/21/2011 08:45:02,spid60,Неизвестно, Установка параметра базы данных RECOVERY в SIMPLE для базы данных SpotlightPlaybackDatabase.
- 02/21/2011 08:45:02,spid60,Неизвестно, Установка параметра базы данных QUOTED_IDENTIFIER в ВЫКЛ для базы данных SpotlightPlaybackDatabase.
- 02/21/2011 08:45:02,spid60,Неизвестно, Установка параметра базы данных AUTO_CLOSE в ВЫКЛ для базы данных SpotlightPlaybackDatabase.
Могли ли какие-либо из этих изменений настроек изменить настройки, примененные ко всему серверу?
Редактирование #1:
Удалось решить эту проблему, перезапустив SQL Server, не уверен, что именно было причиной проблемы изначально. Несмотря на то, что проблема решена, у меня все еще есть некоторые проблемы ввода-вывода, которые нужно решить, о которых я не был ранее осведомлен.
Редактирование #2:
Проблема повторилась. Решение заключалось в отключении анализа трассировки в Spotlight на SQL Server, именно это и тянуло все вниз.
Проверьте sys.dm_os_waiting_tasks и посмотрите, какие ресурсы ожидания. В основном посмотрите на wait_type и посмотрите, что там. Выполните этот запрос и опубликуйте результаты.
select wait_type, sum(wait_duration_ms) sum_wait_duration_ms, avg(wait_duration_ms) avg_wait_duration_ms, count(*) waits
from sys.dm_os_waiting_tasks
group by wait_type
Возможно, вы столкнулись с аналогичной проблемой, о которой я говорил сегодня утром на моем блоге.
Вы не можете управлять использованием ЦП, но вы можете управлять привязанностью ЦП. То есть, кто-то ограничил SQL Server, чтобы он использовал только один ЦП?
В этом же духе, кто-то изменил глобальную настройку maxdop? Это ограничивает все запросы до одного ЦП, но любой отдельный запрос будет выполняться на одном из доступных ЦП.
Предполагая, что не было изменений в конфигурации привязанности ЦП или MAXDOP, как упоминал gbn, есть несколько возможностей.
Первая заключается в том, что план запроса для вашего запроса изменился, потому что распределение индексов или данные подлежащей таблицы изменились значительно. Попробуйте оптимизировать или перестроить индексы на подлежащих таблицах.
Во-вторых, возможно, у вас теперь ограничение по вводу-выводу, либо чтение данных из основного файла базы данных, либо работа с tempdb (где SQL будет хранить промежуточные части запроса, если он слишком велик для ОЗУ). Используйте perfmon и отслеживайте среднюю длину очереди диска. Она должна быть в среднем меньше, чем количество физических дисков в сервере. Если она резко увеличивается во время вашего “тяжелого запроса”, в то время как ЦП остается низким, ЦП просто ждет ввода-вывода диска и, следовательно, не может работать на 100%, выполняя полезную работу. Если это так, у вас есть несколько вариантов: больше ОЗУ (чтобы сократить необходимость в использовании диска), более быстрый диск (SSD?) или оптимизация запросов, индексов и схемы, чтобы сократить ввод-вывод диска. Последний вариант может иметь наибольшее влияние (буквально улучшая ситуацию в 100 раз и более). Но это также может быть самым сложным, в зависимости от вашей структуры данных и запросов. Ознакомьтесь с планами выполнения SQL; купите несколько книг.
Одно, что вы можете сделать, это увидеть, что именно происходит с процессом, выполняющим запрос. Если вы будете продолжать следить за действиями spid и посмотрите, каков его самый распространенный тип ожидания. Вы, вероятно, обнаружите, что есть ресурс, такой как диск ввода-вывода, на который spid ждет, что означает, что ЦП простаивает, ожидая завершения чтений/записей диска.
Эта проблема была решена перезапуском SQL Server, хотя я не знаю, что стало причиной ее в первую очередь. Спасибо всем за ваши ответы.
Ответ или решение
SQL Server Внезапно Использует Лишь Небольшую Долю CPU: Что Делать?
Проблема, с которой вы столкнулись — это распространённая ситуация при работе с SQL Server, и она может быть вызвана множеством факторов. Давайте рассмотрим возможные причины, а также предложим решения на основе предоставленной вами информации.
1. Проверка Настроек SQL Server
Вам следует проверить несколько настроек SQL Server, которые могут ограничивать использование CPU:
-
CPU Affinity: Возможно, кто-то изменил настройку, ограничивающую SQL Server до использования лишь одного процессора или ограниченной группы процессоров. Используйте следующую команду, чтобы проверить настройки:
SELECT * FROM sys.dm_os_schedulers WHERE status = 'ACTIVE'; -
MAXDOP (Maximum Degree of Parallelism): Проверьте настройку MAXDOP, которая может ограничивать количество процессоров, используемых для выполнения конкретного запроса. Стандартная команда для проверки текущего значения:
EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'max degree of parallelism';
Если настройки ограничены, возможно, их стоит увеличить.
2. Нагрузочные Задачи и Ожидания
Проверьте, каким образом ваши запросы взаимодействуют с системой в целом. Используйте следующую команду для диагностики временных ожиданий:
SELECT wait_type,
sum(wait_duration_ms) AS sum_wait_duration_ms,
avg(wait_duration_ms) AS avg_wait_duration_ms,
count(*) AS waits
FROM sys.dm_os_waiting_tasks
GROUP BY wait_type;Это дадут вам представление о том, какая именно операция занимает ваше время и будет ли это задержка от ввода-вывода, блокировок или каких-либо других ресурсов.
3. Блокировки и Ввод/Вывод
Обязанности вашего сервера могут быть ограничены из-за блокировок. Проверьте текущее состояние подключения, используя:
EXEC sp_who2;Кроме того, чтобы исключить возможность того, что ваши запросыпоскольку возможно, произошло необычное изменение в распределении индексов или данных в таблицах. Проверьте, не стали ли ваши запросы менее оптимальными, что может быть определено изменением распределения данных.
4. Ввод-Вывод как Причина Нагрузки
Часто проблема в производительности SQL Server может быть связана с тем, что сервер становится "тесным" из-за недостатка ресурсов ввода-вывода. Используйте Performance Monitor (Perfmon), чтобы отслеживать среднюю длину очереди диска. Если этот показатель превышает количество физических устройств, значит ваш сервер может быть «ячеистым» и нуждаться в улучшении системы хранения данных.
5. Использование Инструментов Мониторинга
Использование таких инструментов, как Spotlight для SQL Server, может существенно помочь в диагностике проблем. Однако, как вы отметили, активация некоторых функций может вернуть проблемы с производительностью.
- В вашем случае, выключение функции Trace Analysis в Spotlight помогло устранить проблему. Это указывает на то, что настройки инструментов мониторинга могут взаимодействовать с производительностью SQL Server, и их нужно оптимизировать.
Заключение
Ваша проблема с ограничением использования CPU на SQL Server 2008 может быть связана с несколькими факторами, от настроек серверов до нагрузки со стороны запросов. Если перезагрузка сервера устраняет проблему, это может указывать на временные проблемы в самой SQL Server или на взаимодействие с другими программами и инструментами. Регулярный мониторинг и оптимизация вашего SQL Server, включая анализ запросов и управление ресурсами, помогут обеспечить стабильную работу.