Stored procedure slower than ad-hoc query

This morning I got a call from a developer saying that he is getting timeout on a call to a procedure. can you investigate please? We have orders that are piling up and not getting processed.! top priority

The developer sent me a windows form that i could use to test the issue. He was claiming that when calling the procedure, it takes 30 seconds then he gets a timeout but when using inline sql the query runs instantly.

Grabbed a coffee, fired the win form using the stored procedure and yes I got a timeout after around 30 seconds, then run the same thing using the inline query and it took a couple of milliseconds..himm

Executed the query that the procedure uses from management studio and it took a couple of seconds! I looked at the query plan, it was using an index seek and no issues there, no blocking too..

Sql server was using a different plan (in this case, a bad plan) for the procedure and a different plan when executing the ad-hoc query.

The issue happened to be that SQL Server creates an execution plan for a stored procedure when it is first called, and it tries to be cleaver by optimising it for the parameter that was used i the first call. However, this presents a problem if there is a large difference in the distribution of the number of records based on different parameters.


Let me explain:
If I execuete a procedure the first time and pass a parameter that returns 1 record from a table of 100 million record. Sql server creates a plan optimised for this parameter. If I then call the same procedure and pass a different parameter than returns 5000 records, assuming the first plan was a using an index seek and a key lookup, it will do the same for 5000 records which will be very inefficient.

There are a couple of ways round this;
1. to create local variables inside a stored procedure and assign the procedure variables to teh local variables. this way sql server will not be able to sniff the local variables.

2. use the OPTIMSE FOR () Hint


Create Procedure [dbo].[sp_GetObjectsByAttribute]
(
@AttrId int,
@Value varchar(128),
@Owner varchar(128)
)
As
SET nocount ON

SELECT TOP 500 o.ID, o.Handle, o.ClassID, v.AttributeID, o.Flags,o.Owner
FROM dbo.ObjectValues v
INNER JOIN dbo.[Objects] o on o.ID = v.ObjectID
WHERE
v.AttributeID = @AttrID
AND v.Value = @Value
AND o.Owner= @Owner
AND o.Flags & 1 = 0
OPTION (OPTIMIZE FOR UNKNOWN)
3. Force recompilation of the procedure evey time it is executed (not recommended)


This is a good post
http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/