sql using different execution plans when running a query and when running that query inside a stored procedure?

Question

I have this query,

Declare @Prcocessrate float
declare @item varchar(20)
declare @process varchar(20)
declare @branch varchar(20)
set @item = 'shirt'
set @process = 'kt'
set @branch = '1'
select @Prcocessrate =  ProcessPrice from itemwiseprocessrate where itemname=@Item and Process=@process and branchid=@branch

when I run it single handed, the execution plan only shows 3 steps, see for youself..

but I have this procedure sp_newBooking as

ALTER PROC sp_newbooking
-- other arguements--
AS
BEGIN

--OTHER FLAGS--

ELSE IF (@Flag = 32)
        BEGIN

            declare @ItemId varchar(max),@ProcessRate float
            --set @BranchId='1'
            select @ProcessCode = DefaultProcessCode from mstconfigsettings where branchid=@BranchId
            select @ItemId= DefaultItemId from mstconfigsettings where branchid=@BranchId
            select @ItemName=  ItemName from itemmaster where itemid=@ItemId and branchid=@BranchId
            select @ProcessRate =  ProcessPrice from itemwiseprocessrate where itemname=@ItemName and ProcessCode=@ProcessCode and branchid=@BranchId
            if(@ProcessRate is not null)
            select @ItemName as ItemName,@ProcessCode as ProcessCode,@ProcessRate as ProcessRate
            else
            select @ItemName as ItemName,@ProcessCode as ProcessCode,'0' as ProcessRate
        END

-- OTHER FLAGS --


END

Now!, when I run this

exec sp_newbooking
@flag = 32,
@Branchid = 1

The execution plan is showing 6 steps! Here's the picture..!
See the query 4

Why is it taking 6 steps to perform the same query when executing from the procedure, while its taking 3 steps when executing alone? Wtf is this?

Answer 1

There are lot of reasons SQL can use different execution plans. It could be :

• Different parameters for the same queries (you use constants in your first example, the queries in the 2nd might have different values)
• Different data (meaning you're running queries on dev & production)
• Parameter sniffing -- more below, but first pass the procedure might have the 'desired' parameters
• Different data types - as @MartinSmith points out, we don't see the parameter declarations. You could have a variable that doesn't match the type of the field it's matched up against.

Parameter Sniffing
Stored procedures do 'parameter sniffing' which is a blessing (if it works for you) and a curse (if it works against you). First pass someone searches on Zebr% for zerbrowski. The last name index realizes this is very specific and will return, lets say, 3 rows from a million -- so one execution plan is built. With the proc compiled for a low row result, the next search is for S%. Well, S is your most common name and matches 93,543 rows out of 1 million.

So what can you do?
There are lots of steps you can take to inspect this....

• Look carefully at the variable data types, comparing your ad hock query, the proc, and the underlying table (sp_columns mytable).
• Isolate the moving parts
  • Make sure you're running in both queries in the same system with the same data
  • Have a trace running to make sure first run of the proc uses the expected parameters
  • Try running your ad hock query with a few different parameters and see how the execution plan changes.
  • If you can't isolate activity, temporarily add WITH RECOMPILE to the proc to compare execution plans. Alternately, do a DBCC FREEPROCCACHE just before running. (DISCLAIMER - if this is a live system make sure you understand what these will do).