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]

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.


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.


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.


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.



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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter


About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

One Comment on “Parameter sniffing in SQL Server”

  1. Simple and straight forward, the best way to explain any concept. Thank you for the detailed explanation Kanchan. Really helpful.

Leave a Reply to Manohar Punna Cancel reply

Your email address will not be published. Required fields are marked *