What is Parameter Sniffing?

Parameter sniffing is generally a good thing. It's SQL Server's ability to "sniff" the value for a given parameter and cache an optimal execution plan based on the row estimates for that parameter value.

When a stored procedure is executed for the first time, the execution plan is cached and re-used each time that stored procedure is called; this is until the plan is removed from the plan cache. The plan that is generated when the stored procedure is first called is optimal for the parameter value that was provided, it might not be optimal for parameter values provided in subsequent calls to that stored procedure. This is called bad parameter sniffing.

This doesn't only apply to stored procedures, it also applies to parameterized queries whether simple or forced and prepared queries using sp_executesql.

How does this apply to memory?

Every query submitted to SQL Server will require some memory. Certain queries submitted to SQL Server require more memory to process certain operations. These queries require a Memory Grant. These are queries that contain Sort and/or Hash operators. We can see this by hovering over the select operator in an execution plan in SSMS and we'll see a memory grant in KB.

We can run the following select statement against the AdventureWorks database and observe the SELECT operator in the execution plan.

select sod.SalesOrderID
,sod.OrderQty
,sod.UnitPrice
,soh.OrderDate 
from sales.salesorderdetail sod
inner join sales.salesorderheader soh
on sod.SalesOrderID = soh.SalesOrderID
where soh.OrderDate < '20020730'
order by sod.UnitPrice

Where does the memory come from and how much memory does SQL Server grant

Memory required for Sort/Hash operations is allocated from the Buffer Pool.Memory grants can take up to 75% of the buffer pool. If the query is not part of a stored procedure and does not have a plan in cache that it can re-use, SQL Server will rely on statistics to estimate how much memory the operator requires to carry out the sort/hash operation. If the query is being submitted through a method that allows plan re-use and it has a plan in cache that it will re-use, the amount of memory SQL Server will grant will be the same as the amount of memory that was granted for the first call to the procedure, irrespective of whether this amount of memory is enough this is parameter sniffing. Once the query has received the memory grant, it carries out the sort/hash operation using the memory granted. If it turns out that the grant was not sufficient, the operation will spill to Tempdb, which is a much slower workspace than in-memory, this happens because once a query receives its memory grant, it cannot request more.

What are the symptoms of this behavior?

Here are a few common issues you might notice.

• Hash and Sort Warnings in SQL Server Profiler

A common tool for investigating performance on SQL Server is to use SQL Profiler, this is a tool that runs as a seperate application to SQL Server Management Studio and can report on what's happening in your instance. Sort or Hash Warning events in SQL Profiler would indicate that sort/hash operations are not being processed in memory but rather have spilled to TempDB. 

• Decrease in performance of stored procedures

Bad parameter sniffing is not the only cause for performance decrease of stored procedures, it could be one or more of many other things, however, bad parameter sniffing can severely affect the performance of stored procedures.

• IO waits in Tempdb

When investigating the performance and waits of your SQL Server, a common method is to use the sys.dm_os_waiting_tasks DMV. This DMV will report on tasks that are currently waiting for some resource is SQL Server. Included in this DMV are two useful columns called wait_type and resource_description. An IO wait_type combined with a resource description of 2: (2: is the database ID for TEMPDB) would indicate that a process is waiting for a resource in TempDB. While bad parameter sniffing is not the only cause for IO waits in TempDB, it certainly can cause it.

• High internal object allocations in Tempdb

When investigating TempDB, The sys.dm_db_session_space_usage DMV will help in understanding TempDB usage. A high amount of allocations for the internal_objects_alloc_page_count column can indicate that there is a high amount of allocations for sort and hash operations.

Now, we'll have a look at the Spill events caused by bad Parameter Sniffing. For this I'll be using the AdventureWorks database.

Create and run a SQL Profiler Trace specifying the events below

Run the query below, specifying "include actual execution plan".

use AdventureWorks
go
select sod.SalesOrderID,sod.OrderQty,sod.UnitPrice
,soh.OrderDate from sales.salesorderdetail sod
inner join sales.salesorderheader soh
on sod.SalesOrderID = soh.SalesOrderID
where soh.OrderDate < '20010730'
order by sod.UnitPrice

Review the SELECT operator in the execution plan.

Run the query below which specifies a different date in the where clause, and again, specify "include actual execution plan".

use AdventureWorks
GO
select sod.SalesOrderID,sod.OrderQty,sod.UnitPrice
,soh.OrderDate from sales.salesorderdetail sod
inner join sales.salesorderheader soh
on sod.SalesOrderID = soh.SalesOrderID
where soh.OrderDate < '20040730'
order by sod.UnitPrice

 Review the SELECT operator in the execution plan.

Reviewing the SQL Profiler trace will show that no Sort warnings have occured, indicating that the sort operations for both queries were done in memory.

Create a stored procedure from the Select statement, specifying soh.OrderDate as an input parameter.

use AdventureWorks
go
create procedure Sales 
@OrderDate datetime
as
select sod.SalesOrderID,sod.OrderQty,sod.UnitPrice,soh.OrderDate from sales.salesorderdetail sod
inner join sales.salesorderheader soh
on sod.SalesOrderID = soh.SalesOrderID
where soh.OrderDate < @OrderDate
order by sod.UnitPrice

Execute the Sales Stored Procedure , specifying '20010730' as the input parameter. Specify  "include actual execution plan".

use AdventureWorks
go
exec Sales '20010730'

Review the SELECT operator in the execution plan

Execute the Sales Stored Procedure , specifying '20040730' as the input parameter. Specify "include actual execution plan".

use AdventureWorks
go
exec Sales '20040730'

 Review the SELECT operator in the execution plan

The details shown in the SELECT operator are exactly the same as the previous execution of the stored procedure, even though we used a different parameter value that returns much more rows. SQL Server used the execution plan that was cached after the first execution of the stored procedure.

Reviewing the SQL Profiler trace will show that a Sort warnings has occured, indicating that the sort operations has spilled to TempDB. Whether or not the buffer pool has enough memory to process the sort operation is irrelevant. SQL Server has spilled to Sort operation to TempDB because once a query receives a memory grant, it cannot request additional memory to process the operation and the amount of memory granted to the first execution of the stored procedure is not enough to process the second execution of the stored procedure.

What can be done?

If the cause of sort/hash operations has been confirmed as bad parameter sniffing, there are a few options that can be explored:

• Alter the stored procedure to include a RECOMPILE hint - This tells SQL Server not to cache a plan for the stored procedure
• Alter the stored procedure to include an OPTIMIZE FOR hint - This tells SQL Server to cache a plan based on a parameter value
• Trace flag 4136 - This turns off Parameter sniffing altogether

These options need to be thoroughly researched and tested before employing any of them as a fix.

It is important to understand the implications of bad parameter sniffing in terms of SQL Server granting too little memory. It's also important to understand that SQL Server can grant too much memory for the processing of Sort and Hash operations. This happens when the first execution of the stored procedure produces a large result set and any subsequent execution of the stored procedure requests the same amount of memory, whether or not the query needs it.

I hope you guys have enjoyed this insight into Parameter Sniffing and Sniffing Memory.