Post reply

Parameter sniffing

  • October 16, 2013 at 9:02 pm

    #395680

    Comments posted to this topic are about the item Parameter sniffing

  • October 16, 2013 at 11:51 pm

    #1659127

    I think this is only related to older SQL versions

  • October 17, 2013 at 12:05 am

    #1659129

    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

    #1659131

    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

    #1659138

    Is it a useful knowledge?

  • October 17, 2013 at 1:32 am

    #1659142

    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

    #1659153

    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

    #1659160

    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

    #1659161

    Very nice QotD!

    Thanks

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • October 17, 2013 at 2:18 am

    #1659162

    Great question!

  • October 17, 2013 at 2:27 am

    #1659165

    Thanks for detail info

  • October 17, 2013 at 2:43 am

    #1659168

    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

    #1659175

    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

    #1659183

    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

    #1659184

    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