Plan cache parameter sensitivity

On-call incidentHard35 minMicrosoft style

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