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

Откуда:
Сообщений: 22
 		Всем добрый день.
Столкнулись с такой проблемой:
в непредсказуемое время некоторые хранимки начинают очень долго отрабатывать (сотни секунд). Потом когда пробуешь повторить, они 20мс выдают. Подозреваем локи, но не можем их отловить.

Сервер MS SQL 2017, данные находятся на SSD в raid1, tempdb на другом ssd в raid0. Дисковая очередь до 1 не добирается.
Оперативы 128 Гб, проц Xeon, 64 логических ядра.
Все запросы отрабатываются через хранимые процедуры, то есть, никаких неожиданных запросов нет, все можно замерить.
Есть свой логгер, который отслеживает длительность отработки хранимок, и алертит, если выполнение идет слишком долго. Это позволяет находить узкие места и оптимизировать их.

С помощью логгера эту проблему и поймали - логгер фиксирет долгое время выполнения хранимок. Дальше идешь их анализировать - а они отрабатывают нормально. Все срабатывания группируются по времени, например, с 12:03-12:06 несколько разных хранимок могут начать тупить, а потом дальше логи пустые и в MS SMSS в плане выполнения все нормально.
С журналом событий винды корреляций не замечено тоже - но может не туда смотрим.
Версий несколько - лок, сеть, обновления винды %) (обновления вроде уже как отсекли).

Собственно, вропрос в том, как дальше отлавливать эту проблему. Несколько раз получалось прямо в момент проявления симптома застать БД, но так как непонятно, куда смотреть - то так ничего и не прояснилось.

Рассматриваем вариант приглашения профильного специалиста для проведения анализа или платной консультации. Оплата по безналу от ООО, надо будет подписать NDA.

Модератор: За платными услугами вот сюда: https://www.sql.ru/forum/job


Сообщение было отредактировано: 15 июл 19, 15:29
15 июл 19, 15:26    [21926871]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
Yasha123
Member

Откуда:
Сообщений: 1955
 		ожидания смотрите в момент,
когда тормозит:

select s.login_name,      
       wt.*
from sys.dm_os_waiting_tasks wt
     join sys.dm_exec_sessions s
        on wt.session_id = s.session_id
where s.is_user_process = 1 --and s.session_id <> @@spid
order by wait_duration_ms desc;


или в джоб повесьте с нужным фильтром на длительность ожидания (wait_duration_ms),
пускай в таблицу скидывает.
план и текст к этому всему ищется через sys.dm_exec_requests
15 июл 19, 15:36    [21926882]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
aleks222
Member

Откуда:
Сообщений: 1244
noexp
например, с 12:03-12:06 несколько разных хранимок могут начать тупить, а потом дальше логи пустые и в MS SMSS в плане выполнения все нормально.

Checkpoint?
15 июл 19, 15:46    [21926891]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34757
Блог
 		нагуглите скрипт, которым можно смотреть блокировки,
или же у вас план исполнения кривой, его тоже можно фиксировать, потом сравнить - когда хорошо и когда плохо
15 июл 19, 15:46    [21926893]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34757
Блог
Критик
его тоже можно фиксировать


тут фиксировать - в смысле сохранять
15 июл 19, 15:47    [21926897]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
Yasha123
Member

Откуда:
Сообщений: 1955
Критик
нагуглите скрипт, которым можно смотреть блокировки,
или же у вас план исполнения кривой, его тоже можно фиксировать, потом сравнить - когда хорошо и когда плохо

и если это НЕ блокировки, а банальные ожидания диска?
как выше написали, может это чекпойнт долбит страницы на диск?

ожидания надо смотреть.
что вылезет, то вылезет.
совсем необязательно это именно блокировки
15 июл 19, 15:52    [21926905]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
noexp
Member

Откуда:
Сообщений: 22
Yasha123
ожидания смотрите в момент,
когда тормозит:
...
или в джоб повесьте с нужным фильтром на длительность ожидания (wait_duration_ms),
пускай в таблицу скидывает.
план и текст к этому всему ищется через sys.dm_exec_requests


Ну, в момент тормозов выполнялась эта штука:
-- текущая ситуация на сервере (выполняемые запросы)
select session_id, status, wait_type, command, last_wait_type, percent_complete, qt.text, total_elapsed_time/1000 as [total_elapsed_time, сек],
       wait_time/1000 as [wait_time, сек], (total_elapsed_time - wait_time)/1000 as [work_time, сек]
  from sys.dm_exec_requests as qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
  where session_id >= 50 and session_id <> @@spid
  order by 1

Но она просто показала что да, хранимки долго отрабатывают.
А что дальше - непонятно. План выполнения норм. Что делать с инфой, что она дольше обычного выполняется, хз =(
Сейчас та же самая хранимка и не тупит ни разу.
15 июл 19, 16:03    [21926913]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
noexp
Member

Откуда:
Сообщений: 22
Yasha123
и если это НЕ блокировки, а банальные ожидания диска?
как выше написали, может это чекпойнт долбит страницы на диск?

ожидания надо смотреть.


Ну, про ожидания диска - дисковая очередь пустая, думаю, там бы тоже что-то проявлялась.
Про ожидания скрипт в любом случае заготовил, спасибо.
И про чекпоинт погуглю - что-то я впервые услышал, надо пойти поизучать.
15 июл 19, 16:08    [21926920]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
Yasha123
Member

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

и что было в wait_type?
15 июл 19, 16:24    [21926930]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
noexp
Member

Откуда:
Сообщений: 22
Yasha123
и что было в wait_type?


Да это не то же самое, что я прогонял тогда, как я понял - сейчас заготовил эту хранику и прогоню при воспроизведении, спасибо.
Про чекпоинты мысль интересная, нашел уже статей.
15 июл 19, 16:40    [21926944]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
Yasha123
Member

Откуда:
Сообщений: 1955
noexp
Да это не то же самое, что я прогонял тогда, как я понял - сейчас заготовил эту хранику и прогоню при воспроизведении, спасибо.
Про чекпоинты мысль интересная, нашел уже статей.

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

у нас, например, недавно все висели с ASYNC_NETWORK_IO.
это бэкап по сети копировался,
т.к. им приспичило срочно ресторить.
все выполнялось непомерно долго,
но на самом деле -- мгновенно,
а все висели, т.к. клиент фетчил результат в час по чайной ложке
15 июл 19, 16:52    [21926951]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
noexp
Member

Откуда:
Сообщений: 22
 		UP: Нашел запрос для просмотра статистики типов ожиданий:
http://sqlcom.ru/block-deadlock-and-latch/sql-server-wait-types-library/
И он выдал такую картину:
Картинка с другого сайта.
Не очень понимаю, как это трактовать - в распараллеливание все упирается что ли.
Ну, в общем, пока яснее не стало..
15 июл 19, 17:59    [21926992]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
noexp
Member

Откуда:
Сообщений: 22
noexp
И он выдал такую картину:


К сообщению приложен файл. Размер - 10Kb
15 июл 19, 18:00    [21926993]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
vikkiv
Member

Откуда: EU
Сообщений: 2921
 		насколько тяжелая база?
сними копию (если получится через backup, т.к. detach наверное пока лучше не рисковать)
затем может попробуй что-то из серии ALTER DATABASE ... SET SINGLE_USER WITH ...

кстати эксперименты можешь и на копии делать
(после restore, ну и WITH MOVE куда-нибудь в надёжное и заведомо доступное место)
при этом убив в разрешениях ролей всех реальных пользователей кроме себя
потому что в д.сл. гонятся за kill_sessions - как из пушки по воробьям.
15 июл 19, 18:21    [21927007]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
vikkiv
Member

Откуда: EU
Сообщений: 2921
 		упс, не та тема, игнор пож.
15 июл 19, 18:22    [21927009]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
Yasha123
Member

Откуда:
Сообщений: 1955
noexp
UP: Нашел запрос для просмотра статистики типов ожиданий:
http://sqlcom.ru/block-deadlock-and-latch/sql-server-wait-types-library/
И он выдал такую картину:
Картинка с другого сайта.
Не очень понимаю, как это трактовать - в распараллеливание все упирается что ли.
Ну, в общем, пока яснее не стало..

не надо вам статистику.
надо ожидания ВАШЕЙ ПРОБЛЕМНОЙ СЕССИИ в тот момент, когда она висит.
вам нало узнать, что ждет ваша сп, а не то, что жлали вообще все с момента рестарта
15 июл 19, 18:30    [21927017]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1698
 		noexp,

я бы посоветовал еще query store включить раз у вас версия сиквела позволяет.
с этой штукой сможете видеть в каких случаях происходила деградация плана отдельно взятой хранимки
15 июл 19, 20:16    [21927063]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3984
Yasha123
ожидания смотрите в момент,
когда тормозит:

select s.login_name,      
       wt.*
from sys.dm_os_waiting_tasks wt
     join sys.dm_exec_sessions s
        on wt.session_id = s.session_id
where s.is_user_process = 1 --and s.session_id <> @@spid
order by wait_duration_ms desc;


или в джоб повесьте с нужным фильтром на длительность ожидания (wait_duration_ms),
пускай в таблицу скидывает.
план и текст к этому всему ищется через sys.dm_exec_requests


Profiler можно настроить пусть ловит и складывает в файлы
16 июл 19, 10:33    [21927304]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
Yasha123
Member

Откуда:
Сообщений: 1955
Ролг Хупин
Yasha123
ожидания смотрите в момент,
когда тормозит:

select s.login_name,      
       wt.*
from sys.dm_os_waiting_tasks wt
     join sys.dm_exec_sessions s
        on wt.session_id = s.session_id
where s.is_user_process = 1 --and s.session_id <> @@spid
order by wait_duration_ms desc;


или в джоб повесьте с нужным фильтром на длительность ожидания (wait_duration_ms),
пускай в таблицу скидывает.
план и текст к этому всему ищется через sys.dm_exec_requests


Profiler можно настроить пусть ловит и складывает в файлы

и где именно в профайлере настраивается ловля ожиданий?
16 июл 19, 11:36    [21927367]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
komrad
Member

Откуда:
Сообщений: 5516
Yasha123
ожидания смотрите в момент,
когда тормозит:

select s.login_name,      
       wt.*
from sys.dm_os_waiting_tasks wt
     join sys.dm_exec_sessions s
        on wt.session_id = s.session_id
where s.is_user_process = 1 --and s.session_id <> @@spid
order by wait_duration_ms desc;


или в джоб повесьте с нужным фильтром на длительность ожидания (wait_duration_ms),
пускай в таблицу скидывает.
план и текст к этому всему ищется через sys.dm_exec_requests


если spid известен, то вьюха sys.dm_exec_session_wait_stats в помощь
16 июл 19, 11:41    [21927371]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
Yasha123
Member

Откуда:
Сообщений: 1955
komrad
если spid известен, то вьюха sys.dm_exec_session_wait_stats в помощь

в статистике (sys.dm_exec_session_wait_stats) нет spid-а,
там есть суммарные на весь сервер значения со времен рестарта.
если же вы про sys.dm_os_waiting_tasks, где как раз есть spid,
то чем мой скрипт не угодил?
16 июл 19, 11:45    [21927374]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
Yasha123
Member

Откуда:
Сообщений: 1955
 		komrad,
прошу прощения.
не на ту dmv смотрю.
у нас 2014, там этого нет,
буду иметь в виду на будущее
16 июл 19, 11:47    [21927378]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
komrad
Member

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

я имел ввиду, что если тормозит определенная сессия, то есть вью для сессионных ожиданий
16 июл 19, 11:48    [21927382]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
komrad
Member

Откуда:
Сообщений: 5516
Yasha123
komrad,
прошу прощения.
не на ту dmv смотрю.
у нас 2014, там этого нет,
буду иметь в виду на будущее


да, у автора 2017, эта вью доступна в 2016+
16 июл 19, 11:49    [21927384]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2017. Требуется помощь в обнаружении локов. В т.ч. в виде платных консультаций.  [new]
Yasha123
Member

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

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