SQL Server Performance

Could I have been getting hit by the 'Parameter Sniffing Problem'?

Discussion in 'Getting Started' started by lcerni, Nov 16, 2009.

  1. lcerni New Member

    We have a procedure that has 5 select queries in it. What was interesting was that it appeared to hang on the fourth query after running fine after an hour. When I would drop the procedure and recreate it then we would have no issues until about another hour later and then again on the fourth query it would hang. I put the WITH RECOMPILE statement in the procedure and it is now working fine.
    The procedure is submitted as usp_ProcedureName '1629975,1629981,1629985,1102195'. The parameter can have one number listed or there could be a list of 500.
    At first I thought that we were getting hit by a bad index or statistic and I would rebuild the indexes and update the statistics. However, this didn't seem to resolve the issue.
    Could I have been getting hit by the 'Parameter Sniffing Problem' or am I assuming too much here?

    This is how the fourth query looks like:
    ---
    Declare @tktList TABLE (tkt_num int)
    insert into @tktList (tkt_num)
    (SELECT value FROM fn_split(@al_tkt_num, ','))
    ---
    SELECT
    ops.ops_tkt_num,
    poss.po_num,
    poss.required_dt,
    poss.qty_ord,
    poss.qty_recvd,
    drop_ship = prev_op.order_num,
    poss.product_id,
    ops.manuf_id,
    ops.client_owned_ind,
    ops.prod_type_cd
    FROM @tktList AS tktList
    INNER JOIN order_product op
    ON op.ops_tkt_num = tktList.tkt_num -- only tkts from the given list
    INNER JOIN order_prod_seg ops
    ON ops.ops_tkt_num = op.ops_tkt_num -- all ops for this tkt
    INNER JOIN po_seg_ship poss
    ON poss.product_id = ops.product_id
    AND poss.manuf_id = ops.manuf_id -- all shippings of prods from current ops ...
    INNER JOIN purchase_order PO
    ON PO.po_num = poss.po_num
    AND PO.whse_id = op.whse_id -- ... in the same warehouse
    LEFT OUTER JOIN order_product AS prev_op
    ON prev_op.ops_tkt_num = P:confused:ps_tkt_num -- one row - previous (first?) tkt from PO if any (for drop_ship)
    WHERE poss.reconciled_dt IS NULL
    AND poss.status_cd = 'O'
    AND PO.po_type = (case when ops.client_owned_ind='Y' then 'C' else 'V' End )
    AND ( ops.client_owned_ind = 'N' OR
    PO.client_id = ops.client_id AND PO.contract_cd = ops.contract_cd
    )
    AND (P:confused:ps_tkt_num IS NULL OR -- only PO w/o ops link or linked to to parent ops tkt (???)
    dbo.f_get_all_prev_ops_tkts(op.ops_tkt_num) like (convert(varchar(10), P:confused:ps_tkt_num) + '%') )
  2. satya Moderator

    In a way RECOMPILE would reduce the parameter sniffing problem, going back to basics: Parameter sniffing” refers to a process whereby SQL Server’s execution environment “sniffs” the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word “current” refers to the parameter values present in the statement call that caused a compilation or a recompilation.
    In your case it should not affect as the variable declaration goes after actual code, as you pass the parameter to a local variable SQL will not use value of parameter to influence the query plan and refer http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html & http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!541.entry?wa=wsignin1.0 useful resources.
    HTH

Share This Page