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

OUTER APPLY in SQL Server -- When and Why to Use It

OUTER APPLY is one of those SQL Server operators that can seem mysterious at first, but once you understand its power, it becomes an invaluable tool in your T-SQL arsenal.  In this post I will try to demystify it using some fun examples with the stock market and trading.

What is OUTER APPLY?

OUTER APPLY is a table operator introduced in SQL Server 2005 that allows us to invoke a table-valued function for each row returned by an outer table expression.  Think of it as a 'for each row, do this' operator that can reference columns from the outer query.

The key difference between OUTER APPLY and CROSS APPLY is that OUTER APPLY returns all rows from the left table expression, even if the right table expression returns no rows (similar to LEFT JOIN vs INNER JOIN).

When to use OUTER APPLY?

1. Top N Per Group Scenarios - A very common use case is retrieving the most recent trades for each symbol.

-- Get the 5 most recent trades for each symbol

SELECT 

    s.Symbol,

    s.CompanyName,

    s.Sector,

    t.TradeID,

    t.TradeDate,

    t.Price,

    t.Volume,

    t.Side -- BUY/SELL

FROM dbo.Securities s

OUTER APPLY (

    SELECT TOP 5

        TradeID, 

        TradeDate, 

        Price,

        Volume,

        Side

    FROM dbo.Trades t

    WHERE t.Symbol = s.Symbol

    ORDER BY TradeDate DESC

) t

2. Complex Market Calculations with Row Context - When you need to perform

complex calculations that depend on the current security.

-- Calculate VWAP and trading statistics for each symbol

SELECT 

    s.Symbol,

    s.LastPrice,

    stats.VWAP,

    stats.TotalVolume,

    stats.BuyVolume,

    stats.SellVolume,

    stats.ImbalanceRatio

FROM dbo.Securities s

OUTER APPLY (

    SELECT 

        SUM(Price * Volume) / NULLIF(SUM(Volume), 0) AS VWAP,

        SUM(Volume) TotalVolume,

        SUM(CASE WHEN Side = 'BUY' THEN Volume ELSE 0 END) AS BuyVolume,

        SUM(CASE WHEN Side = 'SELL' THEN Volume ELSE 0 END) AS SellVolume,

        CAST(SUM(CASE WHEN Side = 'BUY' THEN Volume ELSE 0 END) as FLOAT) / 

            NULLIF(SUM(CASE WHEN Side = 'SELL' THEN Volume ELSE 0 END), 0) AS ImbalanceRatio

    FROM dbo.Trades t

    WHERE t.Symbol = s.Symbol

    AND t.TradeDate >= DATEADD(HOUR, -1, GETDATE()) -- Last hour

) stats

3. Finding Related Market Events - Perfect for correlating trades with market events

or finding bracket orders:

-- Find the next fill after each order placement

SELECT

o.OrderID,

o.Symbol,

o.OrderTime,

o.OrderType,

o.LimitPrice,

fill.ExecutionTime,

fill.FillPrice,

fill.FillQuantity,

DATEDIFF(MILLISECOND, o.OrderTime, fill.ExecutionTime) AS LatencyMs

FROM dbo.Orders o

OUTER APPLY (

SELECT TOP 1

e.ExecutionTime,

e.Price as FillPrice,

e.Quantity as FillQuantity

FROM dbo.Executions e

WHERE e.OrderID = o.OrderID

AND e.ExecutionTime >= o.OrderTime

ORDER BY e.ExecutionTime

) fill

WHERE o.OrderDate = CAST(GETDATE() as DATE)

4. Portfolio Position Calculations - Calculate running positions and P&L per account.

-- Get current positions with realized P&L

SELECT

a.AccountID,

a.AccountName,

pos.Symbol,

pos.NetPosition,

pos.AvgCost,

pos.RealizedPnL

FROM dbo.Accounts a

OUTER APPLY (

SELECT

Symbol,

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

AVG(CASE WHEN Side = 'BUY' THEN Price END) AS AvgCost,

SUM(CASE WHEN Side = 'SELL' THEN (Price - Cost) * Quantity ELSE 0 END) AS RealizedPnL

FROM dbo.Trades t

WHERE t.AccountID = a.AccountID

AND t.TradeDate >= DATEADD(DAY, -30, GETDATE())

GROUP BY Symbol

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

) pos

When NOT to Use OUTER APPLY?

When Simple Joins Suffice - If you're just joining trading tables on a simple

condition without needing row-by-row operations, a regular JOIN is more

readable and often performs better.

1. One of my biggest bandwagons... KISS. Keep It Simple Stupid. If you don't need

it and the simple JOIN will suffice, don't overcomplicate things.  

-- Don't use OUTER APPLY for this:

SELECT s.*, t.LastTradePrice

FROM dbo.Securities s

OUTER APPLY (

SELECT Price as LastTradePrice

FROM dbo.Trades t

WHERE t.Symbol = s.Symbol

) t

-- Use a simple LEFT JOIN instead:

SELECT s.*, t.Price as LastTradePrice

FROM dbo.Securities s LEFT JOIN Trades t

ON s.Symbol = t.Symbol

2. Set-Based Operations Work Better (THE BEST!)

When you can solve the problem with window functions or aggregate queries,

they're usually more efficient.

-- Instead of OUTER APPLY for trade ranking, consider using ROW_NUMBER() with a CTE

WITH RankedTrades AS (

SELECT

Symbol,

TradeID,

Price,

Volume,

TradeDate,

ROW_NUMBER() OVER (PARTITION BY Symbol ORDER BY Volume DESC) AS VolumeRank

FROM dbo.Trades

WHERE TradeDate = CAST(GETDATE() as DATE)

)

SELECT * FROM RankedTrades WHERE VolumeRank <= 10

Performance Concerns with High-Frequency Data OUTER APPLY essentially performs a correlated subquery for each row.

With high-frequency trading data (millions of trades), this can be slower than

alternative approaches without proper indexing. With or without OUTER APPLY,

here are some Performance Tips for Trading Systems: 1. Index critical columns: Symbol, TradeDate, AccountID in your trades table 2. Partition large tables by TradeDate for better performance 3. Consider columnstore indexes for analytical queries on trade history 4. Test with realistic data volumes - what works for 1000 trades might not scale to 10 million 5. Use READ UNCOMMITTED when appropriate for real-time market data queries

And, because you know how much I love the market data...

-- Get bid/ask spread and depth for each symbol

SELECT

s.Symbol,

s.LastPrice,

depth.BestBid,

depth.BestAsk,

depth.Spread,

depth.BidDepth,

depth.AskDepth

FROM dbo.Securities s

OUTER APPLY (

SELECT

MAX(CASE WHEN Side = 'BUY' THEN Price END) AS BestBid,

MIN(CASE WHEN Side = 'SELL' THEN Price END) AS BestAsk,

MIN(CASE WHEN Side = 'SELL' THEN Price END) -

MAX(CASE WHEN Side = 'BUY' THEN Price END) AS Spread,

SUM(CASE WHEN Side = 'BUY' THEN Size ELSE 0 END) AS BidDepth,

SUM(CASE WHEN Side = 'SELL' THEN Size ELSE 0 END) AS AskDepth

FROM dbo.MarketDepth md

WHERE md.Symbol = s.Symbol

AND md.UpdateTime >= DATEADD(SECOND, -1, GETDATE())

) depth

WHERE s.IsActive = 1

OUTER APPLY shines in trading systems when you need row-by-row operations

that reference the outer query, especially for finding recent trades per symbol,

calculating running positions and correlating market events. However, with

high-frequency trading data, always benchmark against window functions and

consider your indexing strategy carefully.

More to read. FROM Clause

AI in SQL Server - Are We Really Doing This?

I’ll be honest -- AI inside SQL Server feels a bit odd to me. I’ve spent a couple decades making SQL Server do exactly what I tell it to do, and now Microsoft wants me to let the engine 'think for itself' -- semantic search, AI calls from T-SQL, even Copilot in SSMS.  Databases are supposed to be deterministic and predictable. Aren't they?  Now we’ve got fuzzy features like vector search, Copilot in SSMS, and T-SQL calling external AI models. 

Weird?  Absolutely.  But is it usable?

• A search that knows 'red shoes' ≈ 'scarlet trainers' ?  Useful.
• Survey results summarized without leaving the database?  Very tempting.
• A query draft when I’m too tired to type JOINs?  There have been some nights... 

Yes, it’s weird, and maybe risky -- but it’s here.  The challenge for us old-school DBAs isn’t to love it, but to test it, cage it, and identify when it’s worth the ride.

The AI-ready enterprise database from ground to cloud

2025 Features That Have My Attention

Intelligent Applications and AI - SQL Server | Microsoft Learn

SQL Server 2025 - New Features

CoPilot in SSMS?  What?!!!   I've been working SQL Server long enough to remember when the maintenance plan wizard was fancy!  Now Microsoft is stuffing AI into everything -- whether we like it or not.  This post is to show you some of what SQL Server 2025 brings, where and how it could help -- and what you should keep an eye on before diving in headfirst.  

AI in T-SQL

What is it?  Define AI models in T-SQL, run embeddings, hit REST endpoints from inside SQL.

Why use it?  Summarize survey text, classify products, or enrich transactions — all without moving data out of the database.

Caution:  Easy to overuse. If every SELECT starts calling out to an AI model, say goodbye to predictable query times (and hello to new invoice surprises).

Copilot

What is it?  AI built right into SSMS to suggest queries, troubleshoot, and even generate code.

Why use it?  Junior devs can get a leg up writing queries, DBAs can prototype faster.  It’s autocomplete on steroids.

Caution:  NEVER trust it blindly.  Copilot will happily generate a beautiful query that runs for 17 hours and eats your tempdb alive.  Think of it like a well-meaning intern -- great for drafts, terrible if left unsupervised.

Native Vector Search

What is it?  Vector data types + DiskANN indexes let SQL Server do a similarity search -- find things that feel alike, not just exact matches.  Pretty cool, imo.

Why use it?  Let’s say you run an e-commerce site. A customer searches for 'red running shoes', but your product catalog has 'scarlet trainers'.  A normal text search won’t match.  Vector search uses embeddings to understand that 'red' ≈ 'scarlet' and 'shoes' ≈ 'trainers'.  Suddenly your results are relevant, rather than incomplete and frustrating.

Caution:  Embeddings and vector indexes have overhead for storage and compute.  Great for search and discovery, but don’t sprinkle them on every column just because it sounds cool.

Change Event Streaming (CES)

What is it?  Real-time event streaming into Azure Event Hubs/Fabric.

Why use it?  No more waiting for nightly ETL. Dashboards update within minutes, IoT feeds light up alerts immediately.

Caution:  Great for visibility, dangerous if you don’t throttle. Streaming everything 'just in case' is a quick way to set your cloud bill on fire.

Optimized Locking  -- I really like this one!

What is it?  SQL Server delays row locks until it knows the row qualifies.  Finally!

Why use it?  OLTP systems finally get fewer blocking chains.  A ticketing system, POS, or ERP will see smoother throughput.

Caution:  Don’t assume it’s a silver bullet. Bad indexing or ugly queries will still hurt — they’ll just hurt a little less.

Security Enhancements

What is it?  Stronger TLS defaults, tighter identity integration, and instant permission cache invalidation.

Why use it: Compliance teams get faster enforcement. No more 10-minute lag where someone 'revoked' still had access.

Caution:  Security is only as good as your process. If your shop’s still sharing SA passwords on Post-it notes, SQL 2025 can’t save you.

Fabric Integration

What is it?  Native mirroring into Microsoft Fabric for near real-time analytics.

Why use it?  Your CFO finally gets dashboards that are minutes behind production instead of hours or days.

Caution:  Great for reporting, but watch schema drift. If you change structures on the OLTP side without planning, Fabric won’t forgive you.

Smarter Query Processing  -- another favorite!

What is it?  More Intelligent Query Processing tricks — adaptive joins, feedback loops, skew-aware parallelism.

Why use it?  That query that used to bomb on 'edge case' parameters now auto-adjusts.  Fewer 2AM fire drills.

Caution:  Still not magic. IQP doesn’t replace query tuning — it just means SQL Server can dig you out of some holes. Not all.

These are not all of them, by any means, but they are the ones that have gotten my attention.  Many of the features are still Preview rather than GA (Generally Available), so behaviors may change.  We also have to factor in compatibility level requirements for some of the smarter query processing features which need 170+ to be enabled.  This could mean that some of your old queries may misbehave. 

I think it's all still pretty new, and new features all bring unknowns.  Best to read up on them before jumping in.   What's New in SQL Server 2025

Optimized Locking in SQL Server 2025 -- Concurrency Gets Smarter

For years, blocking chains and lock contention have been part of the DBA’s daily grind. SQL Server 2025 introduces a major shift with Optimized Locking — said to be a smarter mechanism that reduces wasted locks, improves concurrency, and lowers memory pressure. It has two main components:

1. Lock After Qualification (LAQ)
2. Transaction ID (TID) Locking

The Old Way: Lock First, Ask Later

Traditionally, SQL Server grabs locks as rows were scanned before checking whether they matched the query filter:

• Rows that didn’t qualify still got locked
• Unnecessary locks blocked other sessions
• Large scans with selective predicates suffered the most

It was basically 'lock first, ask questions later'.  Things are very different now.

Lock After Qualification (LAQ)

One of the Optimized Locking components is Lock After Qualification (LAQ), where:

• SQL Server first evaluates rows against the query’s predicate
• Only if a row qualifies does the engine acquire the lock
• This reduces blocking, lowers lock memory use, and improves throughput 

 
    EXAMPLE

    -- Session 1
    BEGIN TRAN;
    UPDATE Sales.Orders
    SET Status = 'Closed'
    WHERE OrderDate < '2025-01-01';
    -- (do not commit yet)

    -- Session 2
    SELECT COUNT(*)
    FROM Sales.Orders
    WHERE OrderDate >= '2025-01-01';

      Pre-SQL Server 2025 - Session 2 could block because Session 1                                 locked rows it wasn’t even updating.

      With SQL Server 2025 - Session 2 runs without blocking and only qualifying               rows are locked.

Transaction ID (TID) Locking

The other half of Optimized Locking is TID Locking, where

• Each row carries the ID of the transaction that last modified it.
• Instead of holding thousands of row/page locks until commit, SQL Server can represent them with a single TID lock.
• This prevents lock escalation, reduces lock memory, and improves concurrency.

Requirements (and caveats)

• Accelerated Database Recovery (ADR) must be enabled.
• Read Committed Snapshot Isolation (RCSI) is recommended.
• Some statements are excluded (OUTPUT clauses, variable assignments, certain joins/index patterns).
• If overhead becomes too high, SQL Server can heuristically disable LAQ.

Optimized Locking may not sound as cool as 'AI integration' or 'vector search', but it delivers results every DBA will notice right away, making concurrency management a whole heckuva lot easier than it has ever been!

• Fewer wasted locks -- people, this is HUGE!
• Less blocking under concurrency
• More efficient resource usage
• Simpler troubleshooting with sys.dm_tran_locks

More to read:    Optimized Locking

Accelerated Database Recovery

Azure Just Killed TLS 1.0/1.1 -- If Your Azure SQL blipped, here’s Your 10-Minute Save.

On August 31, 2025, Microsoft finished retiring TLS 1.0/1.1 across Azure. If any caller still negotiates those protocols, Azure SQL (DB or MI) will refuse the handshake until the client stack is modernized. Good. It’s time.

Read this:   Azure's TLS retirement

Why DBAs should care

• Minimum TLS 1.2 is table stakes. You set it per logical server in the portal. 

• Finding the laggards is easy: enable Azure SQL Auditing and check client_tls_version_n. That’s your hit list. 

• Drivers aren’t the villain: modern ODBC 17/18 and Microsoft.Data.SqlClient already speak TLS 1.2+. The risk is that one dusty Windows service from 2012. (Upgrade it.)

Do this now 

1. Enforce TLS ≥ 1.2
   Azure portal → SQL logical server → Networking → Connectivity → Minimum TLS version = 1.2 (or 1.3) → Save.  Azure Connectivity

2. Modernize client stacks
   Move to ODBC 17/18 or Microsoft.Data.SqlClient; retire SQL Native Client.

3. Prove you’re clean
   Query audit logs for client_tls_version_n and chase anything < 1.2. Keep a short “offenders” list for the next patch window.   

Curiosity corner: TLS 1.3 is supported with TDS 8.0 on SQL Server 2022/Azure SQL—but don’t disable TLS 1.2 yet; some satellite services still need it.  TLS 1.3 Support 

Patch-window PSA

SQL Server 2022 CU21 (Sept 2025) is out; it includes fixes and a known issue with SESSION_CONTEXT in parallel plans. 

Read these notes before you blanket-roll to prod. CU Details

And more.   Azure Database Support

Contained Availability Groups - Finally! MSFT Heard Our Screams

The Old World Horror Story

It's 2 AM. You're on call. The primary replica of your critical Always On Availability Group just failed over. Everything should be fine, right?  Wrong.

Suddenly, your phone explodes.  Applications can't connect.  Jobs are failing.  Reports are timing out... Why?  Because that one SQL login you created last week on the primary replica doesn't exist on the secondary.  Or worse, it exists but with a different SID.  Welcome to the seventh circle of DBA hell.

If you've managed traditional Availability Groups (AGs) for any length of time, you know this pain.  Every single system object -- logins, AGENT JOBS, linked servers, server-level permissions -- must be manually synchronized across every replica.  Miss one?  Enjoy your incident ticket.

The Symphony of Suffering

Let's count the ways traditional AGs made us suffer:

Login Roulette.  Creating a login on the primary?  Better script it out with the exact same SID and password hash for every secondary.  Oh, and don't forget to update all replicas when someone changes their password.  Fun times!

Job Juggling.  My LEAST FAVORITE, the SQL Server Agent jobs only run where they're created.  Failover to a secondary?  Hope you remembered to disable those jobs on the secondaries and have a process to enable them post-failover.  Spoiler: you didn't.

Linked Server Limbo.  Each linked server needs to be configured identically on every replica. Connection strings, security contexts, provider options -- one tiny difference and your cross-server queries explode spectacularly.

Permission Purgatory.  Server-level permissions don't replicate. That service account that needs VIEW SERVER STATE?  Better grant it everywhere. Manually. Every. Single. Time.

This wasn't just inconvenient -- it was a reliability nightmare and a complete pain in the a*s.  Microsoft clearly got tired of hearing us complain about it at every conference, user group, and feedback forum.

Enter the Hero. Contained Availability Groups

With SQL Server 2022, Microsoft finally said, "Hey.  Let's fix this mess."  Contained Availability Groups (Contained AGs) bring sanity to the chaos by automatically synchronizing system metadata between replicas.

Think of it as a bubble around your AG that includes the databases AND all system objects that they databases need to function. Logins, AGENT JOBS, permissions -- they all live inside the bubble now, traveling together, hands held and smiling as one happy family during failovers.  Serious!

The Magic Under the Hood

Contained AGs store system metadata in the master database of each replica, but here's the key:  this metadata is scoped specifically to the AG.  When you create a login in a Contained AG, it's not just a regular server login -- it's an AG-contained login that automatically synchronizes to all replicas.

No more SID mismatches. No more orphaned users. No more 2 AM panic attacks.

Making the Jump From Traditional to Contained

Ready to escape the old world?  Here's the deal:  You cannot convert a traditional AG to a Contained AG.  SQL Server doesn't support changing an AG from one type to the other.  You have to create a new Contained AG from scratch. But don't worry -- we've got options for minimizing the pain.

Step 1: Check Your Version First, ensure you're on SQL Server 2022 or later. Contained AGs are the new kids on the block.

Step 2: Choose Your Migration Strategy

Option A: The UIP (Upgrade-in-Place) - Downtime Required, so plan accordingly.  This is the straightforward but disruptive approach:

• Script all relevant logins, jobs, and linked servers from your current primary
• Generate FULL database backups for all AG-databases
• Take your databases offline from the traditional AG
• Drop the traditional AG completely
• Create a new Contained AG using the same databases
• Re-create your system objects within the Contained AG context (via the listener)
• Bring everything back online

Option B: The Side-by-Side Migration (Zero Downtime!)  The smooth operator's approach... A little more complex but no downtime.

• Set up a parallel infrastructure

  • You'll need completely separate SQL Server instances because a database can only belong to one AG at a time
  • This means temporarily doubling your infrastructure:
    • Spin up new VMs/servers (ideal if you have cloud elasticity)
    • Leverage Azure SQL VM or AWS EC2 for temporary replicas
    • Use existing dev/test servers temporarily (risky but it can be done)
    • Install additional named instances on existing hardware (watch those resources!)

• Create your new Contained AG on the new infrastructure

  CREATE AVAILABILITY GROUP [YourContainedAG]
  CONTAINED  <-- The magic word!
  WITH (
      AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
      DB_FAILOVER = ON,
      DTC_SUPPORT = PER_DB
  )
  FOR DATABASE [YourDatabase]
  REPLICA ON 
      'NewServer1' WITH (ENDPOINT_URL = 'TCP://NewServer1:5022'),
      'NewServer2' WITH (ENDPOINT_URL = 'TCP://NewServer2:5022')
  

• Migrate your databases

  • Restore previously created FULL backups WITH NORECOVERY to your new Contained AG primary instance
  • Add databases to the Contained AG
  • Let automatic seeding handle the secondaries

• Recreate system objects in the Contained AG context

  • Connect to the Contained AG listener (not the instance)
  • Script in all logins, AGENT JOBS, linked servers
  • These will now automatically replicate within the Contained AG

• Test thoroughly

  • Point test applications to the new AG listener
  • Verify logins, permissions, and jobs work correctly
  • Run parallel for several days to verify all business points of operation

• Cut over your applications

  • Update connection strings to point to the new Contained AG listener
  • Keep the old AG running as a fallback

• Decommission the old infrastructure

  • After a burn-in period (72+ hours recommended)
  • Drop the traditional AG
  • Repurpose or decommission the old servers

Step 3: Verify Your Success Fail over to a secondary and watch in amazement as everything just... works. No missing logins. No broken jobs. It's beautiful.

The Fine Print

Before you rush off to implement Contained AGs everywhere, a few considerations:

• SQL Server 2022+ only.  No backporting this goodness to older versions
• Some limitations apply.  Not all system objects are supported (yet)
• Plan your migration.  Moving from traditional to contained requires some downtime
• Different management approach.  Some DMVs and system views work differently with contained objects

The Bottom Line

Contained Availability Groups aren't just an incremental improvement—they're a fundamental fix to one of the most frustrating aspects of SQL Server high availability.  Microsoft clearly listened to the community's pain and delivered a solution that actually works.

If you're still managing traditional AGs and spending hours keeping system objects in sync, it's time to seriously consider SQL Server 2022 and Contained AGs.  Your future self (and your sleep schedule) will thank you.

More information:

What is a Contained Availability Group ?

---

Have you made the jump to Contained AGs? What's been your experience? Drop a comment below and let's compare notes on escaping the old world of AG management.

How long has your SQL Server been running for?

Very good question, and often something you will need to know.  This will get you there quickly.

SET NOCOUNT ON

USE master;

DECLARE 

@crdate DATETIME, 

@hr VARCHAR(50), 

@min VARCHAR(5)

SELECT @crdate = crdate FROM sysdatabases WHERE NAME='tempdb'

SELECT @hr = (DATEDIFF ( mi, @crdate, GETDATE())) / 60

IF ((DATEDIFF (mi, @crdate, GETDATE()))/60) =0 SELECT @min = (DATEDIFF (mi, @crdate,GETDATE()))

ELSE SELECT @min =(DATEDIFF (mi, @crdate,GETDATE())) - ((DATEDIFF(mi, @crdate, GETDATE())) / 60)*60;

PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" has been running for the past '+ @hr + ' hours & '+ @min + ' minutes.'

    

Mine hasn't been running for very long, but you get the point.

More details regarding your SQL Server start times:  last restart