Parameter Sniffing

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

1.      Query Hint

·   Recompile

·   Optimizefor

·   Planguide

2.      Construct a non-clustered index

3.      Temp table instead of table variable