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

Откуда:
Сообщений: 178
Привет всем.

MSSQL 2008 R2

У меня стоит задача анализировать нагрузку на SQL Server. На уровне сервера всё понятно: через perfmon (хотя, конечно есть вопросы). А мне надо анализировать нагрузку в разрезе БД. Т.е. сколько CPU, I/O, Memory кушали запросы в базу.
У меня несколько комунальных серверов и надо порой решать какую базу куда двинуть, чтоб железки с нагрузкой справлялись.

Вторая задача, это анализ запросов в БД. Проблема в том, что хочется видеть и запросы и планы в XML, чтоб потом быстренько повыковыривать из планов какие индексы, как использовались.

Есть SQLSP шаблон "Tuning" который отлично показывает запросы и нагрузку по каждому запросу, но планов в них нет, получается что надо выцеплять имена объектов из SQL. Есть событие "Performance Statistics", в нём планы и интересная инфа по нагрузке на саму компиляцию. Но как "Performance Statistics" увязать с событиями что в "Tuning"? В описании к "Performance Statistics" есть указание на sys.dm_exec_query_stats и т.д., но что же получается, мне с трассировкой включённой, ещё и job включать, который sys.dm_exec_query_stats дёргает?
Попробовал, добавить к "Tuning" событие "Showplan XML", получилось почти что надо, но в MSDN написано, что это "Showplan XML" систему грузит. А мне хотелось бы на сутки включать трассировку, и не хотелось бы чтоб всё колом встало.
Есть ещё идея просто раз в 10 минту sys.dm_exec_query_stats дёргать и вытаскивать оттуда все выполненные за последние 10 минут запросы с планами. Выглядит это как-то криво, неужели трассировки нет подходящей?

Спасибо, заранее.

PS: Порылся в инете, не нашёл ничего подходящего. Если направите в нужное место ссылкой, тоже буду благодарен.
10 сен 13, 13:58    [14819905]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
Glory
Member

Откуда:
Сообщений: 104751
ZOOKABAKODER
А мне надо анализировать нагрузку в разрезе БД. Т.е. сколько CPU, I/O, Memory кушали запросы в базу.

А если запрос оперирует объектами из нескольких баз ?
10 сен 13, 14:00    [14819922]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
ZOOKABAKODER
Member

Откуда:
Сообщений: 178
Ну это уже вопрос анализа, мне б научиться информацию получать, а уж обработать найду как. И не думаю что таких запросов подавляющее большинство.
10 сен 13, 14:03    [14819940]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
Glory
Member

Откуда:
Сообщений: 104751
У событий SQL:StmtCompleted есть DatabaseID, CPU, Reads, Writes
10 сен 13, 14:11    [14820022]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
o-o
Guest
ZOOKABAKODER
...
Есть ещё идея просто раз в 10 минту sys.dm_exec_query_stats дёргать и вытаскивать оттуда все выполненные за последние 10 минут запросы с планами. Выглядит это как-то криво, неужели трассировки нет подходящей?

чего-то такое уже изобретено:

Data Collection
10 сен 13, 14:14    [14820052]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
ZOOKABAKODER
Member

Откуда:
Сообщений: 178
Glory,
в шаблоне SQLSP "Tuning" SQL:StmtCompleted есть это событие. Спасибо всёравно.

o-o,
Интересная штука, посмотрю. Спасибо.
10 сен 13, 15:41    [14820783]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
МуМу
Member

Откуда:
Сообщений: 1134
К сожалению текущими API нет возможности получить планы запросов по тяжелым запросам. То есть получить план запросов только для запроса с количеством реадс более 100000. (По крайней мере на 2008 так было.) Включать получение планов для всех запросов- это серьезно нагрузить систему! Вопрос отнюдь не риторический, почему нет такой возможности?! Ведь можно было бы план запросов сохранять асинхронно и складывать в отдельное хранилище в памяти разумеется. А затем по некоторому идентификатору его сопоставлять с идентификатором запроса и если он проходит фильтрацию - писать в трассу. В этом случае система не нагружается серьезно и задача важная выполняется. Видимо не сделано потому как придется серьезно(а может и не очень:)) переписывать механизмы трассировки. На этот факт говорит и то условие что планы тяжелых запросов пишутся в специальную вьюху(начиная с 2008го). Поэтому в нашем продукте мониторинга получается именно таким корявым образом. Отдельно собирается трасса и отдельно к ней сопоставляется запрос и план(xml) из вьюхи. Разумеется там есть определенная доля погрешности(ну это уже долго рассказывать)
10 сен 13, 18:51    [14822174]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
МуМу
Member

Откуда:
Сообщений: 1134
А с другой стороны если посмотреть насколько бедные АПИ мониторинга производительности например DB2 то наверное жаловаться не приходиться. Поэтому что есть - тоже не плохо.
10 сен 13, 18:55    [14822192]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34998
Блог
МуМу,

откройте что-ли для себя

sys.dm_exec_query_stats (а конкретнее plan_handle)
sys.dm_exec_sql_text

и другие системные представления.

Скоро отметим 10-летие их появления.
10 сен 13, 19:18    [14822238]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
МуМу
Member

Откуда:
Сообщений: 1134
Ура,Критик, открыл!!!;) Вообще то я про них в том числе писал, будьте внимательней - (" На этот факт говорит и то условие что планы тяжелых запросов пишутся в специальную вьюху(начиная с 2008го)"). В 2005-ом и 2008-ом функционал этих представлений несколько поменялся. Не помню уже детально на в 2005-ом чего то важного не хватало.
В некоторых случаях их не достаточно. Было бы важно если бы в трассе планов запросов(запросов самих в том числе) реализовали фильтрацию по дюрайшену, ЦПУ и реадс.
10 сен 13, 19:26    [14822261]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34998
Блог
МуМу
Было бы важно если бы в трассе планов запросов(запросов самих в том числе) реализовали фильтрацию по дюрайшену, ЦПУ и реадс.


ну, вставьте сюда "where ваш фильтр",
либо вы о чем-то своем рассуждаете )

-- запросы с высокими издержками на ввод-вывод
SELECT TOP 10
       [Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count,
       [Total IO] = (total_logical_reads + total_logical_writes),
       [Execution count] = qs.execution_count,
       [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE
                                                                               WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
                                                                               ELSE qs.statement_end_offset
                                                                             END - qs.statement_start_offset)/2),
       [Parent Query] = qt.text,
       [DatabaseName] = DB_NAME(qt.dbid)
  FROM sys.dm_exec_query_stats qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
  ORDER BY [Average IO] DESC


-- запросы с высоким использованием ресурсов ЦП
SELECT TOP 10
       [Average CPU used] = total_worker_time / qs.execution_count,
       [Total CPU used] = total_worker_time,
       [Execution count] = qs.execution_count,
       [Individual Query] = SUBSTRING(qt.text,qs.statement_start_offset/2, 
         (CASE
            WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
            ELSE qs.statement_end_offset
          END - qs.statement_start_offset)/2),
       [Parent Query] = qt.text,
       [DatabaseName] = DB_NAME(qt.dbid)
  FROM sys.dm_exec_query_stats qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
  ORDER BY [Average CPU used] DESC;
10 сен 13, 19:33    [14822282]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
МуМу
Member

Откуда:
Сообщений: 1134
Ну сперва. Давайте для начала мне время начала выполнения запроса, его spid и гарантию его нахождения в представлении.(спустя час)
10 сен 13, 19:45    [14822325]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
МуМу
Member

Откуда:
Сообщений: 1134
Отдельная тема. К слову таким планам - а точнее статистике и соответственно планам доверять нельзя на сто процентов ни в коем случае в случае отсутствия регулярного пересчета статистики. (планируемое количество строк рекордсета может вводить в ступор как оптимизатор MSSQL так и спеца который будет читать этот план).
10 сен 13, 19:56    [14822351]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
ZOOKABAKODER
Member

Откуда:
Сообщений: 178
Критик,
О! Приятно видеть человека, который знает и использует синтаксис "<alias> = <expression>" вместо кашеобразного "<expression> as <alias>". :-)
sys.dm_exec_query_stats тру... но мне всётаки больше хотелось реализовать через trace. И я люблю не sql_handle, а plan_handle раскрывать, т.к. план всёравно query text содержит и его анализоровать прощще.

И ещё не забываем про DTA и
sys.dm_db_missing_index_columns
sys.dm_db_missing_index_details
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group_stats

МуМу, я сейчас присматривался к Trace Event:"Performance Statistics", могу сказать, что в целом она показывает почти всё. Т.к. в событии с EventSubClass = 3 (QueryStats) вложен xml со всей статистикой: cpu, r/w... А план к которому информация относится, можно найти по PlanHandle. Такчто не всё так плохо выходит.
10 сен 13, 20:12    [14822409]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
МуМу
Member

Откуда:
Сообщений: 1134
Я еще раз напоминаю, что как я указал в своем первом посте - "корявенько" так и делаем. Но есть ньюансы как говорится. Приходится стабильно сканировать вьюхи и сопоставлять трассе.(по сути асинхронно выдираем из вьюхи) К слову этот метод вносит более существенную доп. нагрузку на систему, чем мог бы быть при правильной архитектурной реализации. Если это делать слепками с большим интервалом то можно много не досчитаться(не сопоставить).
10 сен 13, 20:22    [14822442]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
МуМу
Member

Откуда:
Сообщений: 1134
А не сделано могу сказать почему.(мое имхо) Потому что в трассе переходя с 7-ой версии по 2000 был принят стандарт "ХХХ"(название не помню была отдельная авторитетная статья) предполагающий непрерывность трассировки. (хотя даже сейчас может возникнуть событие, - типа не успела, не виновата). В соответствии с этим стандартом пришлось жертвовать синхронностью и производительностью. Запись плана запроса в xml - это доп. издержки и большие. С одной стороны план запроса есть всегда, с другой стороны объемна информация на запись и парсинг в xml. Получается не выход писать его синхронно - это огромные потери. С другой стороны асинхронно писать сложно. Нужно отдельным сервисом складывать планы запросов отдельно и сопоставлять их трассе. С учетом ранее принятой концепции есть шанс не сопоставить(в онлайне) в трасссе (особенно в профайлере) запрос к плану запросов. (Если кто не знает как открытый профалер иногда может подсадить производительность - рекомендую почитать статьи ) Поэтому найден компромисс! Отдельный поток складывает во вьюхи планы запросов. То есть в профайлере их открыть невозможно. Но при желании сопоставить трассы (запрос) к результатам вьюх (план запроса) возможно. Только делать нужно самому на свой страх и риск.
10 сен 13, 20:36    [14822515]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
МуМу
Member

Откуда:
Сообщений: 1134
Сейчас например не хватает непрерывной очереди запросов. Что бы можно было бы по ней сканировать по диапазонам. Пускай бы она была ограниченной.(то есть если поток большой и памяти мало - то что успел то успел. вытеснялось бы по принципу фифо) Впрочем эта тема очень объемная...
10 сен 13, 20:39    [14822529]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
МуМу
А не сделано могу сказать почему.(мое имхо) Потому что в трассе переходя с 7-ой версии по 2000 был принят стандарт "ХХХ"(название не помню была отдельная авторитетная статья) предполагающий непрерывность трассировки. (хотя даже сейчас может возникнуть событие, - типа не успела, не виновата). В соответствии с этим стандартом пришлось жертвовать синхронностью и производительностью. Запись плана запроса в xml - это доп. издержки и большие. С одной стороны план запроса есть всегда, с другой стороны объемна информация на запись и парсинг в xml. Получается не выход писать его синхронно - это огромные потери. С другой стороны асинхронно писать сложно. Нужно отдельным сервисом складывать планы запросов отдельно и сопоставлять их трассе. С учетом ранее принятой концепции есть шанс не сопоставить(в онлайне) в трасссе (особенно в профайлере) запрос к плану запросов. (Если кто не знает как открытый профалер иногда может подсадить производительность - рекомендую почитать статьи ) Поэтому найден компромисс! Отдельный поток складывает во вьюхи планы запросов. То есть в профайлере их открыть невозможно. Но при желании сопоставить трассы (запрос) к результатам вьюх (план запроса) возможно. Только делать нужно самому на свой страх и риск.
я не пойму, вы все еще на 7ке или на 2000ом?
Трассировка давно уже переработана и называется теперь XEvents, неужели не слышали?
11 сен 13, 05:08    [14823718]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
МуМу
Member

Откуда:
Сообщений: 1134
Mind
Многие работают еще на 2000,2005. Так что для совместимости приходится учитывать все варианты. К тому же профайлером никогда и не пользуюсь - только трассы с фильтрацией в файл.(что бы не было провалов можно их делать с небольшим нахлестом по времени). Насчет Xevents - насколько я понимаю появились с 2008? Пожалуй нужно будет еще раз почитать на эту тему. Подскажите ссылочки что поинтересней.
11 сен 13, 08:32    [14824030]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
ZOOKABAKODER
Member

Откуда:
Сообщений: 178
МуМу
Mind
...Насчет Xevents - насколько я понимаю появились с 2008?

С 11 (SQL 2012) они появились. Основное удобство в ненужности писать адское количество вызовов sp_trace_... процедур, всё реализуется инструкцией новой. По части вывода: грубо говоря, те же trc файлы с плюшками. Основная плюшка, возможность не писать трассу а писать только количество событий которые пропустил фильтр.
11 сен 13, 17:54    [14827847]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
Гость333
Member

Откуда:
Сообщений: 3683
ZOOKABAKODER
МуМу
Mind
...Насчет Xevents - насколько я понимаю появились с 2008?

С 11 (SQL 2012) они появились.

МуМу прав — с 2008.
http://technet.microsoft.com/en-us/library/cc645579(v=sql.100).aspx
Manageability Enhancements (Database Engine)

SQL Server 2008 introduces SQL Server Extended Events, an event infrastructure for server systems.
11 сен 13, 17:59    [14827868]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
МуМу
Mind
Многие работают еще на 2000,2005. Так что для совместимости приходится учитывать все варианты. К тому же профайлером никогда и не пользуюсь - только трассы с фильтрацией в файл.(что бы не было провалов можно их делать с небольшим нахлестом по времени). Насчет Xevents - насколько я понимаю появились с 2008? Пожалуй нужно будет еще раз почитать на эту тему. Подскажите ссылочки что поинтересней.
http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/01/a-xevent-a-day-31-days-of-extended-events.aspx
11 сен 13, 21:43    [14828540]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
ZOOKABAKODER
МуМу
Mind
...Насчет Xevents - насколько я понимаю появились с 2008?

С 11 (SQL 2012) они появились. Основное удобство в ненужности писать адское количество вызовов sp_trace_... процедур, всё реализуется инструкцией новой. По части вывода: грубо говоря, те же trc файлы с плюшками. Основная плюшка, возможность не писать трассу а писать только количество событий которые пропустил фильтр.
Основные плюшки, это: меньшая нагрузка на систему, возможность получать информацию, которой нет в профайлере и конечно же поддержка в следующих версиях MS SQL Server.
11 сен 13, 21:46    [14828545]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
ZOOKABAKODER
Member

Откуда:
Сообщений: 178
Mind
Основные плюшки, это: меньшая нагрузка на систему, возможность получать информацию, которой нет в профайлере и конечно же поддержка в следующих версиях MS SQL Server.

Меньшая нагрузка это не плюшки, это комплексный обед: каму нужна система слежения, которая тормозит исследуемую. :-) Того что нет в Profiler, есть в Perfmon - не спорю что плюшка, просто не так бесило её отсутствие... хотя м.б. там и есть что-то уникальное, я смотрел только то что мне надо.

Чесслово, в прошлом годе, как поставил 11 сиквел, начал тыкать во все плюсики и увидел Extended events, а в 10 и 10.5 не видел. Ошибочка... простите.
11 сен 13, 22:41    [14828650]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг нагрузки  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
ZOOKABAKODER
Чесслово, в прошлом годе, как поставил 11 сиквел, начал тыкать во все плюсики и увидел Extended events, а в 10 и 10.5 не видел. Ошибочка... простите.
Вот оно поколение "окошек". Если что-то нельзя сделать через UI это не значит, что этого нет :)
12 сен 13, 04:08    [14829029]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить