This is one of the blog post in a series of posts about optimizing SQL Server queries. A list of blog posts about query tuning can be found here – https://jbswiki.com/2021/04/15/sql-server-query-optimization-introduction/

SQL server creates an execution plan for a stored procedure whenever it gets compiled or recompiled. Repeated execution of the same stored procedure uses the execution plan from the plan cache instead of creating a new one every time. The execution plan that is created while compiling the stored procedure basically considers the parameter being passed. This parameter value is actually sniffed and used for cardinality estimation. Parameter sniffing is good most of the time. But there are situations where this creates huge performance issues when the estimates go bad while executing the stored procedure with a different parameter than the one used while the execution plan was created.

Let’s consider we got into a new job and started earning a salary of 1000 dollars. We estimate our monthly expense to be 1000 dollars and create a budget plan for this. Every time we change jobs or whenever there is a hike in the salary, we revise the budget plan. So, in this case we (SQL server) create a new budget plan (execution plan) using the salary(parameter) $1000. After a month when looking at the estimated and actual spending, we are happy that the actual spending matches the estimated spending and all looks good. But after some months we could see that the money in hand was spent within 10 days and land into a serious issue. We had to borrow money from others and had to cater to the expense for that month. Later when we looked at the estimated and actual spending, there are serious differences in the estimates where actual spending is way higher than the estimated spending. Analyzing further we understood that there were some additional spending’s that actually ballooned our actual spending.

Let’s look at an example using database JB_Parameter_Sniffing. You can download the database backup here,

• Execute queries,select COUNT(1) from tab1
  select city,COUNT(*) from tab1 group by city
  

  

• Store procedure proc1 definition,
  create proc [dbo].[proc1] (@city varchar(100))
  as
  begin
  select sno,sname,city
  from tab1
  where city=@city
  end
  GO
• exec proc1 ‘Bangalore’
  The execution completes in 201 ms.
  The statistics looks like this:

  

• The execution plan for the highlighted query looks like this:

  Let’s check how the estimates and actuals look for the Index Seek and the Key lookup:

  

  
• exec proc1 ‘Chennai’
  The query completes in 3232 ms.

  
  

  Let’s now check how the estimates and actuals look for the Index Seek and the Key lookup:

  

  
• The execution of the stored procedure with parameter ‘Chennai’ uses the cached plan of stored procedure with parameter ‘Bangalore’ and the plan proves to be bad for the second execution as the estimates seems bad. We are estimating 100 rows, but the rows returned seems to be close to 100000.
• In this case, we are under estimating the number of rows during second execution.
• Let’s clear the plan from plan cache and run the queries again in different order and check how things looks now,
• exec proc1 ‘Chennai’
  
• exec proc1 ‘Bangalore’
  
  
• In this case, the execution of the stored procedure with parameter ‘Bangalore’ uses the cached plan of stored procedure with parameter ‘Chennai’ and the plan proves to be bad for the second execution as the estimates seems bad. We are estimating 100000 rows, but the rows returned seems to be close to 100. The same query when run first executes in just 201 MS.
• In this case we are over estimating rows during second execution.
•  From the above example, a value is used while creating the execution plan and the same is used by all other executions no matter what parameter is passed. But, how can we identify this? How can we get to know what parameter was used while the plan is created? The answer lies in the XMP plan. Right click the graphical execution plan and click “Show Execution plan XML…” and search for “ParameterList”. In my case I see the below,
• In this case the execution plan was created using parameter “Chennai” as indicated under ParameterCompiledValue and the current execution using the parameter “Bangalore” is indicated by ParameterRuntimeValue.How do we solve this?

  1) RECOMPILE Hint

  -> The easiest way is to add a recompile hint to the stored procedure.

  alter proc [dbo].[proc1] (@city varchar(100))
  with recompile
  as
  begin
  select sno,sname,city
  from tab1
  where city=@city
  end
  GO

  -> Executing the stored procedure now will create different execution plan for different parameters. Make sure you clear the plan cache before attempting this.

  
  

  Are there any issues using the recompile hint?

  -> We will use stored procedure Proc2 and Proc2_Recompile to test this.

  -> Stored procedure Proc2 and Proc2_Recompile are same, the only difference is that procedure Proc2_Recompile contains a recompile hint.

  -> Stored procedure Proc1 and Proc2 are same, But Proc2 contains an IN clause just  to make sure that the execution plan creation takes little more time. The stored procedure definition can be seen from the database backup.

  -> Execute the below query and check the performance. The below query executes stored procedure Proc2 100 times. It will execute exec proc2 ‘Chennai’ 10 times  and exec proc2 ‘Bangalore’ 90 times.

  -> Procedure Proc2 takes close to 3 minute 54 seconds.

  

  -> Procedure Proc2_Recompile takes close to 2 minute 45 seconds.

  

  -> In this case we have an improvement while using the recompile hint.

  -> Let’s execute the procedure other way around. Procedure Proc2 takes close to 5 seconds.

  

  -> Procedure Proc2_Recompile takes close to 2 minute 44 seconds.

  

  -> In this case we don’t have an improvement while using the recompile hint. The performance has gone worse while using the recompile hint.

  -> The time difference comes from the fact that it took much time for creating an execution plan than executing it. With recompile hint it had to spend time in creating the plan every time it executed the procedure. In this case parameter sniffing is better than recompiling the procedure every time.

  2) Local variables

  -> Let us implement Proc3 with a local variable as below,

  Create proc [dbo].[proc3] (@city varchar(100))
  as
  begin
  declare @L_city varchar(100)
  set @L_city = @city
  declare @s_sno int, @s_sname varchar(255), @s_city varchar(255)
  select @s_sno=sno,@s_sname=sname,@s_city=city
  from tab1
  where city=@L_city
  end

  -> Executing procedure,

  

  

  -> Executing procedure,

  
  

  -> The estimates are not that great. But this Is how it works when using the local variables.

  3) OPTIMIZE FOR (@VARIABLE=VALUE) -> It would be good if one line of what this option does is explained

  This hint advises the query optimizer to use a particular value for a local variable when the query is compiled and optimized. The value is used only during query optimization, in this article’s context the value is used to generate the execution plan or we use the value to create an estimation as per our requirements in the estimated execution plan and not during query execution.

  -> Let us create procedure Proc4 as below,

  create proc [dbo].[proc4] (@city varchar(100))
  as
  begin
  declare @s_sno int, @s_sname varchar(255), @s_city varchar(255)
  select @s_sno=sno,@s_sname=sname,@s_city=city
  from tab1
  where city=@city
  option (optimize for (@city=’Chennai’))
  end

  -> Executing procedure,

  
  

  -> Executing procedure,

  
  

  -> The problem arises when any new data is inserted and a new city with more than 100000 rows for that city comes in and when data related to city “Chennai” is deleted.

  4) OPTIMIZE FOR (@VARIABLE=UNKNOWN)  -> It would be good if one line of what this option does is explained

  This hint advised the query optimizer to use statistical data instead of the initial value to determine the value for a local variable during query optimization. It basically matches the query optimizer to make decision the same way it will do if no parameters are defined.

  -> We can use this option to work around issues arising due to previous option.

  -> Let us create procedure Proc5 as below,

  create proc [dbo].[proc5] (@city varchar(100))
  as
  begin
  declare @s_sno int, @s_sname varchar(255), @s_city varchar(255)
  select @s_sno=sno,@s_sname=sname,@s_city=city
  from tab1
  where city=@city
  option (optimize for (@city UNKNOWN))
  end

  -> Executing procedure,

  
  

  -> Executing procedure,

  
  

  -> This is similar to using a local variable.

  5) Trace flag 4136

  -> Enabling trace flag 4136 in the instance level.

  

  -> Executing procedure,

  

  -> Executing procedure,

  

  -> Enabling trace flag 4136 in the instance level will disable parameter sniffing to all queries including queries that benefit from parameter sniffing.

  -> The trace flag can be used with in the query as below,

  create proc [dbo].[proc6] (@city varchar(100))
  as
  begin
  declare @s_sno int, @s_sname varchar(255), @s_city varchar(255)
  select @s_sno=sno,@s_sname=sname,@s_city=city
  from tab1
  where city=@city
  option (querytraceon 4136)
  end

  -> Executing procedure,

  
  -> Executing procedure,

  

  5) Creating different stored procedure for different city.

  -> We can create separate stored procedure for different cities. This requires us to check the table data periodically and create different procedures as appropriate. Not a great idea, but as a workaround.

  6) Disabling parameter sniffing at database level starting SQL server 2016

  -> Starting SQL server 2016, Parameter sniffing comes as a database scoped configuration.

  -> “Select * from sys.database_scoped_configurations” provide us the details.

  -> Database properties contains it as enabled by default.

  

  -> We can disable it in GUI or use “ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;”

  We have some of these options to resolve parameter sniffing. We should test these options and conclude depending on the results.

  Thank You,
  Vivek Janakiraman

  Disclaimer:
  The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.