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

Откуда: Москва
Сообщений: 1162
Вот тут один не очень вежливый человек задавал вопрос про то, как получить параметры процедуры во время выполнения.

Если не учитывать нарушение правил поведения, в целом - вопрос весьма интересный.

Я попытался на него ответить, нашел вот такой способ:

alter proc test_ATATA
@par1 int,
@par2 varchar(2),
@par3 bit
as
begin

select 1 as val
into #testtable
where @par1 = @par1;

insert into #testtable
select 1 
where @par2 = @par2;

insert into #testtable
select 1
where @par3 = @par3;

with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT t2.loc.value('@Column', 'nvarchar(max)'), t2.loc.value('@ParameterCompiledValue', 'nvarchar(max)')
FROM sys.dm_exec_requests req
	FULL OUTER JOIN sys.dm_exec_cached_plans CP
		ON cp.plan_handle = req.plan_handle
OUTER APPLY sys.dm_exec_query_plan(ISNULL(req.plan_handle,CP.plan_handle)) AS QPX
outer apply QPX.query_plan.nodes(N'//ParameterList/ColumnReference') as t2(loc)
where req.session_id = @@spid;

end


Ограничение - параметры попадают в план и могут быть возвращены только в том случае, если они в процедуре используются. Недостатки - в целом неверность подхода, когда мы за параметрами процедуры лезем в план, т.е. генерим такое количество параметров, что приходится лезть в план искать значения.

Есть ли еще какие подводные камни такого способа, которых я не замечаю?

Покритикуйте, плиз.
23 окт 15, 16:10    [18317876]     Ответить | Цитировать Сообщить модератору
 Re: Как получить параметры процедуры во время ее выполнения  [new]
Glory
Member

Откуда:
Сообщений: 104760
Minamoto
Есть ли еще какие подводные камни такого способа, которых я не замечаю?

Что будет, если один план используется из разных соединений ? Разумеется с разными значениями для параметров.
23 окт 15, 16:18    [18317924]     Ответить | Цитировать Сообщить модератору
 Re: Как получить параметры процедуры во время ее выполнения  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Glory, ага, проверил, осознал свою ошибку. Спасибо.
23 окт 15, 16:19    [18317930]     Ответить | Цитировать Сообщить модератору
 Re: Как получить параметры процедуры во время ее выполнения  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Так тоже мимо:

--create proc test_ATATA as select 1 
alter proc test_ATATA
@par1 int,
@par2 varchar(2),
@par3 bit
as
begin
    declare
        @p1 varchar = @par1
        
    set @p1 = @par2        
    

;with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT t2.loc.value('@Column', 'nvarchar(max)'), t2.loc.value('@ParameterCompiledValue', 'nvarchar(max)')
FROM sys.dm_exec_requests req
	FULL OUTER JOIN sys.dm_exec_cached_plans CP
		ON cp.plan_handle = req.plan_handle
OUTER APPLY sys.dm_exec_query_plan(ISNULL(req.plan_handle,CP.plan_handle)) AS QPX
outer apply QPX.query_plan.nodes(N'//ParameterList/ColumnReference') as t2(loc)
where req.session_id = @@spid
end
go
exec test_ATATA
    @par1       = 10
    , @par2     = 10
    , @par3     = 1
 
23 окт 15, 16:19    [18317931]     Ответить | Цитировать Сообщить модератору
 Re: Как получить параметры процедуры во время ее выполнения  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Гавриленко Сергей Алексеевич
Так тоже мимо:

Ну да, это тоже к ограничениям - что параметры должны напрямую участвовать в стейтментах, для которых строится план.
23 окт 15, 16:22    [18317962]     Ответить | Цитировать Сообщить модератору
 Re: Как получить параметры процедуры во время ее выполнения  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Glory
Minamoto
Есть ли еще какие подводные камни такого способа, которых я не замечаю?

Что будет, если один план используется из разных соединений ? Разумеется с разными значениями для параметров.

Получается еще одно ограничение - процедура должна создаваться с опцией RECOMPILE.
23 окт 15, 16:25    [18317998]     Ответить | Цитировать Сообщить модератору
 Re: Как получить параметры процедуры во время ее выполнения  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Minamoto
Glory
пропущено...

Что будет, если один план используется из разных соединений ? Разумеется с разными значениями для параметров.

Получается еще одно ограничение - процедура должна создаваться с опцией RECOMPILE.
Слишком много ограничений для того, чтобы воспользоваться граблями вместо вменяемой передачи списка значений любым доступным способом.
23 окт 15, 16:27    [18318019]     Ответить | Цитировать Сообщить модератору
 Re: Как получить параметры процедуры во время ее выполнения  [new]
Glory
Member

Откуда:
Сообщений: 104760
Minamoto
Glory
пропущено...

Что будет, если один план используется из разных соединений ? Разумеется с разными значениями для параметров.

Получается еще одно ограничение - процедура должна создаваться с опцией RECOMPILE.

Тогда все вызовы процедуры выстроятся в очередь. Ибо пока план занят, его нельзя перекомпилировать.
23 окт 15, 16:29    [18318038]     Ответить | Цитировать Сообщить модератору
 Re: Как получить параметры процедуры во время ее выполнения  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Гавриленко Сергей Алексеевич
Слишком много ограничений для того, чтобы воспользоваться граблями вместо вменяемой передачи списка значений любым доступным способом.

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

Кстати, в некоторых случаях значение параметра вообще не возвращается.

Выполняю:
exec test_ATATA
    @par1       = 22
    , @par2     = 12223311
    , @par3     = 1


Получаю:

@par1 (22)
@par2 '*'
@par3 (1)
23 окт 15, 16:32    [18318056]     Ответить | Цитировать Сообщить модератору
 Re: Как получить параметры процедуры во время ее выполнения  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Если при конверте int , smallint, or tinyint в varchar произойдет переполнение, то результат как раз и будет "*".
23 окт 15, 17:06    [18318273]     Ответить | Цитировать Сообщить модератору
 Re: Как получить параметры процедуры во время ее выполнения  [new]
o-o
Guest
Glory
Minamoto
Получается еще одно ограничение - процедура должна создаваться с опцией RECOMPILE.

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

почему в очередь?
вот процедура с RECOMPILE.
она выполняется минимум 5 минут.
я запускаю ее в трех сессиях,
разница во времени запуска 3 секунды,
все 3 выполняются, никого не ждут

RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure and the procedure is compiled at run time.

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

К сообщению приложен файл. Размер - 75Kb
23 окт 15, 17:14    [18318315]     Ответить | Цитировать Сообщить модератору
 Re: Как получить параметры процедуры во время ее выполнения  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Параметры не всегда видны в трассировщике. Зависит от способа вызова, если правильно помню.
23 окт 15, 17:17    [18318325]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить