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
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
posted by Niall Flanagan at 21:36
1 Comments:
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?
Post a Comment
<< Home