October 16, 2013 at 9:02 pm
Comments posted to this topic are about the item Parameter sniffing
October 16, 2013 at 11:51 pm
I think this is only related to older SQL versions
October 17, 2013 at 12:05 am
Here every time in SP GetIt this statement "set @Name = 'Pelle'" is executed.
So every time record of Pelle will be displayed.
Please comment on this.
I have tested this on SQL 2008 R2.
October 17, 2013 at 12:38 am
It is the same in SQL Server 2005, 2008 and 2012.
If you want to check what the compiled value is you can use this.
create table T(Name varchar(25));
go
create procedure GetIt
@Name varchar(25)
as
set @Name = 'Pelle';
select *
from T
where Name = @Name;
go
exec GetIt 'Kalle';
with xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select T.qp.value('(//ColumnReference/@ParameterCompiledValue)[1]', 'varchar(25)') as ParameterCompiledValue
from
(
select cast(qp.query_plan as xml) as qp
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) as qp
where object_name(st.objectid) = 'GetIt'
) as T;
exec sp_recompile 'T';
exec GetIt 'Urban';
with xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select T.qp.value('(//ColumnReference/@ParameterCompiledValue)[1]', 'varchar(25)') as ParameterCompiledValue
from
(
select cast(qp.query_plan as xml) as qp
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) as qp
where object_name(st.objectid) = 'GetIt'
) as T;
October 17, 2013 at 1:18 am
Is it a useful knowledge?
October 17, 2013 at 1:32 am
palotaiarpad (10/17/2013)
Is it a useful knowledge?
Yes, it is.
Sometimes, store procs run slow because of parameters sniffing and obsolete query plan.
If you recompile, store procs may run better.
October 17, 2013 at 2:00 am
Mikael Eriksson SE (10/17/2013)
It is the same in SQL Server 2005, 2008 and 2012.If you want to check what the compiled value is you can use this.
...
Thanks. Very interesting!
October 17, 2013 at 2:14 am
nilay.chaudhary (10/16/2013)
I think this is only related to older SQL versions
No, it's true for all versions of SQL from 2005+
You can do a check for the SP calls with this code for example:
SELECT cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,
cp.usecounts AS UseCounts,
st.TEXT AS SQLBatch,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
where OBJECT_NAME(st.objectid,st.dbid) = 'GetIt'
Actually this is one of the main points of QofD, to try find something that is true/false for another version or a specific case and etc,... 🙂
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
October 17, 2013 at 2:14 am
Very nice QotD!
Thanks
IgorMi
Igor Micev,My blog: www.igormicev.com
October 17, 2013 at 2:18 am
Great question!
October 17, 2013 at 2:27 am
Thanks for detail info
October 17, 2013 at 2:43 am
How nice not to see page on page of "Easy peasy" comments. 🙂
Interesting question which opens up an area still uncharted for many developers.
October 17, 2013 at 3:07 am
I still didn't get it. I created one table and populated it as follows"
create table dbo.T(Name varchar(20))
insert into dbo.T values('Kalle'), ('Urban'), ('Steve'), ('Sqlnaive')
Then after creating the procedure as provided, I ran the command as follows:
exec dbo.GetIt 'Kalle'
exec sp_recompile 'T'
exec dbo.GetIt 'Urban'
It returned no rows as there was no "Pelle" in dbo.T table. Then I inserted "Pelle" in table dbo.T and ran the above proc execution commands again. It gave me "Pelle" two times for both executions.
October 17, 2013 at 3:23 am
sqlnaive (10/17/2013)
It gave me "Pelle" two times for both executions.
That is as it should be. The question was about what value was used as input to the query optimizer when it builds the execution plan.
The value used as the "compiled value" is not the same as the value actually used in execution.
October 17, 2013 at 3:39 am
However will this affect anyway in output? Please note I am not asking about performance!!!
Viewing 15 posts - 1 through 15 (of 56 total)
You must be logged in to reply to this topic. Login to reply