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

Откуда:
Сообщений: 6
Добрый день, уважаемые коллеги

Столкнулся с непонятным мне поведением курсоров.

У нас есть приложение, которое читает данные из базы преимущественно с помощью курсоров.
В нем у нас по ночам регулярно запускаются тяжелые обработки. В частности проблема возникает в одной из обработок, которая работает
параллельно в несколько потоков с разными параметрами (параметрами задается, какими товарами обработка должна заняться).

Часто случается, что одна (а может, и несколько) из обработок отрабатывает долго. Например не 2 часа, а часов 8-10.

В общих чертах обработка идет так:
1. Приложение использует для обращения к данным в основном курсоры. Это его неотъемлимое свойство.
2. Курсоры содержат параметры и "подготавливаются" с помощью sp_cursorprepexec каждым из потоков параллельной обработки.
Курсоры создаются с параметрами:
scrollopt = 0x10 (FAST_FORWARD)
ccopt = 0x0001 (READ_ONLY)
rowcount = -1

3. После этого каждый из потоков помнит, что курсор уже подготовлен и следующие разы вызывает его с другими параметрами с помощью sp_cursorexecute.
4. В каждом потоке обработки в начале выполняется старт транзакции и в самом конце обработки транзакция коммитится


По данным из профайлера вижу, что есть порядка 5 запросов, по которым наблюдается такое поведение, если обработка начинает "тупить":
1. В начале обработки sp_cursorexecute по ним выполняется быстро, меньше 30мс
2. Чем дальше, тем sp_cursorexecute начинает выполняться всё медленнее и медленнее - к концу порядка 300мс, то есть падение скорости происходит на порядок.
3. Падение скорости выполнения происходит постепенно и пропорционально по всем этим 5 запросам.
4. Есть явная корелляция в трейсе между значениями колонок CPU и Duration. От начала к концу обработки и CPU и Duration растут пропорционально. Но по количеству чтений (колонка Read) роста нет, количество чтений колеблется в пределах 500-2000.
5. При этом каких-то критических нагрузок на SQL-сервере я не наблюдаю. Ни по ЦП (загрузка ЦП 8-10%), ни по дискам (очереди к дискам нет, активное время работы диска - 20-30%)
6. Монитор активности в SQL Management Studio показывает ожидания ресурсов Buffer I/O порядка 30мс в секунду и столько же для Logging. (По крайней мере я это наблюдал под утро, ближе к концу обработки).
7. Во всех 5 запросов, по которым создаются курсоры, указаны хинты по индексам для всех задействованных таблиц. Так что говорить о разных планах для разных запусков курсора не приходится.
8. Данные в таблицах, к которым происходит обращение изменяются, но незначительно относительно общего количества записей в них. В таблицах по несколько десятков миллионов записей.
9. Под конец выполнения обработки из аналогичных осталась она одна (и я даже пробовал останавливать работавшие обработки других типов).
Каких-то долгих ожиданий из-за блокировок таблиц или индексов я тоже не наблюдал.
Точнее таких блокировок утром я не видел вовсе. Тем не менее, скорость выполнения обработки утром оставалась медленной.
10. Данные, которые обработка перебирает, относительно равномерные. То есть нельзя сказать, что в начале она работает с товарами, которые например продаются недавно и мало, а в конце с товарами, которые продаются давно и часто.
Как минимум, об этом говорит не растущие от начала к концу значения в трейсе в колонке Reads.

Подскажите, в какую сторону можно двигаться с разбором этого дела?
21 авг 15, 07:22    [18049288]     Ответить | Цитировать Сообщить модератору
 Re: Постепенное падение производительности sp_cursorexecute для одного и того же курсора  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 2995
тоже сталкивался с тем, что у курсора в процессе фетча падает скорость.
Решения не нашёл, поэтому стараюсь не использовать курсоры и заменять их другими подходами: цикл по временной таблице и подобное
21 авг 15, 08:16    [18049335]     Ответить | Цитировать Сообщить модератору
 Re: Постепенное падение производительности sp_cursorexecute для одного и того же курсора  [new]
owl83
Member

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

Это понятно, что часто лучший способ оптимизировать работу курсора - это избавиться от него.
У нас кстати, это не одно исполнение курсора и потом фетч, а много-много исполнений с небольшим количеством фетчей в каждом.
Можно конечно на это радикально повлиять, но это нужно сильно глубоко в недра функционала влезать.
21 авг 15, 09:01    [18049447]     Ответить | Цитировать Сообщить модератору
 Re: Постепенное падение производительности sp_cursorexecute для одного и того же курсора  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
owl83
7. Во всех 5 запросов, по которым создаются курсоры, указаны хинты по индексам для всех задействованных таблиц. Так что говорить о разных планах для разных запусков курсора не приходится.
Планы я бы всё таки посмотрел. Со статистикой, что бы понять, что там в каждом элементе происходит, со всей статистикой, количеством выполнений и т.п.

owl83
2. Чем дальше, тем sp_cursorexecute начинает выполняться всё медленнее и медленнее - к концу порядка 300мс, то есть падение скорости происходит на порядок.
А всё это происходит в одном коннекте? Если переоткрыть коннект, то что будет? Впрочем, да, у вас же там одна транзакция...
owl83
4. В каждом потоке обработки в начале выполняется старт транзакции и в самом конце обработки транзакция коммитится
Для каждого шага (один вызов sp_cursorexecute) выполняется старт транзакции, или при старте этого многочасового потока обработки?
21 авг 15, 09:25    [18049540]     Ответить | Цитировать Сообщить модератору
 Re: Постепенное падение производительности sp_cursorexecute для одного и того же курсора  [new]
МуМу
Member

Откуда:
Сообщений: 1134
Было дело давным давно...(для 1С 7.7.) Была бага которую помоему так и не пофиксили.
http://www.softpoint.ru/article.php?id=11

Либо сбой в статистике. А в общем технология древняя вполне возможны баги которые и править уже нет смысла.
21 авг 15, 12:14    [18050707]     Ответить | Цитировать Сообщить модератору
 Re: Постепенное падение производительности sp_cursorexecute для одного и того же курсора  [new]
owl83
Member

Откуда:
Сообщений: 6
alexeyvg
Планы я бы всё таки посмотрел. Со статистикой, что бы понять, что там в каждом элементе происходит, со всей статистикой, количеством выполнений и т.п.

Да, планы постараюсь посмотреть.

alexeyvg
А всё это происходит в одном коннекте? Если переоткрыть коннект, то что будет? Впрочем, да, у вас же там одна транзакция...

alexeyvg
Для каждого шага (один вызов sp_cursorexecute) выполняется старт транзакции, или при старте этого многочасового потока обработки?

Транзакция одна многочасовая. Тоже в планах раздробить на мелкие. То, что она одна, не так критично на самом деле, можно и поделить.
21 авг 15, 12:34    [18050846]     Ответить | Цитировать Сообщить модератору
 Re: Постепенное падение производительности sp_cursorexecute для одного и того же курсора  [new]
owl83
Member

Откуда:
Сообщений: 6
МуМу, спасибо, но у меня проблема на тестовых запросах из статьи не воспроизводится. Я, кстати, не написал, что у нас MSSQL 2012, а не MSSQL 2000, про который в статье пишут.
21 авг 15, 12:42    [18050905]     Ответить | Цитировать Сообщить модератору
 Re: Постепенное падение производительности sp_cursorexecute для одного и того же курсора  [new]
МуМу
Member

Откуда:
Сообщений: 1134
Я к тому что просто была подобная бага. Кстати ее так и не исправили багфиксом а поправили только в 2005. Хотя это возможно и не ваш случай. Для решения проблемы нужно иметь возможность ее воспроизвести, это пожалуй ключевой момент.
21 авг 15, 13:19    [18051166]     Ответить | Цитировать Сообщить модератору
 Re: Постепенное падение производительности sp_cursorexecute для одного и того же курсора  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
owl83
Транзакция одна многочасовая. Тоже в планах раздробить на мелкие. То, что она одна, не так критично на самом деле, можно и поделить.
Я к тому, что, может быть, в большой транзакции при выполнении множества sp_cursorexecute что то там накапливается, и начинает тормозить?

А в общем, для решения нужно действительно как то это уметь воспроизвести, как уже сказал МуМу
21 авг 15, 21:36    [18053591]     Ответить | Цитировать Сообщить модератору
 Re: Постепенное падение производительности sp_cursorexecute для одного и того же курсора  [new]
owl83
Member

Откуда:
Сообщений: 6
alexeyvg, сравнил обычную обработку и разбитую на мелкие транзакции, по транзакции на итерацию.
Разница удивительная - разбитая на мелкие транзакции отработала в 20 раз быстрее при тех же параметрах. 6 минут вместо 2х часов.
Такие дела...
22 авг 15, 12:40    [18054425]     Ответить | Цитировать Сообщить модератору
 Re: Постепенное падение производительности sp_cursorexecute для одного и того же курсора  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
owl83
alexeyvg, сравнил обычную обработку и разбитую на мелкие транзакции, по транзакции на итерацию.
Разница удивительная - разбитая на мелкие транзакции отработала в 20 раз быстрее при тех же параметрах. 6 минут вместо 2х часов.
Такие дела...
Ну вот, видимо, причина похожа на ту багу, про которую писал МуМу (только не с временными таблицами, а с курсором, и не из за одного коннекта, а из за транзакции; так что, разумеется, это не та бага, просто её проявление имеет общие черты)
22 авг 15, 13:04    [18054455]     Ответить | Цитировать Сообщить модератору
 Re: Постепенное падение производительности sp_cursorexecute для одного и того же курсора  [new]
owl83
Member

Откуда:
Сообщений: 6
alexeyvg, ну да, проявления чем-то похожи.
Но всё равно это какое-то интересное поведение. Ведь таблицы, к которым происходит обращение в курсорах, в самой этой обработке не модифицируются.
22 авг 15, 13:27    [18054468]     Ответить | Цитировать Сообщить модератору
 Re: Постепенное падение производительности sp_cursorexecute для одного и того же курсора  [new]
МуМу
Member

Откуда:
Сообщений: 1134
Я думаю что механизмы которые вы используете писались лет 25-ть назад.(в релиз попали 17 лет назад). Разумеется нагрузочные тесты проводились. Но все баги не поймаешь без массового разностороннего использования. Объемов данных таких как сейчас раньше не было. Вот и проявляются потихоньку.
23 авг 15, 11:35    [18056383]     Ответить | Цитировать Сообщить модератору
 Re: Постепенное падение производительности sp_cursorexecute для одного и того же курсора  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
owl83
Ведь таблицы, к которым происходит обращение в курсорах, в самой этой обработке не модифицируются.
Ну, очевидно, дело в каких то внутренних структурах, служащих для самой обработки курсоров.

Как по ссылке про временные таблицы - там тоже таблица удалялась и создавалась - а вот, в коннекте (даже не в транзакции), несмотря на удаление, оставались буферы и т.п.
23 авг 15, 17:03    [18056949]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить