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

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Всем доброе время суток!

считаю время выполнения процедуры вот таким методом

declare @debugDate datetime;
set @debugDate = getdate();
  
exec dbo.spSomeStoredProcedure;

print cast(datediff(ms, @debugDate, getdate()) as nvarchar); 


причём внутри этой процедуры есть такой же "расчёт"

Тот что "внутри" выдаёт время "Total execution time: 33"

Тот что "снаружи" время 166.

При этом если запрос выбрать отдельно и выполнить его - будет как "внутри процедуры".

Как заставить выполнятся процедуру что бы duration у неё был такой же как у запроса. Профайлером отлавливается как "снаружи".

По факту там несколько шагов (выборка данных, подсчёт количества, пейджинг)
PRINT
Step 0: "Parsing XML "0
Step 0.5: "Compute variables "0
Step 1: "Preparing params and queries"20
Step 2: "Select data "3
Step 3: "compute count"10
Total execution time: 33


Такой глюк обнаружил только в одной процедуре (пока).
11 май 12, 19:07    [12538691]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт времени выполнения процедуры  [new]
recompile
Guest
NIIIK
Всем доброе время суток!

считаю время выполнения процедуры вот таким методом

declare @debugDate datetime;
set @debugDate = getdate();
  
exec dbo.spSomeStoredProcedure;

print cast(datediff(ms, @debugDate, getdate()) as nvarchar); 


причём внутри этой процедуры есть такой же "расчёт"

Тот что "внутри" выдаёт время "Total execution time: 33"

Тот что "снаружи" время 166.

При этом если запрос выбрать отдельно и выполнить его - будет как "внутри процедуры".

Как заставить выполнятся процедуру что бы duration у неё был такой же как у запроса. Профайлером отлавливается как "снаружи".

По факту там несколько шагов (выборка данных, подсчёт количества, пейджинг)
PRINT
Step 0: "Parsing XML "0
Step 0.5: "Compute variables "0
Step 1: "Preparing params and queries"20
Step 2: "Select data "3
Step 3: "compute count"10
Total execution time: 33


Такой глюк обнаружил только в одной процедуре (пока).

по всей видимости процедура компилируется при каждом запуске. в профайлере есть соответствующий эвент
11 май 12, 19:11    [12538708]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт времени выполнения процедуры  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
recompile,

и что/как правильно делать надо?! Никикаких особых хинтов процедуре не ставил. Процедура содержит динамический SQL.
11 май 12, 19:22    [12538764]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт времени выполнения процедуры  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
NIIIK
recompile,

и что/как правильно делать надо?! Никикаких особых хинтов процедуре не ставил. Процедура содержит динамический SQL.
Правильно посмотреть в профайлер и найти причину тормозов. Т.е. найти, на какие операции сколько ресурсов тратится.

Хинта "работать медленно" понятное дело нет :-)
11 май 12, 19:34    [12538805]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт времени выполнения процедуры  [new]
is_me
Member

Откуда: Нижний Новгород
Сообщений: 165
А что считать временем выполнения?
Время получения результата на сервере (возможно очень удаленном),
тогда скорее результат внутри(33),
или после "доставки надом", тогда скорее снаружи.
Если речь идет об оптимизации запроса, то неважно абсолютное значение
времени, а важно его изменение до и после проведения оптимизации,
средства измерения наверно не так важны, а скорее важны условия измерения
(одинаковая "закешируемость" и одинаковая загрузка компа другими процессами)

Еще можно как на рисунке:

К сообщению приложен файл. Размер - 43Kb
11 май 12, 21:21    [12539186]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт времени выполнения процедуры  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Вот пример, сначала запрос "внешене" выполняется долго. Т. е. внутри процедуры расчёт времени функцией Datediff показывает нормальное время выполнение. Если делать этоже "снаружи", то большое.

Потом запускаем эту процедуру с другими параметрами и она работает быстро.

Потом возвращаемся к старому тест кейсу - он тоже работает быстро и время внешнее совпадает с внутренним!

И так до момента перекомпиляции процедуры.

Что это может быть?!

К сообщению приложен файл. Размер - 25Kb
26 май 12, 02:30    [12618920]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт времени выполнения процедуры  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Потом быстрый вариат (дургой тест кейс с другими параметрами)

К сообщению приложен файл. Размер - 33Kb
26 май 12, 02:30    [12618921]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт времени выполнения процедуры  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Потом старый тест работает быстро.

К сообщению приложен файл. Размер - 27Kb
26 май 12, 02:31    [12618922]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт времени выполнения процедуры  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Есть у кого-нить идеи?!
28 май 12, 11:09    [12624504]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт времени выполнения процедуры  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
NIIIK
recompile,

и что/как правильно делать надо?! Никикаких особых хинтов процедуре не ставил. Процедура содержит динамический SQL.

по-моему динамический sql ведет к перекомпиляции процедуры.
28 май 12, 11:20    [12624590]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт времени выполнения процедуры  [new]
Дмитрий_sql
Member

Откуда:
Сообщений: 35
Попробуй захинтовать sql запрос. Чтоб рекомпиляция быстрее выполнялась.
select ... from tbl with(index ('tbl_Index')) .

Недостаток этого в том если статистика этого индекса устареет, то это будет уже не оптимально.
28 май 12, 11:23    [12624605]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт времени выполнения процедуры  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Мистер Хенки
NIIIK
recompile,

и что/как правильно делать надо?! Никикаких особых хинтов процедуре не ставил. Процедура содержит динамический SQL.

по-моему динамический sql ведет к перекомпиляции процедуры.


по факту конечно запрос формируется "динамически" но на выходе перед sp_executesql он уже "такой же как был предыдущие запуски". Можно считать что "такой же запрос в новом окне SSMS" да и сами запросы выполняются быстро "внутри процедуры работает как надо".
Таких процедур по сути не мало, проблема только конкретно с этой. И что странно это не зависит "первый или последующие запуски". Но если выполнить процедуру с определённым набором параметров - всё потом работает быстро.
Я просто не знаю "где шарится". Отдельно с запросом таких проблем нет.
28 май 12, 11:47    [12624771]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт времени выполнения процедуры  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
NIIIK
Мистер Хенки
пропущено...

по-моему динамический sql ведет к перекомпиляции процедуры.


по факту конечно запрос формируется "динамически" но на выходе перед sp_executesql он уже "такой же как был предыдущие запуски". Можно считать что "такой же запрос в новом окне SSMS" да и сами запросы выполняются быстро "внутри процедуры работает как надо".
Таких процедур по сути не мало, проблема только конкретно с этой. И что странно это не зависит "первый или последующие запуски". Но если выполнить процедуру с определённым набором параметров - всё потом работает быстро.
Я просто не знаю "где шарится". Отдельно с запросом таких проблем нет.

может быть процедура с правильными параметрами загружает в кеш данных нужные данные, которые потом используются при других запусках. Может попробовать чистить кеш данных перед запуском DBCC DROPCLEANBUFFERS, тогда если запуски со всеми вариантами параметров будут примерно одинаково долго, то обьяснение подобного поведения сервера в использовании кеша данных.
28 май 12, 12:52    [12625240]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт времени выполнения процедуры  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
NIIIK
Мистер Хенки
пропущено...

по-моему динамический sql ведет к перекомпиляции процедуры.


по факту конечно запрос формируется "динамически" но на выходе перед sp_executesql он уже "такой же как был предыдущие запуски". Можно считать что "такой же запрос в новом окне SSMS" да и сами запросы выполняются быстро "внутри процедуры работает как надо".
Таких процедур по сути не мало, проблема только конкретно с этой. И что странно это не зависит "первый или последующие запуски". Но если выполнить процедуру с определённым набором параметров - всё потом работает быстро.
Я просто не знаю "где шарится". Отдельно с запросом таких проблем нет.
временные таблицы есть в процедуре?
29 май 12, 02:34    [12629038]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт времени выполнения процедуры  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
NIIIK
Отдельно с запросом таких проблем нет.
Отдельно запрос не оптимизируется под значения параметров.
Можно попробовать "выключить" parameter sniffing путем добавления опции OPTION (OPTIMIZE FOR UNKNOWN) к каждому запросу внутри процедуры.
29 май 12, 02:39    [12629039]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт времени выполнения процедуры  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
По факту была перекомиляция процедуры.

Убрал "костылём". Входные параметры "переобъявил".

declare  
 @firstRecord_ int = @firstRecord,
 @lastRecord_ int = @lastRecord,
 @pageIndex_ int = @pageIndex,
 @pageSize_ int = @pageSize,
 @orderRule_ nvarchar(max) = @orderRule,
 @rowCount_ int = @rowCount,
 @xmlSearchParam_ xml = @xmlSearchParam;
29 май 12, 14:33    [12631986]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт времени выполнения процедуры  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
NIIIK
По факту была перекомиляция процедуры.

Убрал "костылём". Входные параметры "переобъявил".

declare  
 @firstRecord_ int = @firstRecord,
 @lastRecord_ int = @lastRecord,
 @pageIndex_ int = @pageIndex,
 @pageSize_ int = @pageSize,
 @orderRule_ nvarchar(max) = @orderRule,
 @rowCount_ int = @rowCount,
 @xmlSearchParam_ xml = @xmlSearchParam;
OPTION (OPTIMIZE FOR UNKNOWN) делает по сути тоже самое
29 май 12, 20:57    [12634169]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт времени выполнения процедуры  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
>OPTION (OPTIMIZE FOR UNKNOWN) делает по сути тоже самое
Сорри что не спросил сразу
Но это вроде "хинт запроса, а не процедуры" ?!
А отхавывание времени "вначале".
7 июн 12, 02:18    [12678984]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт времени выполнения процедуры  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
NIIIK
>OPTION (OPTIMIZE FOR UNKNOWN) делает по сути тоже самое
Сорри что не спросил сразу
Но это вроде "хинт запроса, а не процедуры" ?!
А отхавывание времени "вначале".
"Отхавывание" времени вызвано рекомпиляцией процедуры, что в свою очередь наверняка происходит из-за рекомпиляции запроса(ов) внутри процедуры.
7 июн 12, 02:22    [12678990]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт времени выполнения процедуры  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Mind,

Запросы внути динамические.
Не думаю что этот хинт в динамических запросах (возврат страницы и количества записей) как-то поможет.
7 июн 12, 12:03    [12680843]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт времени выполнения процедуры  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
NIIIK
Mind,

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

Если же у вас параметризованные запросы через sp_executesql, то по сути эти вызовы работают с параметрами так же как и обычные процедуры.
7 июн 12, 20:11    [12684286]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт времени выполнения процедуры  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Mind,

Mind
Если же у вас параметризованные запросы через sp_executesql, то по сути эти вызовы работают с параметрами так же как и обычные процедуры.

Естественно.
Я конечно сейчас перепроверю, но глубоко сомневаюсь.
Отпишусь если получится.
8 июн 12, 00:07    [12684791]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт времени выполнения процедуры  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
NIIIK
Mind,

Mind
Если же у вас параметризованные запросы через sp_executesql, то по сути эти вызовы работают с параметрами так же как и обычные процедуры.

Естественно.
Я конечно сейчас перепроверю, но глубоко сомневаюсь.
Отпишусь если получится.


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

with
cteP as
(
select t.c.value('./@column', 'nvarchar(255)') as colName,
       case t.c.value('./@desc', 'bit') when 1 then ' desc' else '' end as direction,
       t.c.value('./@order', 'int') ord
  from @xmlSearchParam.nodes('/root/ORDER/c') t(c)
),

cteE as
(
select t.c.value('./@column', 'nvarchar(255)') as colName,
       t.c.value('./@alias', 'nvarchar(255)') as alias
  from @colList.nodes('/root/ORDER/c') t(c)
)

select @orderRule
       =        
       case 
        when @orderRule is not null
        then @orderRule + ', '
        else ''
       end
       + 
       e.colName
       +
       p.direction
       
  from cteP p
 inner
  join cteE e
    on p.colName = e.colName
    or p.colName = e.alias
 order 
    by p.ord
    
OPTION (OPTIMIZE FOR UNKNOWN);  
8 июн 12, 00:26    [12684827]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить