Parameter sniffing

  • Mikael Eriksson SE

    SSCommitted

    Points: 1688

    Comments posted to this topic are about the item Parameter sniffing

  • nilay.chaudhary

    SSC Enthusiast

    Points: 152

    I think this is only related to older SQL versions

  • mahmad_khoja

    SSCommitted

    Points: 1511

    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.

  • Mikael Eriksson SE

    SSCommitted

    Points: 1688

    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;

  • palotaiarpad

    SSCarpal Tunnel

    Points: 4889

    Is it a useful knowledge?

  • Carlo Romagnano

    SSC-Insane

    Points: 21790

    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.

  • elgatitto

    Right there with Babe

    Points: 756

    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!

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    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

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Very nice QotD!

    Thanks

    IgorMi

    Igor Micev,
    My blog: www.igormicev.com

  • Arthur Olcot

    SSCertifiable

    Points: 6008

    Great question!

  • nilay.chaudhary

    SSC Enthusiast

    Points: 152

    Thanks for detail info

  • Richard Warr

    SSCertifiable

    Points: 6955

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

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

    _____________________________________________________________________
    MCSA SQL Server 2012

  • sqlnaive

    SSCoach

    Points: 17435

    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.

  • Mikael Eriksson SE

    SSCommitted

    Points: 1688

    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.

  • mahmad_khoja

    SSCommitted

    Points: 1511

    However will this affect anyway in output? Please note I am not asking about performance!!!

Viewing 15 posts - 1 through 15 (of 57 total)

You must be logged in to reply to this topic. Login to reply