SQL Server 2022's Parameter Sensitive Plan Optimization - A Game Changer for Trading Systems

If there's one feature that's generating buzz in the SQL Server community right now, it's Parameter Sensitive Plan (PSP) Optimization in SQL Server 2022. For trading systems where query performance can directly impact P&L, this intelligent query processing enhancement tackles one of the most persistent performance headaches: parameter sniffing problems.

The Parameter Sniffing Problem in Trading Systems

Parameter sniffing occurs when SQL Server creates an execution plan based on the first set of parameters passed to a stored procedure, then reuses that plan for all subsequent executions. In trading systems, this would be particularly problematic.

CREATE PROCEDURE dbo.usp_GetTradesBySymbol (

    @Symbol VARCHAR(10),

    @StartDate DATETIME

)

AS

SELECT 

    TradeID, 

    Symbol, 

    Price, 

    Volume, 

    Side,

    TradeDate

FROM dbo.Trades

WHERE Symbol = @Symbol 

AND TradeDate >= @StartDate

When this procedure is first called with a highly liquid symbol like 'SPY' (millions of trades), SQL Server might choose a parallel scan.  But when later called with an illiquid symbol like 'XYZ' (hundreds of trades), that same plan is inefficient.

How PSP Optimization Works

SQL Server 2022's PSP Optimization automatically detects when different parameter values would benefit from different execution plans.  It creates multiple plan variants based on the statistics histogram, essentially solving the 'one plan fits all' problem. 

Before PSP (SQL Server 2019 and earlier):

-- First execution with high-volume symbol

EXEC GetTradesBySymbol @Symbol = 'AAPL', @StartDate = '2024-01-01'

-- Subsequent execution with low-volume symbol  

EXEC GetTradesBySymbol @Symbol = 'ZZZZ', @StartDate = '2024-01-01'

That 1st call creates a plan optimized for millions of rows, and that 2nd call reuses the exact same plan - terribly inefficient!

With PSP (SQL Server 2022), the optimizer now creates multiple plan variants automatically:

- One optimized for high-cardinality symbols (parallel scan)
- Another for low-cardinality symbols (index seek + lookup)
- Possibly others based on your data distribution

To identify PSP Opportunities in your trading database, look for these patterns in your trading system queries:

-- Find procedures with high variance in execution stats

SELECT 

    p.name as ProcedureName,

    s.execution_count,

    s.total_logical_reads / s.execution_count AS AvgReads,

    s.min_logical_reads,

    s.max_logical_reads,

    CAST(s.max_logical_reads as FLOAT) / NULLIF(s.min_logical_reads, 0) AS VarianceRatio

FROM sys.dm_exec_procedure_stats s JOIN sys.procedures p 

  ON s.object_id = p.object_id

WHERE s.max_logical_reads > s.min_logical_reads * 10 -- high variance

ORDER BY VarianceRatio DESC

 

Real-World Trading System Example

Consider this portfolio valuation query that suffers from parameter sniffing:

CREATE PROCEDURE dbo.usp_GetPortfolioPositions (

    @AccountID INT,

    @AsOfDate DATETIME

)

AS

SELECT 

    t.Symbol,

    SUM(CASE WHEN Side = 'BUY' THEN Quantity ELSE -Quantity END) AS NetPosition,

    AVG(CASE WHEN Side = 'BUY' THEN Price * Quantity ELSE 0 END) / 

        NULLIF(SUM(CASE WHEN Side = 'BUY' THEN Quantity ELSE 0 END), 0) AS AvgCost,

    s.LastPrice,

    (s.LastPrice - AVG(CASE WHEN Side = 'BUY' THEN Price ELSE 0 END)) * 

        SUM(CASE WHEN Side = 'BUY' THEN Quantity ELSE -Quantity END) AS UnrealizedPnL

FROM dbo.Trades t JOIN Securities s 

  ON t.Symbol = s.Symbol

WHERE t.AccountID = @AccountID

AND t.TradeDate <= @AsOfDate

GROUP BY t.Symbol, s.LastPrice

HAVING SUM(CASE WHEN Side = 'BUY' THEN Quantity ELSE -Quantity END) <> 0

Institutional accounts might have thousands of positions, while retail accounts have just a few. The PSP Optimization automatically handles both scenarios efficiently -- without you!

Enabling and Monitoring PSP

-- Enable at Database Level

ALTER DATABASE TradingDB 

SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON

-- Check which of your queries are using PSP

SELECT 

    qp.query_id,

    qt.query_sql_text,

    qp.plan_id,

    qp.query_plan,

    rs.count_executions,

    rs.avg_duration / 1000.0 as avg_duration_ms,

    rs.avg_logical_io_reads

FROM sys.query_store_query_text qt JOIN sys.query_store_query q 

  ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan qp 

    ON q.query_id = qp.query_id JOIN sys.query_store_runtime_stats rs 

  ON qp.plan_id = rs.plan_id

WHERE qp.query_plan LIKE '%ParameterSensitivePlan%'

ORDER BY rs.count_executions DESC

Best Practices for Trading Systems

- Enable Query Store first to baseline performance
- Test with production-like data - trading data has unique distribution patterns
- Monitor plan cache for dispatcher stubs and variant plans
- Consider combining with other features like Memory Grant Feedback and  

  Adaptive Joins
- Watch for increased plan cache memory usage with multiple variants

When PSP Might Not Help
- Queries already using OPTION (RECOMPILE)
- Procedures with complex branching logic
- Queries where data distribution doesn't vary significantly
- Systems already using plan guides or forced plans effectively

For trading systems where microseconds matter and parameter values vary wildly (ie., symbol liquidity, account sizes, date ranges), Parameter Sensitive Plan Optimization (PSP) is a game changer.  It eliminates many scenarios where we previously had to resort to OPTION (RECOMPILE), plan guides, or multiple procedure variants. 

The beauty is that it's largely automatic - enable it, monitor it, and let SQL Server handle the complexity of maintaining multiple optimal plans.  Your trading system gets the performance benefits without the maintenance overhead of manual optimization techniques. 

Read more  Parameter Sensitive Plan Optimization