Understand Parameter Sniffing

Introduction

There is a common problem that the developer faced when dealing with Stored Procedure (SP). They always told that the same stored procedure is sometimes taken a long time and sometimes takes few second to execute. Don’t be suppressed, to know that it is the same procedure. It happens and it is called parameter snipping.

 

Here we are just going to discuss the problem stamen of parameter snipping.

For solving the parameter snipping please search google and you find thousands of blogs related to it.

Before understanding the process of parameter snipping, let’s examine the problem statement.

Problem Statement

The problem statements are the developer has a Stored Procedure named storedpoc_ShowOutstanding. It takes three input parameters. One is Customer ID, second is from the date range and last is To Date rang.

When the developer tries to run the stored procedure with bigger, bigger range of it takes a little bit of time as the data volume is huge. Quite fair as per data volume is huge.

Now the developer is trying again with small range, but the problem is the performance of the stored procedure is not increased. It takes longer time to execute.

Question is why it is taking longer time to execute.

 

How the Execution Plan made by Optimizer

To understand this question, we have to understand how the query optimizer works.
When a SQL statement executes, after syntax checking or pursing it moves to algebrizer where the actual existence of objects verified and then move to the optimizer to create an execution plan.

To create execution plan, the optimizer need to be understood, how many records is retrieved by the query. For this he needs to run it. But for optimizer it is not possible to run the query at this point of time. So, to understand the number of records retrieved by the query, the optimizer used a complex algorithm called the cardinality.

So after this long and costly process, the optimizer makes the cost effective execution plan and cased the execution plan for further use.

 

How the Parameter snipping works

To make a correct execution plan by optimizer is a costly process. So, optimizer don’t want to lose it. For this they cased the execution plan for further use.

Optimizer creates the execution plan when we are going to run the stored procedure first time with some set of values.

 

In our case the optimizer takes a bigger range of date value and create the execution plan with higher cardinality. As the data volume retrieved by the query is huge.

 

For second execution with small range of data. The Optimizer doesn't create any new execution plan for it. It just used the cased execution plan. Although the date range is smaller and with low cardinality. So our second execution with limited date ranges takes a long time to execute.

 

 

Hope you like it.

 

Posted by: MR. JOYDEEP DAS