It's one of the most-asked SQL Server questions: a stored procedure runs in 200 ms for weeks, then one morning the same call takes 90 seconds. Nothing was deployed. Running the query by hand in SSMS is fast. Restarting the server "fixes" it.
The usual culprit is parameter sniffing, which is really plan caching meeting skewed data.
What's actually happening
The first time a procedure runs, SQL Server compiles a plan using the parameter values of that call. It estimates row counts from the statistics for those values, picks joins and access methods, and caches the plan. Every later call reuses it, whatever values it passes.
That is usually a good thing, because compiling is expensive. It goes wrong when the data is skewed:
CREATE OR ALTER PROCEDURE dbo.GetOrders @CustomerID int
AS
SELECT o.OrderID, o.OrderDate, o.Total
FROM dbo.Orders AS o
WHERE o.CustomerID = @CustomerID;
- Customer 42 has 3 orders, so the best plan is an index seek plus a key lookup.
- Customer 1 (your biggest account) has 4 million orders, so the best plan is a scan.
Whichever customer calls first after the plan is compiled decides the plan everyone else gets. If a small customer goes first, the big one runs 4 million key lookups. If the big customer goes first, every small customer scans the table.
Plans get recompiled after a restart, a failover, a statistics update or sp_recompile, which is why the problem seems to come and go at random.
Why it's fast in SSMS
When you paste the query into SSMS with a local variable, or run the proc from a session with different SET options, you get a different cache entry and a fresh plan. The most common mismatch is ARITHABORT, which is ON in SSMS and OFF for most drivers. So your test is compiled for the value you typed, not the one the app sniffed.
