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 = Pps_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 (Pps_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), Pps_tkt_num) + '%') )
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