Parameter sniffing

  • Comments posted to this topic are about the item Parameter sniffing

  • I think this is only related to older SQL versions

  • 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.

  • 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;

  • Is it a useful knowledge?

  • 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.

  • 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!

  • 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

  • Very nice QotD!

    Thanks

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • Great question!

  • Thanks for detail info

  • How nice not to see page on page of "Easy peasy" comments. 🙂

    Interesting question which opens up an area still uncharted for many developers.

  • 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.

  • 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.

  • 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