Parameter sniffing in SQL Server

Hi Friends,

SQL Server uses histograms from statistics objects to estimate cardinality of a query and this helps SQL Server to produce an optimal execution plan. Query optimizer has to inspect values supplied in query parameters and this behaviour is called Parameter sniffing in SQL Server.

Plan cache can store execution plans so that they can be reused next time we execute the same query and an execution plan matches the current parameters of a query improves the performance. This saves both optimization time and CPU as well.

Query optimizer may produce different execution plans for identical queries based on their parameters, caching and reusing any specific plans may cause performance issues of identical queries which would have been benefited from a better plan. This problem is known when we use stored procedures and I’m going to demonstrate this behaviour with an example.

Let’s write a stored procedure using PurchaseOrderDetail table on AdventureWorks2012 database.

--USE [AdventureWorks2012]

CREATE PROCEDURE sp_RetPurOrdDetail (@PID INT)
AS
SELECT * FROM [Purchasing].[PurchaseOrderDetail]
WHERE PurchaseOrderDetail.ProductID = @PID

Now let us execute this stored procedure and see the execution plan.

EXEC sp_RetPurOrdDetail @PID=876

Query optimizer used Index Seek and a Key Lookup (read here Part1, Part2, Part3, Part4) operator to search required records. This was a good choice though the plan is relatively expensive however query was highly selective. What if we specify a different parameter, may be less selective one? Let’s try this including SET STATISTICS IO ON.

SET STATISTICS IO ON

EXEC sp_RetPurOrdDetail @PID=319

(130 row(s) affected)
Table 'PurchaseOrderDetail'. Scan count 1, logical reads 262, physical reads 0, read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

As we can see, SQL Server has performed 262 logical reads which increases I/O. Let us now clear the plan cache and re-run the stored procedure with same parameter as above.

DBCC FREEPROCCACHE

EXEC sp_RetPurOrdDetail @PID=319

This time execution plan is totally different and now we will turn on I/O information and observe logical reads one more time.

SET STATISTICS IO ON

EXEC sp_RetPurOrdDetail @PID=319

(130 row(s) affected)
Table 'PurchaseOrderDetail'. Scan count 1, logical reads 66, physical reads 0, read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Logical reads came down to 66 from 262 and execution plan includes a Clustered index scan. As we cleared the procedure cache, SQL Server optimized the query from beginning using new parameter and plan shown in above figure chosen as optimized one.

This does not mean that we should not trust our stored procedures anymore. Only that we need to be aware of the fact that in case any stored procedure performance drops suddenly after introducing a new parameter, this could a potential issue.

Regards,

Kanchan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook