Why should you care?

One plan for a huge customer and a tiny customer often fails; PSPO keeps variants.

See it live — copy this example

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

USE DataVerse;
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;
CREATE OR ALTER PROCEDURE dbo.usp_OrdersByCityPspo @City NVARCHAR(50)
AS
SELECT OrderId, Amount FROM dbo.Orders WHERE City = @City;
EXEC dbo.usp_OrdersByCityPspo @City = N'Mumbai';
EXEC dbo.usp_OrdersByCityPspo @City = N'Pune';

What happened?

• Enabling PSPO lets the engine consider multiple plans for sensitive parameters.
• Still design indexes well — PSPO is not a substitute for schema quality.

Practice next

1. Enable PSPO on your database (2022+).
2. Run the city proc for skewed cities.
3. Inspect Query Store for multiple plans.
4. Check Query Store plan counts for the proc.
5. Update stats and retest.

Remember

PSPO caches multiple parameter plans. Helps skewed data. Available on SQL Server 2022+.

Skewed city traffic

DataVerse Mumbai dwarfs other cities.

Outcome: PSPO keeps separate plans; Pune stops timing out.