Why should you care?

A proc filtered by rare CustomerId vs a huge customer can reuse the wrong plan and tank performance.

See it live — copy this example

Run in SQL Server Management Studio (SSMS) or Azure Data Studio.

USE DataVerse;
CREATE OR ALTER PROCEDURE dbo.usp_OrdersByCity
    @City NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderId, Amount
    FROM dbo.Orders
    WHERE City = @City;
    -- Diagnose: OPTION (RECOMPILE) -- temporary
    -- Or copy to local var / optimize for unknown carefully
END
GO
EXEC dbo.usp_OrdersByCity @City = N'Pune';
EXEC dbo.usp_OrdersByCity @City = N'Mumbai';

What happened?

• Both calls share a cached plan shape sniffed from the first execution.
• If data is skewed, consider RECOMPILE, OPTIMIZE FOR, or redesign — not random hints forever.

Practice next

1. Create the proc and run for two cities.
2. Inspect plans from the plan cache for that proc.
3. Try OPTION (RECOMPILE) in lab and compare.
4. Update stats and rerun both cities.
5. Compare estimated rows for each city.

Remember

First parameters influence cached plans. Skewed data exposes sniffing pain. Fix with measured options, not folklore.

City proc uneven

DataVerse Mumbai has 100× Pune rows; sniffed plan hurts Pune.

Outcome: Team enables PSPO or targeted RECOMPILE on that proc.