SQL Server Parameter Sniffing...

SQL Server Parameter Sniffing happens when SQL Server tries to be clever and fast, but ends up accidentally hurting performance.

To put it simply: It is when SQL Server creates a shortcut based on the first value it sees, and then uses that same shortcut for everyone else—even if it is a terrible fit for them.

A Real-World Analogy: The Pizza Delivery Route

Imagine you work at a pizza shop and a delivery driver asks: "What is the fastest route to deliver an order?"

1. The First Order: The first customer lives right next door (2 minutes away). You tell the driver: "Just take your bicycle. It's the fastest way."
2. The Problem: The driver memorizes that strategy. But the next order is for a customer who lives 10 miles away on the other side of town.
3. The Sniffing Issue: Instead of looking at the new address and realizing they need a car, the driver blindly uses the old strategy—they try to ride the bicycle 10 miles. It takes forever!

In this analogy:
• The "Parameter" is the delivery address.
• "Sniffing" is looking at that address to decide the route.
• The Bug is locking in that route for all future deliveries, no matter how far away they are.

How it Happens in SQL Server

1. The First Run: When you run a stored procedure for the very first time, SQL Server looks at ("sniffs") the parameter value you passed in (e.g. CustomerID = 5). Based on that specific value, it builds a specific map (called an execution plan) for how to fetch the data.
2. Caching the Plan: SQL Server saves this map in its memory so it doesn't have to waste time building a new one next time.
3. The Subsequent Runs: Later, another user runs the exact same procedure, but passes a completely different parameter value (e.g. CustomerID = 999999).
4. The Performance Hit: SQL Server reuses the old map. If the first query needed to scan just a few rows, but the second query needs to scan millions, the old map will perform terribly, causing the query to freeze or run very slowly.

How to Fix It

If parameter sniffing is causing your queries to slow down randomly, database administrators usually fix it using a few standard approaches:
• WITH RECOMPILE: Tells SQL Server to ignore the old map and build a fresh one every single time the query runs.
• OPTIMIZE FOR: Forces SQL Server to build a map based on a typical, average parameter value rather than whatever happens to run first.
• Local Variables: Copying the parameter into a local variable inside the stored procedure, which tricks SQL Server into not sniffing the initial value at all.