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

Advertisements
Leave a comment

1 Comment

  1. >Can you clarify the issue that leads to the parameter sniffing issue. It seems to me that recompiling the stored procedure (which would occure when the suggestion is implemented). I’ve not seen an issue like this that I wasn’t able to resolve by adding or rebuilding indexes and then recompiling the stored procedures. Rebuilding indexes on a production environment is not always an immediately available option, is this resolution a means to resolve the issue without having to rebuild indexes?

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: