Parameter Sniffing

My weekend was ruined with this one. Well, that is not fully true. We are still looking at other underlying performance reasons for a system slowdown. But this slowdown manifested itself in a single stored procedure called many hundreds of times by each user.

Consider a number of cases below. Case A is a simple select statement. Case B is the same code wrapped in a stored procedure

Case A
Declare @x int
Set @x = 1
Select * from something where id = @x

Case B
Create Procedure usp_Niall
@x int
As
Select * from something where id = @x
GO
Exec usp_Niall 1

So why does Case B perform 500 times slower than Case A - on 3 different servers (Production, Reporting & Backup servers)?

After some research, we discovered parameter sniffing does not always work. This is where SQL Server should use the parameter to deduct the optimal path.

When we then modified the stored procedure to be

Case C
Create Procedure usp_Niall2
@x int
As
Declare @y int
Set @y = @x
Select * from something where id = @y
GO
Exec usp_Niall2 1

This performed exactly the same as the Case A above - on all 3 servers.

You have been warned.

If you want to know more, then visit the following sites, where the issue is more fully discussed.
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/c992528dc5c6d8e4?hl=en&lr=&ie=UTF-8&oe=UTF-8
http://www.thescripts.com/forum/thread81556.html