Background info………
Recently one of our stored procedures had performance problems. The issue was complicated because performance was inconsistent on the same dataset using the same procedure parameters. After some digging it came apparent that this was due to how the SQL server prepares the execution plan for each query inside the stored proc. I could run the same procedure on the same dataset and get a difference of 40 seconds on execution.
Investigation……
When I hooked up the SQL server profiler I was able to extract the execution plan for the query in the store procedure that was causing the problem. I was able to find that depending of the execution sequence this query would have 2 different exestuation plans.
Sequence 1: I recompile the stored proc and run it on small dataset first then on a bigger dataset, the proc would be really slow on the bigger dataset.
Sequence 2: I recompile the stored proc and run it on the bigger dataset first (same data, same parameters) the sorted proc runs fast (40 seconds difference)
Something to keep in mind that the stored proc only ran slow in a jasper report, if I re run the proc in the SQL server it would be fast. So when testing, be sure to test it inside a report.
Execution plan……
As you know the DB will try to create an execution plan for query(non trivial) that is executed, unless there is one that already exists in the cache, in that case it will re use an execution plan. SQL server uses cost-based technique to determine the processing strategy. The query will use metadata of the database objects and current statistics to decide with indexes and what join strategies to use.
The overall time required to execute a query is the sum of the time required to generate the execution plan and the execution of the query.
The cost incurred in the generation of the execution plan depends on the process of generating the execution plan, the process of caching the plan and the reusability of the plan.
When it comes to execution plans for procedures they are no generated when you create the procedure, it gets created and cached first time you run it.
…..there is much much more, but this should be enough to see the problem in our case
Parameter sniffing…..
"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. The word "current" refers to the parameter values present in the statement call that caused a compilation or a recompilation.
The problem with parameter sniffing is that if you have procedures that return wide range of data the execution of the procedure will change………dramatically in our case.
If you run a procedure for the first time with small dataset, there will be execution plans created for queries in the procedure…. not necessarily efficient ones. So that if you re-run the same proc with bigger dataset the performance will suffer do to re-use of inefficient execution plan.
In some cases this can be fixed by creating local variable in the procedure and assigning the parameter variables to the local ones. This will confuse the query analysis and it will create an execution plan based on statistics. This might or might not be good. Depending on your situation, but it’s worth trying.
a) Sniffing enabled--- execution plan based on the parameter passed
CREATE PROCEDURE GetUser
@UserName nvarchar(20)
AS
BEGIN
select * from user where username = @UserName
END
b) Sniffing disabled --- execution plan based on statistic due to local variable
CREATE PROCEDURE GetUser
@UserName nvarchar(20)
AS
BEGIN
DECLARE @myUserName nvarchar(20)
SET @myUserName = @UserName
select * from user where username = @myUserName
END
Query hints………..option (recompile)
If the disabling of the parameter sniffing didn’t fix the problem, and the execution plan created is still slow, another option is to play around with the query hints. What I end up using is a hint that recompiles the specified query when it’s executed every time: option (recompile). This option is not placed on the whole procedure but only on specific query inside the procedure.
CREATE PROCEDURE GetUser
@UserName nvarchar(20)
AS
BEGIN
select * from user where username = @UserName option (recompile)
END
You have to be careful when using this option as in some case the creation of the execution plan could be costly and provide bigger over head.
Conclusion……….
In the end I was able to use query hint recompile to fix my problem and get a good constant execution of the procedure. I don’t have to care about different data ranges as every time the procedure executes it creates a low cost execution plan for the troubled query.
