Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
Genniy
Member

Откуда:
Сообщений: 141
Ребята, привет!

Помогите, пожалуйста, разобраться со следующей проблемой.
На продакшн среде клиента простые запросы с top 50 и около десятка join-ов на другие таблицы около 10 раз в день у пользователей запросы отрабатывают около 50 секунд.
Я данные тяжелые запросы вижу через статистику тяжелых запросов. Но когда пытаюсь выполнить у себя (на этом же сервере и под пользователем, у которого отрабатывало долго), то данный запрос отрабатывает за 1 секунду. И если рассматривать план запроса, то тоже никаких вопросов нет.
Ранее я предполагал, что такое выполнение запроса может быть из-за очень частых обновлений таблиц, к которым обращаемся. Я добавил в выборки nolock. Количество таких тяжелых запросов уменьшилось, но они остались и с хинтами.
Также раз в день поставил принудительный сбор статистики по всем таблицам. Также добавил на ночь перестройку всех индексов. Тяжелые запросы остались.

Как можно идентфицировать проблему? Возможно как-то можно сохранять с тяжелым запросом и план, чтобы потом можно проанализировать?

Инстанс 2012, оперативной памяти под инстанс 14 ГБайт.

Может ли быть причиной такого поведения малое количество оперативной памяти?

Подскажите, кто и как решает данные задачи в своей работе.

Заранее большое спасибо!
19 май 15, 09:10    [17659089]     Ответить | Цитировать Сообщить модератору
 Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Классика.
Локализация проблем производительности
19 май 15, 09:14    [17659116]     Ответить | Цитировать Сообщить модератору
 Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
Genniy
Member

Откуда:
Сообщений: 141
Jovanny, спасибо за ссылку. Буду разабираться.
Но если есть возможность попытать сузить круг поиска и подсказать, куда именно смотреть или какие скрипты, утилиты можно использовать для решения, то буду очень благодарен.
19 май 15, 09:18    [17659134]     Ответить | Цитировать Сообщить модератору
 Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
Glory
Member

Откуда:
Сообщений: 104751
Genniy
Но если есть возможность попытать сузить круг поиска и подсказать, куда именно смотреть или какие скрипты, утилиты можно использовать для решения, то буду очень благодарен.

Смотрите счетчики производительности - попадаение в кэш, очереди к диску
19 май 15, 09:22    [17659154]     Ответить | Цитировать Сообщить модератору
 Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
Genniy
Member

Откуда:
Сообщений: 141
Glory, спасибо!
19 май 15, 09:23    [17659169]     Ответить | Цитировать Сообщить модератору
 Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Ну, и вот это здорово помогает.
http://www.sqlskills.com/blogs/glenn/sql-server-diagnostic-information-queries-for-april-2015/
19 май 15, 09:28    [17659200]     Ответить | Цитировать Сообщить модератору
 Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
Genniy
Member

Откуда:
Сообщений: 141
Jovanny, спасибо. Пока все перечитываю. Нужно много разбирать.
Но забыл упомянуть, что в при задержках выполнения Wait type: PAGEIOLATCH_SH.
Это поможет сузить поиск?
19 май 15, 09:42    [17659257]     Ответить | Цитировать Сообщить модератору
 Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Genniy
Но забыл упомянуть, что в при задержках выполнения Wait type: PAGEIOLATCH_SH.
Это поможет сузить поиск?

Подсказывает, что есть задержка с диском. Но в 99% случаев это ни о чём не говорит.
19 май 15, 09:47    [17659277]     Ответить | Цитировать Сообщить модератору
 Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
Genniy
Member

Откуда:
Сообщений: 141
Jovanny, спасибо!

Сейчас инстансу выделено 14ГБайт из 16ГБайт доступны. Уменьшить веделенное место для инстанса может помочь?
19 май 15, 09:55    [17659320]     Ответить | Цитировать Сообщить модератору
 Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
shur.boot
Member

Откуда:
Сообщений: 3
Была аналогичная проблема с призводительностью на SQL и терминальных серверах. После установки утилиты "MegaRAID Starage Manager" увидели, что почти на всех серверах "умерли" батареи на Raid контроллерах. Контроллеры стали работать в режиме Write Trough. После установки в режим по умолчанию в Write Back (без BBU) все встало на свои места (скорость дисковых операций). Затем заменили батареи на новые (Write Back with BBU) и вуаля.
19 май 15, 11:06    [17659865]     Ответить | Цитировать Сообщить модератору
 Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
Genniy,

смотри планы, выполняя запросы ровно с теми же параметрами.
В нытье, что под тобой все ок, я не верю - значит, делаешь что-то не так. если совсем невмоготу - set user.
19 май 15, 11:15    [17659942]     Ответить | Цитировать Сообщить модератору
 Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
Genniy,

. Я добавил в выборки nolock.

no lock убирай.
только хуже будет


Также раз в день поставил принудительный сбор статистики по всем таблицам.

пока ты не нашел причину "тормозов" - бесполезно. как правило, если данные большие, статистика будет всегда довольно актуальна.


Также добавил на ночь перестройку всех индексов.

это вообще бред, абсолютно бесполезное занятие .
19 май 15, 11:20    [17659973]     Ответить | Цитировать Сообщить модератору
 Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
Genniy
Member

Откуда:
Сообщений: 141
MasterZiv, запросы выполняются долго не постоянно и это никак не зависит от пользователя. Один и тот же запрос сейчас может отрабатывать быстро, а через пол часа уже долго.
19 май 15, 11:21    [17659985]     Ответить | Цитировать Сообщить модератору
 Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
Genniy
Member

Откуда:
Сообщений: 141
MasterZiv
Genniy,

. Я добавил в выборки nolock.

no lock убирай.
только хуже будет

Почему хуже?

Также раз в день поставил принудительный сбор статистики по всем таблицам.

MasterZiv
пока ты не нашел причину "тормозов" - бесполезно. как правило, если данные большие, статистика будет всегда довольно актуальна.

Эта была одна из причин тормозов. Так как в планах были сортировки, которые отрабатывали в tempDB из-за не правильной статистики
MasterZiv
Также добавил на ночь перестройку всех индексов.
это вообще бред, абсолютно бесполезное занятие .

Странно... Когда в базу постоянно влетает/изменяется большое количество данных, то перестройка индексов всегда позволяет ускорить выборки.
Если ты считаешь, что это пред, то аргументируй, пожалуйста
19 май 15, 11:25    [17660027]     Ответить | Цитировать Сообщить модератору
 Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
Glory
Member

Откуда:
Сообщений: 104751
Genniy
Когда в базу постоянно влетает/изменяется большое количество данных, то перестройка индексов всегда позволяет ускорить выборки.

Вы не путаете перестройку индексов с обновлением статистики ?
Индексы перестраивают обычно для уменьшения фрагментации

Genniy
Также раз в день поставил принудительный сбор статистики по всем таблицам.

А вот скажем автоматический сбор/создание статистики отключены ?
19 май 15, 11:28    [17660045]     Ответить | Цитировать Сообщить модератору
 Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Genniy,

Вот посмотрите, я когда-то писал на тему анализа производительности.
15688166
Почитайте, может быть поможет.

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

Далее я выполнил поиск по кэшу планов, чтобы найти планы, в которых индексы сканируются. Нашел одну процедуру. Проверил по трассе, она вызывалась от одного раза в 10 секунд, до нескольких раз в секунду – действительно сканировала эти индексы, и страницы постоянно забивали кэш. Далее создал два нужных индекса, убедившись в плане запроса, что сканирований нет. На этом все, давление на кэш ушло, пока живем, но память серверу уже заказали. =)
19 май 15, 11:32    [17660076]     Ответить | Цитировать Сообщить модератору
 Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
Genniy
MasterZiv, запросы выполняются долго не постоянно и это никак не зависит от пользователя. Один и тот же запрос сейчас может отрабатывать быстро, а через пол часа уже долго.


Один и тот же запрос может запросто выполняться с разными планами, как с разными параметрами, так и с одними и теми же -- план вытесняется из кэша планов -- и всё, оптимизатор вынужден оптимизировать его заново.

Кстати, проверь размер процедурного кэша (кэша планов запросов), или как он там сейчас называется.
19 май 15, 11:42    [17660141]     Ответить | Цитировать Сообщить модератору
 Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
Genniy
Member

Откуда:
Сообщений: 141
Genniy
Также раз в день поставил принудительный сбор статистики по всем таблицам.
А вот скажем автоматический сбор/создание статистики отключены ?


Нет. Не откюлючены. Просто при анализе попадал на ту ситуацию, когда запрос отрабатывал с промахом по статистике. Пересобрал статистику и все заработало. После этого поставил на постоянку.
19 май 15, 11:47    [17660180]     Ответить | Цитировать Сообщить модератору
 Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
Genniy
Member

Откуда:
Сообщений: 141
MasterZiv
Genniy
MasterZiv, запросы выполняются долго не постоянно и это никак не зависит от пользователя. Один и тот же запрос сейчас может отрабатывать быстро, а через пол часа уже долго.


Один и тот же запрос может запросто выполняться с разными планами, как с разными параметрами, так и с одними и теми же -- план вытесняется из кэша планов -- и всё, оптимизатор вынужден оптимизировать его заново.

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


Не помнишь как можно посмотреть размер?
19 май 15, 11:51    [17660218]     Ответить | Цитировать Сообщить модератору
 Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
>>no lock убирай.
>>только хуже будет

>Почему хуже?

Ну, ПАТАМУЧТА.
Объяснять долго и муторно. Но если ты не знаешь приложение и только оптимизируешь производительность, NOLOCK так вот на шару ставить просто НЕЛЬЗЯ. Некорректно. Разные данные могут возвращаться.

Эта была одна из причин тормозов. Так как в планах были сортировки, которые отрабатывали в tempDB из-за не правильной статистики


Нет, не верю. Если бы это была бы одна из причин тормозов, то у тебя уже бы тормозов не было бы.
Эта же фраза: "были сортировки, которые отрабатывали в tempDB из-за не правильной статистики" вообще граничит с бредом -- статистика на сортировку никак не влияет. Сортировка либо может быть в запросе "покрыта" сканированием индекса в нужном порядке, либо нет. Нет -- в 90% случаев. И от статистики это не зависит.



Странно... Когда в базу постоянно влетает/изменяется большое количество данных, то перестройка индексов всегда позволяет ускорить выборки.
Если ты считаешь, что это пред, то аргументируй, пожалуйста


Мне лень, если честно. Почитай 240 статей в интернете на эту тему, по любой СУБД.
Вкратце -- индексы -- это B+-tree, самобаллансируемое дерево. Перестраивать индексы иногда нужно, но это случается в очень специфичных приложениях, и до тех пор, пока ты не увидел у себя на индексе очень низкий fillfactor, перестраивать индекс бесполезно. Хотя впрочем и безвредно, но беда в том, что ты просто делаешь ненужную работу, а нужную -- не делаешь.
19 май 15, 11:51    [17660222]     Ответить | Цитировать Сообщить модератору
 Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
Genniy
Не помнишь как можно посмотреть размер?

Даже больше -- не знаю!
19 май 15, 11:53    [17660238]     Ответить | Цитировать Сообщить модератору
 Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
Genniy
Member

Откуда:
Сообщений: 141
MasterZiv
>>no lock убирай.
>>только хуже будет

>Почему хуже?

Ну, ПАТАМУЧТА.
Объяснять долго и муторно. Но если ты не знаешь приложение и только оптимизируешь производительность, NOLOCK так вот на шару ставить просто НЕЛЬЗЯ. Некорректно. Разные данные могут возвращаться.

Эта была одна из причин тормозов. Так как в планах были сортировки, которые отрабатывали в tempDB из-за не правильной статистики


Нет, не верю. Если бы это была бы одна из причин тормозов, то у тебя уже бы тормозов не было бы.
Эта же фраза: "были сортировки, которые отрабатывали в tempDB из-за не правильной статистики" вообще граничит с бредом -- статистика на сортировку никак не влияет. Сортировка либо может быть в запросе "покрыта" сканированием индекса в нужном порядке, либо нет. Нет -- в 90% случаев. И от статистики это не зависит.



Странно... Когда в базу постоянно влетает/изменяется большое количество данных, то перестройка индексов всегда позволяет ускорить выборки.
Если ты считаешь, что это пред, то аргументируй, пожалуйста


Мне лень, если честно. Почитай 240 статей в интернете на эту тему, по любой СУБД.
Вкратце -- индексы -- это B+-tree, самобаллансируемое дерево. Перестраивать индексы иногда нужно, но это случается в очень специфичных приложениях, и до тех пор, пока ты не увидел у себя на индексе очень низкий fillfactor, перестраивать индекс бесполезно. Хотя впрочем и безвредно, но беда в том, что ты просто делаешь ненужную работу, а нужную -- не делаешь.


Мда. Вижу мы говорим немного о разном. Предлагаю здесь и остановиться. Спасибо!
Если знаешь, что конкретно нужно сделать в моем случае, то было бы хорошо.
Сейчас перечитываю все рекомендации. Не хватает знаний админа БД.
19 май 15, 12:00    [17660298]     Ответить | Цитировать Сообщить модератору
 Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
Glory
Member

Откуда:
Сообщений: 104751
Genniy
Genniy
Также раз в день поставил принудительный сбор статистики по всем таблицам.
А вот скажем автоматический сбор/создание статистики отключены ?


Нет. Не откюлючены. Просто при анализе попадал на ту ситуацию, когда запрос отрабатывал с промахом по статистике. Пересобрал статистику и все заработало. После этого поставил на постоянку.

Т.е. у вас и для базы задано автоматическое обновление статистики, и еще вы это же по расписанию делаете ?
19 май 15, 12:02    [17660318]     Ответить | Цитировать Сообщить модератору
 Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
Genniy
Если знаешь, что конкретно нужно сделать в моем случае, то было бы хорошо.


Конкретно в твоём случае (как и вообще в любом случае проблем с производительностью) надо:
  • Найти самый медленноработающий запрос.
  • посмотреть его план. Выявить проблемы.
  • Создать нужные индексы или поставить нужные хинты.
  • Если пред. невозможно -- переписывать запрос.

    Да, бывают случаи плохой конфигурации сервера или проблем с железом, из-за которых страдает производительность.
    Но это -- 10%. Остальные 90% -- неверные планы. То, что ты попал в 10%, я пока не верю -- никаких данных, говорящих за это, ты тут не дал. Так что успехов.
  • 19 май 15, 12:11    [17660375]     Ответить | Цитировать Сообщить модератору
     Re: Произвольно тормозят запросы. Как можно идентифицировать причину?  [new]
    SomewhereSomehow
    Member

    Откуда: Moscow
    Сообщений: 2480
    Блог
    Genniy,

    Ответ, что не хватает памяти вас не устроил?

    И тип ожидания PAGEIOLATCH_SH и разное время выполнения при одинаковых планах и параметрах - все это с очень большой долей вероятности говорит о том, что памяти не хватает (попали в кэш - быстро, нет данных в кэше - медленно).

    Объем требуемой памяти зависит от размера баз данных и характера работы с ними, об этом вы не говорили, но 14 ГБ для типичной современной продакшн системы - это, как правило, очень мало.
    Вот например, Пол Рэндал собирал статистику
    +
    Картинка с другого сайта.

    Менее 16 ГБ сейчас только примерно у 2%.

    Так что с большой долей вероятности - у вас не хватает памяти. Соответственно, ищите, кто это память потребляет и не отпускает.
    Если вы не думаете, что это память, то пример общего алгоритма я описал выше, можете попробовать им воспользоваться.
    19 май 15, 12:23    [17660453]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
    Все форумы / Microsoft SQL Server Ответить