What is the Parameter Sniffing
• Parametersniffing is the process whereby SQL Server creates an optimal planfor a stored procedure by using the calling parameters that are passed thefirst time a stored procedure is executed.
• Everysubsequent call to the same store procedure with the same parameters will alsoget an optimal plan, whereas calls with different parameter values maynot always get an optimal plan.
Solve the problem of one case
A stored procedure usp_LMS_Reports_ListExceptionsByUsertakes long time to complete. Can you check why?
1. Search the procedure name in the trace file.
· First
· Second
· Third
PS: There is a misleading pointthat what we should check is the RPC:Completed line, not the blue linein the pictures above, although their data are similar.
So, we can find that the durationof the third one is quite longer than the others. This may be a typical parametersniffing problem.
Further study.
Then, we can discover which statements cost so much time.
2. Check the statistics profile of the statement
Insert into #tmp_exceptiondata
Select EmpFK ,URAER.FLPercentage
FROM @tmpviewableusers t
Inner join TBL_TMX_USERREQUIREDACTER URAER on URAER.EmpFK = t.ViewEmpId
Inner join TBL_TMX_Activity Act on Act.Activity_PK = URAER.RootActivityFK
where Act.Private=0
AND URAER.LevelVal = 1
UNION ALL
Select EmpFK ,URAER.FLPercentage
FROM @tmpviewableusers t
Inner join TBL_TMX_USERREQUIREDACTER URAER on URAER.EmpFK = t.ViewEmpId
Inner join TBL_TMX_Activity Act on Act.Activity_PK = URAER.RootActivityFK
where Act.Private=0
AND (URAER.LevelVal = 0
AND URAER.ActivityFK NOT IN (SELECT PrntActFK FROM TBL_TMX_UserRequiredActER WHERE PRNTACTFK IS NOT NULL))
Comparing the actual rows and the estimate rows, we can find that the statistics is not so accurate. So the third one has used an unsuitable plan inherited from the first one, although the first one is really the optimal for itself.
Furthermore, a table variable is created inmemory, and so performs slightly better than #temp tables.
However, the system will not generate automatic statistics on table variables. Likewise, you cannot manually createstatistics (statistics are used to help the optimizer pick the best possible query plan).
3. Solution
· Recompile
· Optimizefor
· Planguide
2. Construct a non-clustered index
3. Temp table instead of table variable