The very fact that there were not many articles about this indicates that it is not a very common issue and you might spend your whole career without getting to know about it. However this article is to help those few who are affected by this issue and scratching your head like I did.
As explained by Microsoft site
"Parameter sniffing" refers to a process whereby SQL Server's execution environment "sniffs" the
current parameter values during compilation or recompilation, and passes it along to the query optimizer
so that they can be used to generate potentially faster query execution plans
Yes SQL server uses the actual parameter of a stored procedure to come up with an optimized execution plan. Now this was never a problem until that one time I was hit by a very strange performance issue. When I searched internet for the solution there weren’t many similar cases but however came across this term by chance and it did hit the spot of our issue. When the Stored procedure is executed the first time, it is compiled and the execution plan is cached to be reused by the subsequent request. It is this first time that the parameter used to create the execution plan is sniffed along with it. Consider the following procedure
Create Procedure [dbo].[GetTotalOrders]
@agentID int,
@location varchar(20)
AS
........
........
Select * From Orders O
Inner Join .....
Where O.Agent = @agentID
And L.Location = @location
GO
It is a simple procedure to get the list of all order for a given parameter. It was working well so far except for a new agent who got added very recently. The pages will timeout for that one agent all the time. To worsen the scenario, when I ran the select statement from the Management Studio it returned in seconds just as it did for other agents. It just got as strange as it could get.
The total number rows for this new agent was awfully lot more than any other agents in our system. The timeout was due to Parameter Sniffing. When the procedure ran first it used the agentID parameter from that query to create the execution plan, however the execution plan wasn’t holding good for this new agent. When we recompiled the procedure with the new agent it returned as expected.
Fortunately the solution for this was simple, to use a local variable instead of the parameter and magically the Parameter Sniffing issue goes away.
Create Procedure [dbo].[GetTotalOrders]
@agentID int,
@location varchar(20)
AS
Declare @tempagentID int
Declare @templocation varchar(20)
Set @tempagentID = @agentID
Set @templocation = @location
........
........
Select * From Orders O
Inner Join .....
Where O.Agent = @tempagentID
And L.Location = @templocation
GO
This article focuses on the stored procedure, but this issue can hit queries submitted using “sp_executesql” as well. Being a web developer this was enough for me to rectify the issue, however if you are an enthusiastic SQL Developer or a DBA, you can research to find more for yourself.
No comments:
Post a Comment