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

Откуда:
Сообщений: 124
Добрый вечер!

Есть некий сервер, на нем выполняются ночные процедуры.
Во время выполнения есть промежутки когда загрузка процессора достигает 100%.
Я уверен, что есть не оптимальные запросы которые используют parallelism где можно обойтись без него (уже не раз натыкался на такие).
Задача поймать эти сценарии.
Запускать profiler на всю ночь с событием SQL: StmtCompleted, что бы выловить запросы с наибольшим CPU на мой взгляд не совесем оптимально т.к. сильно нагружу сервер.
Есть событие Degree of Parrallelism (меньше нагружает сервер), но по нему не могу увидеть какой сценарий выполняется.
Какими настройками profiler'а можно найти сценарии которые загружают процессор на 100%, при этом минимально нагружать сервер трасой?
30 июл 12, 19:47    [12936905]     Ответить | Цитировать Сообщить модератору
 Re: Процессор 100%  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Meriguan
Добрый вечер!

Есть некий сервер, на нем выполняются ночные процедуры.
Во время выполнения есть промежутки когда загрузка процессора достигает 100%.
Я уверен, что есть не оптимальные запросы которые используют parallelism где можно обойтись без него (уже не раз натыкался на такие).
Задача поймать эти сценарии.
Запускать profiler на всю ночь с событием SQL: StmtCompleted, что бы выловить запросы с наибольшим CPU на мой взгляд не совесем оптимально т.к. сильно нагружу сервер.
Есть событие Degree of Parrallelism (меньше нагружает сервер), но по нему не могу увидеть какой сценарий выполняется.
Какими настройками profiler'а можно найти сценарии которые загружают процессор на 100%, при этом минимально нагружать сервер трасой?
Можно попробовать вот так:
select TOP 50
  total_worker_time/1000. as total_worker_time_ms,
  total_worker_time/execution_count/1000. as avg_worker_time_ms,
  total_elapsed_time/1000. as total_elapsed_time_ms,
  total_elapsed_time/execution_count/1000. as avg_elapsed_time_ms,
  execution_count,
  text,
  query_plan
from sys.dm_exec_query_stats s1 
cross apply sys.dm_exec_sql_text(sql_handle) as  s2
cross apply sys.dm_exec_query_plan(plan_handle) as  s3
order by total_worker_time desc

Выдаст все CPU-intensive запросы. Правда это будут все запросы с момента последнего рестарта сервера или с момента очистки процедурного кэша. Но можно перед началом процедур сбросить кэш, все равно ночью я так понимаю никто не работает - 
DBCC FREEPROCCACHE

Еще можно настроить XEvents (Extended Events)

Или таки запустить трейс по расписанию. Только не client-side, а server-side, ну и не забыть поставить фильтр по CPU.

Только мне не совсем понятен смысл оптимизации. Если ночью пользователи не работают, то кого напрягают высовие показания CPU? Если процесс будет напрягать все CPU на 100% при этом выполнится за 1 час, или 1 CPU и на 3 часа, то по-моему первый вариант предпочтительнее, при условии что другие процесы не выполняются.
30 июл 12, 20:24    [12937012]     Ответить | Цитировать Сообщить модератору
 Re: Процессор 100%  [new]
Meriguan
Member

Откуда:
Сообщений: 124
Mind
Meriguan
Добрый вечер!

Есть некий сервер, на нем выполняются ночные процедуры.
Во время выполнения есть промежутки когда загрузка процессора достигает 100%.
Я уверен, что есть не оптимальные запросы которые используют parallelism где можно обойтись без него (уже не раз натыкался на такие).
Задача поймать эти сценарии.
Запускать profiler на всю ночь с событием SQL: StmtCompleted, что бы выловить запросы с наибольшим CPU на мой взгляд не совесем оптимально т.к. сильно нагружу сервер.
Есть событие Degree of Parrallelism (меньше нагружает сервер), но по нему не могу увидеть какой сценарий выполняется.
Какими настройками profiler'а можно найти сценарии которые загружают процессор на 100%, при этом минимально нагружать сервер трасой?
Можно попробовать вот так:
select TOP 50
  total_worker_time/1000. as total_worker_time_ms,
  total_worker_time/execution_count/1000. as avg_worker_time_ms,
  total_elapsed_time/1000. as total_elapsed_time_ms,
  total_elapsed_time/execution_count/1000. as avg_elapsed_time_ms,
  execution_count,
  text,
  query_plan
from sys.dm_exec_query_stats s1 
cross apply sys.dm_exec_sql_text(sql_handle) as  s2
cross apply sys.dm_exec_query_plan(plan_handle) as  s3
order by total_worker_time desc

Выдаст все CPU-intensive запросы. Правда это будут все запросы с момента последнего рестарта сервера или с момента очистки процедурного кэша. Но можно перед началом процедур сбросить кэш, все равно ночью я так понимаю никто не работает - 
DBCC FREEPROCCACHE

Еще можно настроить XEvents (Extended Events)

Или таки запустить трейс по расписанию. Только не client-side, а server-side, ну и не забыть поставить фильтр по CPU.

Только мне не совсем понятен смысл оптимизации. Если ночью пользователи не работают, то кого напрягают высовие показания CPU? Если процесс будет напрягать все CPU на 100% при этом выполнится за 1 час, или 1 CPU и на 3 часа, то по-моему первый вариант предпочтительнее, при условии что другие процесы не выполняются.



За скрипт спасибо, по нему все тяжелые запросы которые были уже оптимизировал, правда еще не опобликовал на прод сервер.
Очистить процедурный кэш можно, но мне такой воли никто не даст :)
Ночью пользователи не работают и все бы нечего, если бы все ночьные процедуры отрабатывали во вермя(до начала рабочего дня), к тому еще готовится ряд процедур для запуска ночью.
По поводу XEvents (Extended Events) сейчас попробую заборатся что это за событие.
Запускать понятно что на стороне сервера, профайлером было бы самоубийство :)
31 июл 12, 11:14    [12939094]     Ответить | Цитировать Сообщить модератору
 Re: Процессор 100%  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5124
Meriguan,
памяти вашему серверу хватает?
31 июл 12, 11:29    [12939209]     Ответить | Цитировать Сообщить модератору
 Re: Процессор 100%  [new]
Meriguan
Member

Откуда:
Сообщений: 124
Дедушка
Meriguan,
памяти вашему серверу хватает?


Если исходить из данных в sys .dm_os_ring_buffers, то проблем с памятью нет.
Использовал вот этот скрипт:

WITH RingBufferXML
AS(SELECT CAST(Record AS XML) AS RBR FROM sys .dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
)
SELECT DISTINCT 'Зафиксированы проблемы' =
CASE
WHEN XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint') = 0 AND
XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint') = 2
THEN 'Недостаточно физической памяти для системы'
WHEN XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint') = 0 AND
XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint') = 4
THEN 'Недостаточно виртуальной памяти для системы'
WHEN XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]', 'tinyint') = 2 AND
XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint') = 0
THEN'Недостаточно физической памяти для запросов'
WHEN XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]', 'tinyint') = 4 AND
XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]', 'tinyint') = 4
THEN 'Недостаточно виртуальной памяти для запросов и системы'
WHEN XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint') = 2 AND
XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint') = 4
THEN 'Недостаточно виртуальной памяти для системы и физической для запросов'
WHEN XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]', 'tinyint') = 2 AND
XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]', 'tinyint') = 2
THEN 'Недостаточно физической памяти для системы и запросов'
END
FROM RingBufferXML
CROSS APPLY RingBufferXML.RBR.nodes ('Record') Record (XMLRecord)
WHERE XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint') IN (0,2,4) AND
XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]' ,'tinyint') IN (0,2,4) AND
XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint') +
XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]' ,'tinyint') > 0
31 июл 12, 11:33    [12939236]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить