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

Откуда: Москва
Сообщений: 192
Добрый день.
Есть MSSQL 2005, примерно 50 активных пользователей. Клиет - MS Access с T-SQL, т.е. через ODBC линки.
Заметил такую неприятную особенность. Одна очень важная, мною созданная функция, в непредсказуемое время начинает тормозить всю систему. Т.е. если пользователи с клиента вызывают процедуру в которой есть эта функция StatINV(), то клиент виснет примерно на 20-30 секунд. Достаточно функцию пере-ALTER-рить и всё начинает быстро работать. Есть ли мысли? В чём м.б. дело?
28 июл 11, 11:09    [11038332]     Ответить | Цитировать Сообщить модератору
 Re: Виснет моя функция, пере-ALTER-ришь её, всё ОК.  [new]
Glory
Member

Откуда:
Сообщений: 104751
При ALTER удаляется план из кэша
Т.е. функция вовсе не виснет, а просто долго работает.
28 июл 11, 11:11    [11038346]     Ответить | Цитировать Сообщить модератору
 Re: Виснет моя функция, пере-ALTER-ришь её, всё ОК.  [new]
LightN
Member

Откуда: Москва
Сообщений: 192
После ALTER, функция мгновенно ускоряется в работе. Все пользователи компании примерно 1-2 недели нормально работают. Потом бац и опять тормоза.
28 июл 11, 11:14    [11038363]     Ответить | Цитировать Сообщить модератору
 Re: Виснет моя функция, пере-ALTER-ришь её, всё ОК.  [new]
Glory
Member

Откуда:
Сообщений: 104751
LightN
После ALTER, функция мгновенно ускоряется в работе. Все пользователи компании примерно 1-2 недели нормально работают. Потом бац и опять тормоза.

Ну так сравните планы выполнений
28 июл 11, 11:15    [11038372]     Ответить | Цитировать Сообщить модератору
 Re: Виснет моя функция, пере-ALTER-ришь её, всё ОК.  [new]
LightN
Member

Откуда: Москва
Сообщений: 192
В функции ничего не меняется, ни пробел ни запятая. Разве может план поменяться на лету, просто в процессе6 работы и вернуться в нормальное состояние после ALTER? Как избежать сбоя плана?
28 июл 11, 11:25    [11038440]     Ответить | Цитировать Сообщить модератору
 Re: Виснет моя функция, пере-ALTER-ришь её, всё ОК.  [new]
Glory
Member

Откуда:
Сообщений: 104751
LightN
В функции ничего не меняется, ни пробел ни запятая. Разве может план поменяться на лету, просто в процессе6 работы и вернуться в нормальное состояние после ALTER? Как избежать сбоя плана?

План может устаревать. Из-за изменения данных, а не кода
28 июл 11, 11:29    [11038463]     Ответить | Цитировать Сообщить модератору
 Re: Виснет моя функция, пере-ALTER-ришь её, всё ОК.  [new]
LightN
Member

Откуда: Москва
Сообщений: 192
Сравнил планы, предположем они отличаются, так что дальше то делать, куда копать?
28 июл 11, 11:42    [11038561]     Ответить | Цитировать Сообщить модератору
 Re: Виснет моя функция, пере-ALTER-ришь её, всё ОК.  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
LightN, можно этот процесс автоматизировать, то есть избавляться от устаревшего плана заранее (до того наступают тормоза). использовать ALTER, либо DBCC FREEPROCCACHE, нужная частота подбирается опытным путём
28 июл 11, 11:55    [11038638]     Ответить | Цитировать Сообщить модератору
 Re: Виснет моя функция, пере-ALTER-ришь её, всё ОК.  [new]
LightN
Member

Откуда: Москва
Сообщений: 192
Спасибо, а если DBCC FREEPROCCACHE поставить в ночной job. Не слишком часто?
28 июл 11, 12:28    [11038873]     Ответить | Цитировать Сообщить модератору
 Re: Виснет моя функция, пере-ALTER-ришь её, всё ОК.  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
LightN,Shakill,

а можно сделать по человече и поправить функцию чтобы она не генерила затратный план выполнения:) но это конечно сложнее, костылей со сбросом кеша:)
28 июл 11, 12:33    [11038917]     Ответить | Цитировать Сообщить модератору
 Re: Виснет моя функция, пере-ALTER-ришь её, всё ОК.  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
WarAnt, ну а как это сделать, если по ходу работы системы на разных наборах данных оптимальные планы вдруг становятся затратными и наоборот, судя по описанию автора?
28 июл 11, 13:42    [11039457]     Ответить | Цитировать Сообщить модератору
 Re: Виснет моя функция, пере-ALTER-ришь её, всё ОК.  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
LightN, частоту джоба подбирайте сами, но кэш надо не весь очищать, а удалять только конкретный план

хэндл плана можно найти так
SELECT plan_handle, st.text
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'%CREATE FUNCION dbo.fn_MyFunction%'
28 июл 11, 13:49    [11039488]     Ответить | Цитировать Сообщить модератору
 Re: Виснет моя функция, пере-ALTER-ришь её, всё ОК.  [new]
SomewhereSomehow
Member

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

во первых, зачем выискивать хендл плана и удалять его так сложно, когда можно просто альтер сделать или пометить процедуру sp_recompile?
Во-вторых, WarAnt, дело говорит, перекомпиляция каждый раз когда "зависнет" - только маскировка проблемы. Лучше переписать процедуру чтобы она при компиляции получала более стабильный план.
Для этого надо найти отличия с "нормальным" планом и понять разницу.

Имхо, тут проблема в том, что иногда процедура автоматически перекомпилируется, вследствии каких-то условий, и при следущем выполнении параметры имеют нетипичное значение и генерируется "плохой" план, который для типичных значений приводит к долгому выполнению. После принудительной перекомпиляции и выполнения процедуры с типичными параметрами - все начинает летать, до следующего случая автоматической рекомпиляции с "плохими" значениями параметров. Вот неплохо бы понять что это за значения и когда это происходит.
28 июл 11, 14:32    [11039726]     Ответить | Цитировать Сообщить модератору
 Re: Виснет моя функция, пере-ALTER-ришь её, всё ОК.  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
SomewhereSomehow, собственно, я и предложил на выбор 2 варианта, альтер или удаление плана ручками. что второе можно делать через sp_recompile - не знал, отсюда и велосипед
но лучше найти причину, согласен
28 июл 11, 15:03    [11039909]     Ответить | Цитировать Сообщить модератору
 Re: Виснет моя функция, пере-ALTER-ришь её, всё ОК.  [new]
Crimean
Member

Откуда:
Сообщений: 13147
это стандартная и непобедимая беда для ms sql
для "хранимых объектов" строится план и кешится
меняется он нечасто. когда - смотрим мсдн
нюанс!
план строится в момент запроса и под те условия, которые были в момент выполнения запроса
пример
есть таблица
в ней 100500 тыщ записей
есть хранимка С ПАРАМЕТРАМИ (multiline udf, не суть)
при одних значениях параметра серверу "по факту" удобнее сканить таблицу. скажем, параметры такие, что надо вернуть 90% записей. при других значениях параметра удобнее применить поиск по индексу ибо возвращается около 2% данных
считаем что 2% запросы у нас составляют 90% всех запросов к этим данным
рестарт сервера
кеш планов чистый
первым приходит 2% запрос
в кеш попадает план 2% версии
серверу щасте - 90% запросов у нас 2% и для них все летает. для 90% запроса в принципе похрен - он и так небыстрый
тут - опа - наступает момент когда серверу надо сменить план (читаем мсдн когда это) и тут - вуаля - приходит 90% запрос. план сделается и ляжет в кеш именно для него. все правильно, но для 2% запросов наступает жопа. и длится она до тех пор, пока не сменится план. а когда это бывает мы уже знаем (почитали мсдн)
однозначного решения нет, проблема "системная", частично может помочь хинт optimize for для проблемного запроса, если вообще он применим - там константу надо писать.. если есть типовая константа, которая для всегда "сдвинет" запрос в сторону 2% плана выполнения - получите вечное щасте для этого объекта. для старых версий сиквела помогали значения по умолчанию для параметров. для 2008 лучше использовать именно optimize for
28 июл 11, 20:50    [11041861]     Ответить | Цитировать Сообщить модератору
 Re: Виснет моя функция, пере-ALTER-ришь её, всё ОК.  [new]
Crimean
Member

Откуда:
Сообщений: 13147
p.s.
по ворробьям из пушки - option ( recompile ) и пробьлемы нет вообще, но это годится только для "нечастых" и "неконкурентных" запросов. + еще и для несложный, а то время компиляции будет больше времени выполнения
28 июл 11, 20:52    [11041864]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить