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

Откуда:
Сообщений: 29
Всем привет.
Иногда случается такое: звонят юзеры и говорят - база (на сиквиле) что-то сильно тупит.
Админа этого самого сиквела, ясное дело, нет и не предвидится.

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

Пробовал сам копаться в литературе по администрированию и статьям, но в них либо очень много "теории", типа SQL Internals и мало практики. А в тех что с практикой вроде как порядок, одни советуют сразу открывать профайлер и смотреть запросы, другие - логи винды.

Сам я сильно новичок в вопросе. Могу делать базовые запросы, простейшие операции админки (создание баз, юзеров и прочие житейские мелочи типа бэкапов).

Заранее спасибо.
6 мар 14, 21:48    [15685577]     Ответить | Цитировать Сообщить модератору
 Re: проблемы производительности, с чего начать  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32167
just4qestions
Хочу попросить знающих спецов набросать список с чего и в каком порядке нужно начинать мониторинг проблем производительности.
С профайлера.
6 мар 14, 22:41    [15685754]     Ответить | Цитировать Сообщить модератору
 Re: проблемы производительности, с чего начать  [new]
Speshuric
Member

Откуда: г. Москва
Сообщений: 129
alexeyvg
С профайлера.
+ счетчиков производительности
7 мар 14, 00:16    [15686169]     Ответить | Цитировать Сообщить модератору
 Re: проблемы производительности, с чего начать  [new]
aleks2
Guest
Ответы неверные.

С обучения писать правильные запросы к правильно спроектированным данным.
7 мар 14, 09:33    [15686786]     Ответить | Цитировать Сообщить модератору
 Re: проблемы производительности, с чего начать  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
just4qestions,

perfmon.exe покажет многое, плюс в SQL Server есть динамические представления, так называемые DMV, из них можно узнать какие ожидания присутствуют в системе. sys.dm_os_wait_stats, sys.dm_os_waiting_tasks. Возможно просто имеете пользовательские блокировки. На пальцах объяснять долго, да и не зачем - есть литература в интернете и хелп - гуглите.
7 мар 14, 09:52    [15686877]     Ответить | Цитировать Сообщить модератору
 Re: проблемы производительности, с чего начать  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Вы задаете нетривиальный вопрос, который не имеет однозначного ответа. На эту тему написаны статьи, и даже целые книги (поищите всякие Performance Guide). Теорию тоже желательно знать, чтобы понимать и интерпретировать какие-то практические результаты. Новичку будет трудно.

Так что простые решения, может просто апегрейдить железо? =)

Но я вкратце (относительно книг и статей=)) постараюсь все-таки сейчас рассказать про свое видение проблемы, на уровне подходов и направлений. Спрятал под спойлер, а то очень уж длинно получилось.

+ Мой подход к поиску проблемы производительности


Прежде всего, необходимо четко определить проблему.

1. Простой случай
Есть конкретный запрос/модуль, который работает медленно.

Попробуйте воспроизвести ситуацию, когда будете пытаться воспроизводить - помните об эффекте прослушивания параметров и то, что локальная переменная оценивается сервером иначе, чем параметр модуля. Т.е. если вы просто достанете запрос из модуля, и попробуете его выполнить, то можете получить другой план, не такой как у пользователя, это ничего не даст. Даже простой вызов модуля (например, процедуры) из SSMS может отличаться по времени от того что делает пользователь из-за разных планов, которые возникают из-за разных настроек соединения. Читать про это можно тут Медленно в приложении, быстро в SSMS

Самый простой способ отловить план, это использовать профайлер и события:
Performance: Showplan XML Statistics profile, Stored Procedures: SP: Starting, SP: Completed, SP: StmtCompleted. Обязательно поставьте фильтр на ObjectID по ID процедуры, иначе, в случае высокой нагрузки, вы еще больше затормозите сервер. Главное помнить, если вы что-то меряете вы влияете на то, что меряете. Кроме того, с последним SP: StmtCompleted нужно быть очень аккуратным! Это событие может сильно нагрузить сервер, т.к. если у вас в процедуре есть не in-line функции, то это событие будет вызываться каждый раз когда вызывается функция. Есть 1 000 000 строк для которых вызывается функция – получите 1 000 000 событий в Profiler, что абсолютно бесполезно и сильно нагружает сервер (признак – возрастают ожидания TRACEWRITE), по этому, возможно придется в фильтр добавить исключающее условие по ID функций.

Как только вы поймали долгую операцию (поле Duration отражает длительность), то смотрите план. Далее все зависит от вашего умения тюнинговать запросы. Но в целом проблема будет понятна. Если сами не справитесь – публикуйте здесь.

2. Сложный случай
Проблема неизвестна, нет конкретного запроса или модуля, который нужно мониторить. Более того, сама система для вас черный ящик, делалась не вами и вам не знакома.

Далее, ситуацию можно разделить на два сценария.

2.1 Проблема есть постоянно, т.е. какая-то операция всегда работает медленно

В таком случае, самый быстрый способ – запустить на короткое время профайлер и попросить пользователя выполнить действие, в зависимости от того, как у вас организована работа клиента с БД, можно мониторить события RPC:Completeed, Batch:Completed. Вы увидите все вызовы процедур или запросов которые инициировал клиент. Находите тот, что долго выполняется, и далее пытаетесь воспроизвести. Действия из п.1.

2.2 Проблема появляется периодически, в разных местах и операциях

В таком случае, необходим комплекс мер.

2.2.1 Собрать общую информацию о системе

Соберите общую информацию о системе:
  • - сервер физический/виртуальный
  • - число ядер, памяти, дисковая подсистема
  • - есть ли на сервере софт помимо сиквела, возможно сиквел испытывает внешнее давление
  • - сколько БД на сервере, и как они расположены, как расположены файлы данных БД, файлы журнала транзакций, tempdb
  • - соберите более конкретную статистическую информацию из административных представлений, лучший набор запросов для этого, который я знаю это SQL Server Diagnostic Information Queries for March 2014 и sp_Blitz™ – Free SQL Server Health Check Scrip.

    Но самое смешное, что если вы не знаете отправной точки (или что принято называть Baseline) и нагрузки (Workload), то эти метрики сами по себе будут если не абсолютно бесполезны, то уж точно мало-полезны. Например, вы узнали, что у вас есть процедура, которая имеет очень много чтений, хорошо это или плохо? Вроде бы плохо, ну а что если эта процедура вызывается раз в день или вообще ночью и практически не влияет на рабочую нагрузку. А вы начнете ее оптимизировать, потратите время, но не достигнете результата. Поэтому, не спешите. Проверьте общую информацию на явные косяки, как то внешний софт, расположение файлов и т.д. (sp_Blitz, даже умеет человеческим языком такие вещи сообщать), но не лезьте в дебри. Возможно, конечно, что-то вы увидите явно сразу, но если вы не знаете эту систему и вы не увидели что-то сразу, то вероятность стремится к нулю, а время на поиски к бесконечности.

    2.2.2 Контакт с пользователями

    Попросите их записывать в течение дня действие, которое «подвисло», хотя обычно выполняется быстро, и время окончания этого действия, и собственно рабочую станцию или логин, под которым пользователь ходит в БД. Точность времени зависит от нагрузки, но в целом, чем точнее будет время, тем вам будет потом легче. До минуты пользователь точно может записывать, просто по экранным часам.

    2.2.3 Сбор счетчиков Perfmon

    Поищите поисковиком следующий плакат по счетчикам, называется «SQL Server Perfmon Counters Of Interest» . Почитайте, выберете несколько, наиболее интересных. Можете начать, хотя бы с общих:
  • Processor: % Processor Time (если сервер не виртуальный)
  • Memory: Available Mbytes
  • SQL Server:Buffer Manager Page life expectancy
  • SQLServer: SQL Statistics : Batch Requests/sec
  • SQLServer: Locks: Lock Waits / Sec: _Total
  • Physical Disk: Avg. Disk sec/Read
  • Physical Disk: Avg. Disk sec/Write
  • System : Processor Queue Length
  • etc.
    Вообще там много разного есть, у каждого свой набор, вот например что советует собирать Brent Ozar: SQL Server Perfmon (Performance Monitor) Best Practices

    Наладьте их сбор и сохранение. Опять же, просто глядя на счетчики, не зная baseline, довольно трудно будет сказать, есть ли проблема. Но, в отличие от простой статистики, тут у вас процесс идет во времени и появляется динамика, можно очень хорошо видеть, моменты когда сервер «проседает», давление на кэш, загрузка процессора и т.д. Вы уже можете наложить эту информацию на отзывы пользователей и определить примерно время, когда это происходит, а также констатировать факт, что проблема есть. С наибольшей вероятностью, определить ресурс, которого недостает серверу (память, диск, процессор). Но на данном этапе пока ничего нельзя сказать о причинах проблемы.

    2.2.4 Наладьте долгосрочную трассировку запросов и вызовов

    Только не делайте это при помощи Profiler-а, и тем более, не сохраняйте при помощи профайлера в таблицу, и не запускайте профайлер на сервере. Это все сильно увеличивает нагрузку на сервер, который и так страдает.

    В идеале, если у вас сервер 2012, то используйте xEvents, они меньше нагружают мониторингом систему и имеют более тонкие настройки.

    Но если нет, то вполне сгодятся процедуры sp_trace_... (профайлер, кстати, их и использует). Пишите в файл, только выберете правильные настройки и расположение файлов трассы.

    Собираете те же события, что и профайлером в п.2.1, но не забудьте:
  • поставить фильтр на Reads > 0 (отсечете всякие set запросы и много другого шума), Duration > 10 000 (отсчете запросы длительностью менее 10 мс, можете еще увеличить это значение, но помните, что может быть очень много мелких запросов, которые сильно нагружают сервер, и выставляя слишком большое время в фильтре, вы просто пропустите проблему). Кстати, насчет Duration – нюанс, в профайлере он отображается в миллисекундах, в трассе и фильтре в микросекундах – нужно учитывать.
  • выбрать только необходимые колонки, чтобы не писать лишнего (я выбирал: EndTime, EventClass, ApplicationName, HostName, SessionLoginName, SPID, DatabaseName, ObjectName, CPU, Duration, Reads, RowCounts, Writes, TextData)
    Я когда-то немного писал про sp_trace, но вам, в отличие от статьи, не нужно собирать никакой агрегирующей информации, можно просто взять за отправную точку, ну и в BOL много примеров.

    Как я уже сказал, поскольку трасса будет писаться в файлы, нужно выбрать правильное расположение файлов, т.к. будет дополнительная нагрузка на запись, которая зависит от вашего workload, фильтров и выбранных столбцов.

    Для примера скажу, мониторинг системы с Batch Requests/sec от 1500 до 15000, т.е. в среднем около 5000-7000 батчей в секунду, с подобными фильтрами за сутки занимает места примерно 200 МБ, так что я положил это туда же где собирается дефолтный трейс – никаких проблем не было. Вы сами смотрите (экспериментальным путем можно), все зависит от вашей нагрузки. Полезная опция указывать в трассировке время отключения, если вдруг вы сами забудете ее остановить, я обычно ставил +сутки от начала трассировки.

    Следующий шаг, загрузить данные из фалов в таблицу, это делается легко, при помощи функции fn_trace_gettable.

    2.2.5 Проведите анализ

    Собираете данные пунктов 2.2.1 – Общая информация, 2.2.2 – Жалобы пользователей, 2.2.3 – Счетчики, 2.2.4 – Трасса. Теперь, у вас есть все, что нужно для анализа. Его сложность зависит от нагрузки и вашего умения. Очевидно, попытаться наложить по времени жалобы, счетчики и трассу, чтобы определить конкретный запрос, для конкретного пользователя, и конкретное состояние системы в данный момент.

    Это тем труднее сделать, чем больше запросов секунду есть в трассе, но в принципе зная время, логин юзера (если ходят под общим логином – сложнее) и операцию (примерный набор процедур) – это можно сделать, даже если в 5 минутном интервале несколько десятков тысяч вызовов.

    Определили проблемные процедуры, возможно уже станет понятно, чего не хватает по состоянию системы на данный момент, а нет см. пункт 1.

    Если планы одинаковые, по счетчикам не видно, то более тонкий поиск проблемы, можно выполнить настроив сессию xEvents для события wait_info, wait_info_external (доступно с 2008), теперь зная имя процедуры, примерное время и поставив фильтр на длительность ожидания. Запускаете профайлер, запускаете сессию xEvents, ловите события вызова этой процедуры профайлером, ожидания xEvents-ами. Увидели в профайлере тот самый «подвисший» вызов, останавливаете обе сессии (профайлер и xEvents) и коррелируете ожидания и проблемный вызов процедуры, например, по SPID. Обычно сразу все становится ясно.

    Из недавних примеров, собственного опыта, это было ожидание PAGEIOLATCH_SH, которое говорит о давлении на buffer pool, что собственно было видно и из счетчика PLE, который сильно падал в определенные моменты. Далее, чтобы определить, кто так забивает кэш, я сделал Job, который раз в 30 секунд собирал статистику из DMV по принадлежности страниц буферного пула объектам (таблицам, индексам) в БД. После этого, отобрал из статистики объекты, которые занимали очень много страниц и удерживали их долгое время. Оказалось что 80% всего рабочего времени, 40% всего буферного пула, занимают всего три широких индекса, все индексы по одной таблице.

    Далее я выполнил поиск по кэшу планов, чтобы найти планы, в которых индексы сканируются. Нашел одну процедуру. Проверил по трассе, она вызывалась от одного раза в 10 секунд, до нескольких раз в секунду – действительно сканировала эти индексы, и страницы постоянно забивали кэш. Далее создал два нужных индекса, убедившись в плане запроса, что сканирований нет. На этом все, давление на кэш ушло, пока живем, но память серверу уже заказали. =)


  • Есть еще много подходов и способов, поиском все это легко ищется. И если вы всерьез решили этим заняться, то приготовьтесь много читать.
    7 мар 14, 12:50    [15688166]     Ответить | Цитировать Сообщить модератору
     Re: проблемы производительности, с чего начать  [new]
    just4qestions
    Member

    Откуда:
    Сообщений: 29
    Всем спасибо.
    Особенно SomewhereSomehow.
    Буду читать.
    7 мар 14, 21:01    [15690754]     Ответить | Цитировать Сообщить модератору
     Re: проблемы производительности, с чего начать  [new]
    tomcat2
    Member

    Откуда:
    Сообщений: 69
    just4qestions,

    Очевидно начинать нужно с изучения Execution Plan. На предмет "бутылочных горлышек" тоесть операция с нелогично большим % времени выполнения. Там же вам дадут рекомендации по индексам. Профайлер это конечно круто но муторно и не всегда адекватно, его данные нужно уметь правильно интерпретировать.
    9 мар 14, 10:14    [15694923]     Ответить | Цитировать Сообщить модератору
     Re: проблемы производительности, с чего начать  [new]
    Ivan Durak
    Member

    Откуда: Minsk!!!
    Сообщений: 3787
    tomcat2
    just4qestions,

    Очевидно начинать нужно с изучения Execution Plan. На предмет "бутылочных горлышек" тоесть операция с нелогично большим % времени выполнения. Там же вам дадут рекомендации по индексам. Профайлер это конечно круто но муторно и не всегда адекватно, его данные нужно уметь правильно интерпретировать.

    умора... цитирую
    автор
    звонят юзеры и говорят - база (на сиквиле) что-то сильно тупит.

    план ЧЕГО ты собираешься изучать???
    Так что сперва в профайлер фтыкаем
    10 мар 14, 11:36    [15698361]     Ответить | Цитировать Сообщить модератору
     Re: проблемы производительности, с чего начать  [new]
    just4qestions
    Member

    Откуда:
    Сообщений: 29
    Нашел вот такую книгу, вроде все что советовали выше в оглавлении имеется.
    Судя по отзывам единственный минус книги - это индийский английский))
    А так - куча практики и ссылки к материалам для восполнения теоретических знаний.
    Может кто-то уже читал, что скажете?

    Microsoft-Server-Performance-Tuning-Cookbook
    10 мар 14, 12:17    [15698532]     Ответить | Цитировать Сообщить модератору
     Re: проблемы производительности, с чего начать  [new]
    vi0
    Member

    Откуда:
    Сообщений: 312
    советую обратить внимание на методику APDEX
    http://www.ravepoint.narod.ru/aticles/tecnology/2methodics/2_1.htm

    с учетом этого
    just4qestions
    Иногда случается такое: звонят юзеры и говорят - база (на сиквиле) что-то сильно тупит.
    Админа этого самого сиквела, ясное дело, нет и не предвидится.

    Хочу попросить знающих спецов набросать список с чего и в каком порядке нужно начинать мониторинг проблем производительности.

    и этого
    SomewhereSomehow
    Но самое смешное, что если вы не знаете отправной точки (или что принято называть Baseline) и нагрузки (Workload), то эти метрики сами по себе будут если не абсолютно бесполезны, то уж точно мало-полезны. Например, вы узнали, что у вас есть процедура, которая имеет очень много чтений, хорошо это или плохо? Вроде бы плохо, ну а что если эта процедура вызывается раз в день или вообще ночью и практически не влияет на рабочую нагрузку. А вы начнете ее оптимизировать, потратите время, но не достигнете результата. Поэтому, не спешите.
    13 мар 14, 08:35    [15714827]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить