Plan cache parameter sensitivity
Your task
SQL Server. A stored procedure that powers a tenant dashboard is usually ~50 ms but several times a day spikes to 20+ s for some tenants and is instantly fine again after you free the plan cache. The proc filters by @tenantId. Most tenants have a few thousand rows; three mega-tenants have tens of millions. Query Store shows the SAME query hash flipping between a fast index-seek plan and a slow scan-heavy plan. No deploy correlates. Triage and give both an immediate and a durable fix.
It covers
- Parameter sniffing
- Plan cache
- Skewed data