Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 SQL Server 2005. Большая загрузка процессоров.  [new]
Андриященко Алексей
Member

Откуда: Киев, Украина
Сообщений: 91
Доброго времени суток!

Имеем несколько серверов по филиалам (3 из 12) с одинаковой проблемой. Везде (на всех 12ти) установлено однотипное железо и ПО с точностью до версии.

Конфигурация серверов: HP, Intel XEON 3.2 GHz, 3.50 GB RAM.
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2).

На сервере только SQL Server, под него выделено 3 GB RAM.
В аттаче результат sp_configure. Сейчас AWE отключено в качестве эксперимента, на проблему эта опция не повлияла.
На серверах крутится по одной базе, размером до 10 GB, с количеством коннектов ~ 50 (с обычных ПК и с отдельного терминального сервера).

Происходит следующее...
Периодически возникает ситуация, когда все 4 ядра загружены до 90% а то и более (Kernel Time составляет 20% от общего). При этом не наблюдается запросов, которые так грузят систему. TM показывает, что процессорное время использует исключительно sqlserver.exe. Используя Profiler, не обнаружено запросов (с большим CPUTime), которые так нагружают сервер. Просто все запросы пользователей обрабатываются медленно, но явного "тормоза", который является причиной, нет.
Пока не решили проблему - лечится перезапуском службы SQL, после этого коннектятся пользователи, делают ту же работу (те же запросы к базе) и средняя загрузка не выше 15%. Через определенное время (может быть час, а может и неделя) загрузка растет снова. Иногда ситуация разруливается сама (загрузка резко падает). Возникает, в основном, во время активной работы с базой. Еще есть Jobs, которые регулярно запускаются. Блокировок данных при этом не наблюдается. Реиндексация ничего не изменила, выполняется регулярно.
При чем на остальных 9ти серверах такой проблемы пока не было.

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

Спасибо всем, кто подскажет по делу куда копать...

К сообщению приложен файл (sp_configure.txt - 9Kb) cкачать
26 окт 07, 12:12    [4843490]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2005. Большая загрузка процессоров.  [new]
HLW
Guest
Подключайте счетчики Perfomance monitor: Processor Time, Avg. Disk Queue Length и т.п. и мониторьте периоды пиковой нагрузки.
TM не всегда выдает корректную информацию
26 окт 07, 12:45    [4843728]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2005. Большая загрузка процессоров.  [new]
HLW
Guest
Еще обратите внимание на размер tempdb в период "тормозов" по сравнению с нормальными периодами
26 окт 07, 12:49    [4843768]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2005. Большая загрузка процессоров.  [new]
KI
Member

Откуда: London, UK
Сообщений: 309
Попробуйте отключить паралелизм.
max degree of parallelism           0           64          0            0
И когда будет проблема, возьмите пример вызова ХП и запустите ее в Менеджмент Студии с сохранением экзекюшен плана.
Или лучше,
настройте профайлер на этот запуск и поставте записывать экзекюшен планы:
журналирование каждого стейтемента и евенты:

10 RPC:Completed
Occurs when a remote procedure call (RPC) has completed.

11 RPC:Starting
Occurs when an RPC has started.

12 SQL:BatchCompleted
Occurs when a Transact-SQL batch has completed.

13 SQL:BatchStarting
Occurs when a Transact-SQL batch has started.

40 SQL:StmtStarting
Occurs when the Transact-SQL statement has started.

41 SQL:StmtCompleted
Occurs when the Transact-SQL statement has completed.

44 SP:StmtStarting
Indicates that a Transact-SQL statement within a stored procedure has started executing.

45 SP:StmtCompleted
Indicates that a Transact-SQL statement within a stored procedure has finished executing.

146 Showplan XML Statistics Profile
Occurs when an SQL statement executes. Identifies the Showplan operators and displays complete, compile-time data. Note that the Binary column for this event contains the encoded Showplan. Use SQL Server Profiler to open the trace and view the Showplan.

168 Showplan XML For Query Compile
Occurs when an SQL statement compiles. Displays the complete, compile-time data. Note that the Binary column for this event contains the encoded Showplan. Use SQL Server Profiler to open the trace and view the Showplan.

122 Showplan XML
Occurs when an SQL statement executes. Include this event to identify Showplan operators. Each event is stored in a well-formed XML document. Note that the Binary column for this event contains the encoded Showplan. Use SQL Server Profiler to open the trace and view the Showplan.

Найди наиболее длительно выполняющийся стейтемент и запости экзекюшен план, лучше Showplan XML Statistics Profile.
26 окт 07, 13:00    [4843845]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2005. Большая загрузка процессоров.  [new]
NEXUS_14
Member

Откуда:
Сообщений: 9
При работе на многопроцессорных системах иногда возникает еще одна проблема. Она заключается в том, что SQL Server не всегда корректно производит распараллеливание при выполнении запросов. За счет избыточного числа переключений между процессорами (context switching) скорость выполнения запроса на многопроцессорной системе может оказаться заметно ниже, чем на однопроцессорной. В этой ситуации в вашем распоряжении есть два решения:

- использовать параметр настройки сервера cost threshold for parallelism (его можно настроить при помощи хранимой процедуры sp_configure). Он определяет стоимость запроса, начиная с которой выполнения этого запроса будет распараллеливаться. По умолчанию в SQL Server 2000 и SQL Server 2005 для этого значение установлено значение 5. Это значит, что будут распараллеливаться запросы, ожидаемое время выполнения которых больше 5 секунд (на самом деле такой порог будет заметно меньше 5 секунд, поскольку "калибровка" единиц стоимости производилась в середине 90-х годов при разработке SQL Server 7.0). Увеличение значения этого параметра приведет к тому, что будет распараллеливаться меньшее количество запросов. Однако проблема распараллеливания больших запросов все равно останется;

- использовать флаг трассировки 8687. Он просто запрещает распараллеливание запросов. Кроме решения проблем с некорректным распараллеливанием, его применение дает дополнительные важные преимущества: особо "тяжелый" запрос не сможет захватить все ресурсы всех процессоров, и он повлияет на работу других пользователей в меньшей степени.
26 окт 07, 13:35    [4844126]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2005. Большая загрузка процессоров.  [new]
KI
Member

Откуда: London, UK
Сообщений: 309
Третий вариант
max degree of parallelism поставте в 1, и отлючите этот параллелизм. :-)))
NEXUS_14
- использовать параметр настройки сервера cost threshold for parallelism (его можно настроить при помощи хранимой процедуры sp_configure).
- использовать флаг трассировки 8687. Он просто запрещает распараллеливание запросов.
26 окт 07, 13:38    [4844152]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: SQL Server 2005. Большая загрузка процессоров.  [new]
kirser
Member

Откуда: Киев
Сообщений: 225
NEXUS_14
есть два решения:
- использовать параметр настройки сервера cost threshold for parallelism (его можно настроить при помощи хранимой процедуры sp_configure). Он определяет стоимость запроса, начиная с которой выполнения этого запроса будет распараллеливаться. По умолчанию в SQL Server 2000 и SQL Server 2005 для этого значение установлено значение 5. Это значит, что будут распараллеливаться запросы, ожидаемое время выполнения которых больше 5 секунд (на самом деле такой порог будет заметно меньше 5 секунд, поскольку "калибровка" единиц стоимости производилась в середине 90-х годов при разработке SQL Server 7.0). Увеличение значения этого параметра приведет к тому, что будет распараллеливаться меньшее количество запросов. Однако проблема распараллеливания больших запросов все равно останется;

Доброго дня.
Может кто подскажет какое значение "ost threshold for parallelism" сейчас самое оптимальное. Возможно как-то можно посмотреть среднее значение стоимости запросов?
Спасибо
21 июл 09, 15:02    [7440747]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2005. Большая загрузка процессоров.  [new]
Glory
Member

Откуда:
Сообщений: 104760
kirser

Доброго дня.
Может кто подскажет какое значение "ost threshold for parallelism" сейчас самое оптимальное. Возможно как-то можно посмотреть среднее значение стоимости запросов?
Спасибо

Универсального оптимального значения "сost threshold for parallelism" для всех времен и народов не существует.
Потому что, если бы оно существовало, то в настройке "сost threshold for parallelism" не было бы никакой нужды.
21 июл 09, 15:17    [7440845]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2005. Большая загрузка процессоров.  [new]
Aleksey-K
Member

Откуда: Москва
Сообщений: 3116
Алексей, а меня была аналогичная проблема.
Windows 2003 32бита + SQL 2005
Использовался режим AWE и памяти 8 ГБ, но в настройках сервера не было установлены пределы памяти. Все началось после установки SP3.
Удалось побороть с помощью установки:
Min memory server = 1024
Max memory server = 6144.

С уважением, Алексей
21 июл 09, 15:33    [7440951]     Ответить | Цитировать Сообщить модератору
 Re: SQL Server 2005. Большая загрузка процессоров.  [new]
Glory
Member

Откуда:
Сообщений: 104760
Aleksey-K
Алексей, а меня была аналогичная проблема.

Алексею(который автор темы двухгодичной давности) наверное уже все равно.
21 июл 09, 15:40    [7441007]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить