When I started working with SQL Server® almost ten years ago, various aspects of performance tuning had to be taken into account. Since that time, there has been a lot of talk about parameter sniffing, but not many people really know what that means. I intend to change that with this blog.

What is the issue?

Here’s a typical scenario: an end-user complains that the page they sometimes use to search customers runs slow on certain days. Assuming there is no issue related to local variables, something is causing end-users’ data to be inconsistent and slow. They want you to help improve performance.

Troubleshooting

There are a number of ways to troubleshoot this problem.

• Using Activity Monitor, we can see the current activities and queries that are running. This helps us identify the most expensive queries.
• Using the Performance Dashboard in Management Studio, we receive similar information.
• Querying the DMV sys.dm_exec_requests, and cross-applying – sys.dm_exec_sql_text and sys.dm_exec_query_plan, we are able to see instances of current queries being executed. If you see the waiting is on pageiolatch_sh, it is indicating that it is waiting to transfer data from disk to buffer.

So how is this linked to parameter sniffing? The idea here is to find a pattern. If there are different parameters, does the same execution plan become inefficient? At the slower execution, we will see the same plan is used, but this plan is not good for all parameter values. This is what we call a parameter sniffing issue. Another pattern we can see, at the slower execution, is the actual number of rows that are far from the estimate.

Option 1:

Rewrite the stored procedure adding the RECOMPILE hint. It will be something like:

ALTER PROCEDURE Sales.SalesFromDate (@StartOrderdate datetime) AS 
SELECT *
FROM Sales.SalesOrderHeader AS h 
INNER JOIN Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID
WHERE (h.OrderDate >= @StartOrderdate)
OPTION (RECOMPILE)

Please note the Option keyword added to the SP. With this a plan is calculated every time, and adapted to each parameter value sent to the SP.

Option 2:

Rewrite the stored procedure to use the option (OPTIMIZE FOR(@Parameter1 = ‘xxxx’)), or use a plan guide. But we still need to decide which value we want to use that suits most of the relevant workload, creating and maintaining one plan that becomes efficient for the parameterized value.

Option 3:

This is a time-tested method for countering this pattern. You can rewrite the SP with local variables, which would typically look like this:

ALTER PROCEDURE Sales.SalesFromDate (@StartOrderdate datetime) AS 
DECLARE @date datetime
Select @date=@StartOrderDate
SELECT * 
FROM Sales.SalesOrderHeader AS h 
INNER JOIN Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID
WHERE (h.OrderDate >= @date)

As you can see, we reassign the parameter sent to the stored procedure to a local variable and we start using the same to internally post that.

Hopefully, you are aware of these methods, and that this is just a friendly reminder. Keep them in mind and they will serve you well.