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

Откуда:
Сообщений: 15
Есть у меня одна хранимая процедура, у которой наблюдаются пока непонятные мне проблемы с быстродействием.

Тестирование производится следующим образом:

1. Имеется достаточно большой набор данных;
2. Пишется скрипт, которые этот набор данных помещает в таблицы;
3. Запускается хранимая процедура и измеряется время ее выполнения;
4. Набор данных заново помещается в таблицы (только с иными идентификаторами);
5. Снова запускается хранимая процедура и измеряется время ее выполнения на добавленом наборе данных;
... им так много раз.

Дело в том, что при некоторых запусках наблюдается резкое падение быстродействия - процедура начинает работать в 10 раз медленнее по сравнению с предыдущими запусками.
Картина наблюдается следующая:
первые 50 запусков (приблизительно) - быстродействие в норме;
затем - один запуск с резким падением производительности;
последующие 20 запусков - быстродействие в норме;
затем - снова один запуск с резким падением производительности;
последующие 10 запусков - быстродействие в норме;
затем - снова один запуск с резким падением производительности;
и далее - 10 запусков с нормальной производительностью и 1 запуск с плохой повторяются постоянно.

Первое что пришло в голову - происходит перекомпиляция хранимой процедуры.
Поставил опцию with Recompile. Фигушки точно такая же картина.

С чем такое поведение может быть связано еще? Подскажите, гуру...
26 янв 06, 12:46    [2289854]     Ответить | Цитировать Сообщить модератору
 Re: Неясные проблемы с быстродействием хранимой процедуры.  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34268
Блог
Посмотрите в сторону
1. DBCC DROPCLEANBUFFERS и DBCC FREEPROCCACHE при тестировании
2. Обновление статистики/индексов
26 янв 06, 13:18    [2290073]     Ответить | Цитировать Сообщить модератору
 Re: Неясные проблемы с быстродействием хранимой процедуры.  [new]
Kenneth
Member

Откуда:
Сообщений: 15
Спасибо за советы!
А как _правильно_ можно проверить это:
Критик
Посмотрите в сторону
2. Обновление статистики/индексов

Мне приходит в голову только следуеющий способ: изменить филл-фактор индексов и прогнать тестирование заново. В этом случае картина должна измениться.
27 янв 06, 11:46    [2293695]     Ответить | Цитировать Сообщить модератору
 Re: Неясные проблемы с быстродействием хранимой процедуры.  [new]
www.perlscript.ru
Member

Откуда: Москва
Сообщений: 344
А может дело в блокировках?
27 янв 06, 11:53    [2293743]     Ответить | Цитировать Сообщить модератору
 Re: Неясные проблемы с быстродействием хранимой процедуры.  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34268
Блог
exec sp_msforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN' 
exec sp_msforeachtable N'DBCC DBREINDEX (''?'')'
DBCC UPDATEUSAGE(0)

А может и в блокировках)
27 янв 06, 11:55    [2293768]     Ответить | Цитировать Сообщить модератору
 Re: Неясные проблемы с быстродействием хранимой процедуры.  [new]
Kenneth
Member

Откуда:
Сообщений: 15
Похоже дело действительно в индексах.
Добавил в процедуру принудительное переиндексирование при каждом запуске

UPDATE STATISTICS myTable WITH FULLSCAN
DBCC DBREINDEX (myTable)
DBCC UPDATEUSAGE (0, myTable)

Только что с тестирования вернулись вполне приемлемые результаты по быстродействию.
26 фев 06, 15:52    [2391423]     Ответить | Цитировать Сообщить модератору
 Re: Неясные проблемы с быстродействием хранимой процедуры.  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34268
Блог
Ээээ, вообще-то это делают не так часто, не при каждом запуске хп, и во время наименьшей загруженности сервера, попробуйте поставить реиндексацию на обед(в job).
26 фев 06, 16:23    [2391552]     Ответить | Цитировать Сообщить модератору
 Re: Неясные проблемы с быстродействием хранимой процедуры.  [new]
Kenneth
Member

Откуда:
Сообщений: 15
Критик
Ээээ, вообще-то это делают не так часто, не при каждом запуске хп, и во время наименьшей загруженности сервера, попробуйте поставить реиндексацию на обед(в job).


На самом деле тут есть некая дилемма:

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

С другой стороны, нет гарантии, что между плановым перестроением индексов (которое я запланировал в обед, например) не произойдет вставка нескольких сотен тысяч записей в эту таблицу и запуск хранимой процедуры в результате будет происходить в десятки раз медленнее чем обычно.
26 фев 06, 18:31    [2392131]     Ответить | Цитировать Сообщить модератору
 Re: Неясные проблемы с быстродействием хранимой процедуры.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37101
автор
С другой стороны, нет гарантии, что между плановым перестроением индексов (которое я запланировал в обед, например) не произойдет вставка нескольких сотен тысяч записей в эту таблицу и запуск хранимой процедуры в результате будет происходить в десятки раз медленнее чем обычно.

В принципе, чем дольше живет система, тем менее вероятно, что доп. заливка данных перекосит статистику.
26 фев 06, 18:46    [2392182]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить