SQL SERVER – Parameter Sniffing Simplest Example

Today we will see a very simple example of the Parameter Sniffing. I was asked by my client if I can explain to them what is the simplest example of the parameter sniffing during a recent consulting engagement of Comprehensive Database Performance Health Check. Let us see a very simple example of the same with the help of the latest sample database.

 

SQL SERVER - Parameter Sniffing Simplest Example parametersniffing0-800x363

Parameter Sniffing

SQL Server creates an optimal plan for a stored procedure by using the parameters that are passed the first time to the stored procedure is executed is called Parameter Sniffing.

SQL Server does this exercise to build the execution plan once and use it multiple times without recreating the execution plan again and again. The compilation process of the stored procedure can take some precious time for the execution of the procedure is very large. This entire exercise is done to save those repeated moments recompiling the stored procedures.

While this works out great in most of the cases, there is often a negative impact of the parameter sniffing issue for the stored procedure.

Let us see a very simple example. To begin with, let us create the following stored procedure.

CREATE OR ALTER PROC GetCustomerOrders (@CustomerID INT)
AS
SELECT *
FROM WideWorldImporters.Sales.Orders
WHERE CustomerID = @CustomerID
GO

Now let us enable the actual execution plan for your query so we can, later on, inspect the execution plans.

First, run the stored procedure with the first value.

EXEC GetCustomerOrders 1060

Let us observe the execution plan. You will notice in the execution plan we see operators reading 4 rows out of potential 4 rows.

SQL SERVER - Parameter Sniffing Simplest Example parametersniffing1

Next, run the stored procedure with a different value.

EXEC GetCustomerOrders 90

Let us observe the execution plan. You will notice in the execution plan that the operator is reading 150 rows out of potential 4 rows.

SQL SERVER - Parameter Sniffing Simplest Example parametersniffing2

Now let us look at the property of the execution plan by right-clicking on the left-most SELECT operator in the SSMS.

Pay attention to the parameter list. You will see here there is mention of the parameter @CustomerID. This will also demonstrate that the parameter is compiled with the value 1060 but currently it is running this with a value of 90.

SQL SERVER - Parameter Sniffing Simplest Example parametersniffing3

This process of building the execution plan for the stored procedure with the initial run parameter is called Parameter Sniffing.

Performance Issue

In the real world, there is no performance issue of the execution plan of the new parameter is the same as the original execution plan. However, if the newer parameter is better suited for the different execution plan there can be huge potential performance degradation.

Let us understand this further by clearing the execution plan for our stored procedure by recompiling it.

EXEC sp_recompile 'GetCustomerOrders'

 

Now let us run the stored procedure again with the newer value of the 90 and you will see that the execution plan changes from the original execution plan. Earlier the execution plan displayed the count of the rows as 150 out of 4 which is not corrected as 150 of 150.

EXEC GetCustomerOrders 90

SQL SERVER - Parameter Sniffing Simplest Example parametersniffing4

If you now run the stored procedure with the earlier parameter where we got 4 rows, you will notice that that query now uses this newer plan and display 4 out of 150. This may be a potential performance issue as the query may get more resources allocated.

EXEC GetCustomerOrders 1060

SQL SERVER - Parameter Sniffing Simplest Example parametersniffing5

The simplest solution to overcome the parameter sniffing is to recompilation, however, it has also many different issues. I often discusses this in detail in my Comprehensive Database Performance Health Check.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQL SERVER – Copy Database Without Statistics Query Store
Next Post
SQL SERVER – Parameter Sniffing and Local Variable in SP

Related Posts

7 Comments. Leave new

  • Carter Cordingley
    December 20, 2019 8:23 am

    Great information. I will asume that you can also use the Option(recompile) at the end of you query to perform this every time

    Reply
  • Hi Pinal, But in my case when ever i am running procedure from sql browser it is working fine but when i am calling it from c# it got stucked. and As i update statistics it start working fine. What would be reason behind this.

    Reply
  • I had once faced a similar issue in SQL Server 2008 R2. I just copied the stored proc parameters to the locally declared variables and used them. It worked. Something like this:

    CREATE OR ALTER PROC GetCustomerOrders (@CustomerID INT)
    AS

    DECLARE @l_CustomerID INT
    SET @l_CustomerID = @CustomerID

    SELECT *
    FROM WideWorldImporters.Sales.Orders
    WHERE CustomerID = @l_CustomerID
    GO

    Though I am not sure if there are any disadvantages of this approach.

    Reply
  • To get around this problem, we have created a local variable and reassigned the value. This has resolved the slowness issue without recompiling. However, after looking at your explanation, it shouldn’t fiz the issue… any thoughts on that?

    CREATE OR ALTER PROC GetCustomerOrders (@CustomerID INT)
    AS

    Declare @_customerID as int;
    Set @_customerID = @customerID;

    SELECT *
    FROM WideWorldImporters.Sales.Orders
    WHERE CustomerID = @_CustomerID
    GO

    Reply
  • @Alok

    > and As i update statistics it start working fine. What would be reason behind this.

    “Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile.”

    Source:
    https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql

    Reply
  • How to deal with parameter sniffing in “exec sp_executesql”, query coming from .NET Entity Framework?

    Thank you.

    Reply

Leave a Reply

Menu