Legacy SQL Server ?? Microsoft Says It Isn’t. Its Partner Slide Said Otherwise.

A Microsoft partner slide put SQL Server under the heading '1st party legacy'. Brent Ozar flagged it, and a day later Microsoft said SQL Server isn't legacy. Many users saw the slide and they are already asking what it means. Here's what actually happened, what Microsoft shipped, and why that "legacy" label deserves more scrutiny than Microsoft's explanation alone.

The Slide

FabCon and SQLCon Europe ran September 28 to October 1, 2026 in Barcelona. At a partner day during the event, a slide was photographed and shared on LinkedIn. Brent posted it on September 30 in I'm Just Gonna Leave This Here.

One column reads "1st party legacy". The line under it reads "Modernize every SQL Server". Microsoft is the first party. Brent zoomed in on that pair of columns and called it what it reads like: Microsoft telling its partners that SQL Server is a legacy database.

That's a pretty significant label to put on a product that Microsoft released a new major version of less than a year ago. In enterprise IT, "legacy" generally suggests aging technology that organizations should be planning to replace or retire. It doesn't mean a supported, actively developed database platform simply because it isn't running in Azure. There's a difference, and Microsoft should know better than to blur it in a presentation to its own partners.

Image: Brent Ozar, from a slide shared on LinkedIn.

The post drew more than 40 comments. Postgres came up early. So did government and regulated shops that are required to keep data on-prem. Brent asked the obvious question -- if SQL Server is legacy, and the next step is Azure SQL DB or Fabric, what exactly are customers running in AWS supposed to do?

Microsoft's Response

On October 1, Anna Hoffman of Microsoft replied in the comments. She said the wording on the partner slide was poor, and that the two lines described separate modernization motions, not a designation of SQL Server. Then she said it plainly:

"Microsoft does not consider SQL Server a legacy product."

Brent wasn't done. He pointed back to the slide's own wording and asked Microsoft what it should have said instead. That's where the thread stands.

Both can be true. The product team can genuinely consider SQL Server a modern, actively developed platform while Microsoft's partner messaging encourages customers to move toward Azure. There's nothing inherently wrong with that strategy, but calling SQL Server "legacy" is a very different message from encouraging customers to modernize their existing deployments.

A migration strategy is not a product lifecycle announcement. Let's look at what Microsoft actually shipped.

What Microsoft Shipped

The same week, Microsoft published its SQLCon Barcelona announcements. The first section is titled "Running data and AI on infrastructure you control", and it walks through TDE in 2008, Always Encrypted in 2016, and Ledger in 2022 as decades of SQL Server investment. That is not how you talk about a legacy product. The announcements included:

SQL Server on Azure Local - GA
Both connected and disconnected operations. Disconnected means a local control plane for environments where cloud connectivity is unavailable or restricted. Microsoft pitched it squarely at digital sovereignty. That is SQL Server running on infrastructure you control. Announcement

Agent Mode for GitHub Copilot in SSMS - GA
SQL Projects and Schema Compare in SSMS went GA with it. You don't keep building out the client tool for a product you're retiring.

Azure SQL Database Hyperscale
160 and 192 vCore Premium-series GA, serverless auto-pause in preview, DiskANN vector indexes GA, and a new Azure Arc migration path to Hyperscale in preview.

Database Hub in Microsoft Fabric - Public Preview
Inventory, health, performance and security signals across your database estate, with database agents for SQL and PostgreSQL.

Read that list honestly. There is one on-prem GA and one tooling GA. The rest is Azure and Fabric. The marketing energy is pointed at the cloud, but the engine is still getting investment.

The servicing cadence tells the same story. SQL Server 2025 has had nine cumulative updates in 2026, one a month since January. CU9 (KB5122048) shipped September 15, 2026 as build 17.0.5005.3. One week earlier, security updates shipped for both the GDR branch (KB5122770) and the CU branch (KB5122769). That's an active servicing commitment to Microsoft's current database engine, not evidence of a product being phased out.

The Support Calendar Doesn't Say Legacy

Slides change. Lifecycle commitments are published. These are straight from Microsoft Learn:

Version	Mainstream End	Extended End
SQL Server 2025	1/7/2031	1/7/2036
SQL Server 2022	1/12/2028	1/12/2033
SQL Server 2019	3/1/2025	1/9/2030
SQL Server 2017	10/12/2022	10/13/2027

Microsoft Learn lists each end date at 6:59:59 AM Pacific Time.

SQL Server 2025 is supported into 2036. That's a decade of committed patches for the version that shipped last November. Whatever the slide meant, that date is the one your contracts and your compliance people care about.

The calendar that actually matters right now is the other end. SQL Server 2016 reached end of support on July 14, 2026. SQL Server 2017 is next, in October 2027, and Brent posted a one-year countdown on October 12. If you're running either one, that is your legacy problem. Not the slide.

Know Where You Stand

Run this on each instance. It returns the version and current update level, so you can line it up against the table above.

SELECT
    @@SERVERNAME ServerName,
    CASE PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)), 4)
        WHEN '17' THEN 'SQL Server 2025'
        WHEN '16' THEN 'SQL Server 2022'
        WHEN '15' THEN 'SQL Server 2019'
        WHEN '14' THEN 'SQL Server 2017'
        WHEN '13' THEN 'SQL Server 2016'
        WHEN '12' THEN 'SQL Server 2014'
        WHEN '11' THEN 'SQL Server 2012'
        ELSE 'Older - check ProductVersion' END VersionName,
    SERVERPROPERTY('ProductVersion') ProductVersion,
    SERVERPROPERTY('ProductLevel') ProductLevel,
    SERVERPROPERTY('ProductUpdateLevel') UpdateLevel,
    SERVERPROPERTY('Edition') Edition;

Anything that comes back 2016 or older is already out of support. Anything that comes back 2017 has about a year. If it comes back 2025, compare ProductVersion against the build list. Mine says CU7. At the time of writing, the latest is CU9, 17.0.5005.3. Even a supported version can be behind. Start there.

If You're Not in Azure

A lot of SQL Server runs on-prem, in colos, and on AWS. None of those customers were in the room for that partner slide. All of them will hear about it.

Brent asked what AWS customers are supposed to do. AWS already answered. On July 21, 2026, Amazon RDS for SQL Server added SQL Server 2025 for Enterprise, Standard, and Developer editions. Existing RDS instances can upgrade by modifying the DB engine version. AWS supporting Microsoft's latest SQL Server release reinforces something that shouldn't need explaining: running SQL Server outside Azure doesn't make it legacy.

My advice to clients is simple. Don't make a platform decision off a partner slide or a blog comment. Make it off the lifecycle dates, your licensing, your RPO/RTO, and what your applications actually support. If that leads to Azure, fine. If it leads to SQL Server 2025 on the hardware you already own, Microsoft has committed to supporting that product through January 2036.

Microsoft is entitled to promote its cloud platforms, and customers are entitled to choose something else. What shouldn't get lost in that conversation is the distinction between modernizing a SQL Server environment and replacing SQL Server because someone decided to call it legacy.

SQL Server isn't legacy. SQL Server 2016 is.

More to Read

Brent Ozar: I'm Just Gonna Leave This Here
Microsoft: SQLCon Barcelona 2026 announcements
Microsoft: SQL Server on Azure Local is now generally available
Microsoft Learn: SQL Server 2025 build versions
Microsoft Learn: SQL Server 2025 lifecycle
Microsoft: SQL Server 2016 end of support is here
Brent Ozar: SQL Server 2017 Support Ends One Year From Today
AWS: Amazon RDS for SQL Server now supports SQL Server 2025

Your New REGEXP_LIKE Just Turned a Seek Into a Scan

SQL Server 2025 finally gave us native regex, and everyone is swapping their old LIKE patterns for the shiny new REGEXP_LIKE. Microsoft documents anchored REGEXP_LIKE patterns as SARGable, meaning they can potentially benefit from index access. I anchored mine with '^', but SQL Server scanned a million rows anyway.

March 2025, Brent said don't get too excited.... Here is the measurement that suggests things remain the same 18 months later.

The Setup

One table, one million rows, one index on the column we search. Only 100 of those million rows start with 'Smith' -- that matters, because a rare value is exactly the case an index seek is built to win. Compatibility level 170 is required. REGEXP_LIKE does not exist below that CL.

IF DB_ID('RegexDemo') IS NULL
CREATE DATABASE RegexDemo;
GO
USE RegexDemo;
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 170;

DROP TABLE IF EXISTS dbo.Customers;
GO
CREATE TABLE dbo.Customers
(
    CustomerID INT IDENTITY PRIMARY KEY,
    LastName VARCHAR(50) NOT NULL
);

/* 1,000,000 rows. Only 100 start with 'Smith' -- that is the point.
   Everybody else starts with 'Acct'. Hard cap in TOP. */
;WITH Tally AS
(
    SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
INSERT dbo.Customers (LastName)
SELECT CASE WHEN n % 10000 = 0
            THEN 'Smith' + CAST(n AS VARCHAR(10))   /* 100 rows */
            ELSE 'Acct'  + CAST(n AS VARCHAR(10))   /* the other 999,900 */
       END
FROM Tally;

CREATE NONCLUSTERED INDEX idx_Customers_LastName
    ON dbo.Customers (LastName);

The SELECT below returns only CustomerID and LastName. CustomerID is the clustered key, so it already rides inside the nonclustered index -- the index covers the query, gets rid of any key lookups, and the logical reads measure one thing cleanly: did we seek, or did we scan?

The Baseline: LIKE Seeks

Find every customer whose last name starts with Smith, the old way. STATISTICS IO on, actual execution plan on:

SET STATISTICS IO ON;
GO

SELECT CustomerID, LastName
FROM dbo.Customers
WHERE LastName LIKE 'Smith%';

Index Seek. Three logical reads to pull 100 rows out of a million. That's a number to remember.

The Regex The Docs say Should Have a Chance to Seek

Now the regex version. Microsoft's documentation identifies anchored patterns as SARGable, so I used ^Smith to express the same prefix search as LIKE 'Smith%'. The question is whether SQL Server will now take advantage of the existing index.

SELECT CustomerID, LastName
FROM dbo.Customers
WHERE REGEXP_LIKE(LastName, '^Smith');

Index Scan. 2,979 logical reads for the same 100 rows. The anchor is right there at the front of the pattern, the documentation says that makes it SARGable, and the optimizer scanned the entire index anyway.

The Scoreboard

Query	Operator	Logical reads
LIKE 'Smith%'	Index Seek	3
REGEXP_LIKE(LastName, '^Smith')	Index Scan	2,979

Same 100 rows. Same indexed column. The anchored regex cost roughly a thousand times the reads of the LIKE it was meant to replace.

Why 'SARGable' Didn't Save Me

SARGable means the optimizer is allowed to use a seek. It does not mean that it will. That choice comes down to cost, and cost comes down to how many rows the optimizer thinks it will find.

That is where things get interesting. SQL Server chose an index scan even though the pattern was anchored and the nonclustered index covered everything the query needed. Microsoft's documentation includes two query hints, ASSUME_FIXED_MIN_SELECTIVITY_FOR_REGEXP and ASSUME_FIXED_MAX_SELECTIVITY_FOR_REGEXP, specifically to adjust the default selectivity assumptions for regex predicates. That tells us selectivity estimation is an important consideration, but it doesn't prove that cardinality estimation caused this particular scan. What the execution plan does prove is that the optimizer didn't turn this anchored regex into the index seek we saw with LIKE.

The Takeaway

Do not swap LIKE for REGEXP_LIKE on a prefix search assuming the index will behave the same way. On my v2025 instance, the anchored regex scanned the index and required 2,979 logical reads to return the same 100 rows that the LIKE retrieved with just 3. That doesn't mean every anchored regex will scan, but it clearly demonstrates that SARGability alone is no guarantee of an efficient execution plan. REGEXP_LIKE earns its place where LIKE genuinely cannot do the job -- character classes, quantifiers, real pattern matching. For 'starts with Smith', the traditional LIKE 'Smith%' remains the better choice in this test.

Test it yourself before you trust it, and watch the reads. Rowcount is the same either way. Only the reads tell us what it costs.

/* Cleanup */
USE master;
DROP DATABASE IF EXISTS RegexDemo;

More to read

MSFT -- REGEXP_LIKE (Transact-SQL)
Brent Ozar -- T-SQL Has Regex in SQL Server 2025. Don't Get Too Excited.

SQL Copilot's Read-Only Mode Wasn't Read-Only.

I was in Italy for a month. While I was gone, somebody got sysadmin out of SQL Copilot with a variable.

The vulnerability is CVE-2026-65669, the SSMS 22 Copilot bug I wrote about early September. The full write-up went public on September 30th, and the detail that stuck with me is how Copilot's 'read-only mode' was enforced. It wasn't a permission. It was a regex blocklist.

That blocklist is the same control we have watched fail against SQL injection for twenty years. Before I get to Copilot, let's build one here and see it fail.

The Setup

First, a table to protect:

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.names;
GO
CREATE TABLE dbo.names (UserName SYSNAME NOT NULL);
INSERT dbo.names (UserName) VALUES (N'rebecca');
GO

Now a stored procedure that runs whatever query string you hand it, as long as it decides the string is read-only. It decides by scanning the text for keywords like INSERT, UPDATE and DELETE, and refusing the query if it finds one. I am building that check with LIKE so this runs on any version of SQL Server you have -- v2025 is not required.

CREATE OR ALTER PROCEDURE dbo.usp_ReadOnlyGuard
    @query NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    /* 'Read-only' enforcement: reject the dangerous keywords. */
    IF @query LIKE '%INSERT %'
    OR @query LIKE '%UPDATE %'
    OR @query LIKE '%DELETE %'
    OR @query LIKE '%DROP %'
    OR @query LIKE '%ALTER %'
    OR @query LIKE '%EXEC %'
    OR @query LIKE '%EXECUTE %'
    OR @query LIKE '%MERGE %'
    OR @query LIKE '%TRUNCATE %'
    BEGIN
        RAISERROR('Blocked: read-only mode permits SELECT only.', 16, 1);
        RETURN;
    END

    EXEC (@query);   /* approved -- run it */
END
GO

There is no AI here yet. This proc is standing in for it. You hand it a string, it checks the string and runs it -- exactly like Copilot's tool did. A legitimate read-only query goes straight through:

EXEC dbo.usp_ReadOnlyGuard @query = N'SELECT UserName FROM dbo.names;';

Purely SELECT, it ran exactly as intended. Now let's try two obvious attacks. A direct write, and a stored procedure call, both of which should be blocked.

EXEC dbo.usp_ReadOnlyGuard @query = N'UPDATE dbo.names SET UserName = N''x'';';

EXEC dbo.usp_ReadOnlyGuard @query = N'EXEC sp_who;';

Both blocked. Each one hit a keyword on the list, the guard stopped it and returned this:

Check your data. Confirm nothing moved:

SELECT UserName FROM dbo.names;

This is where people relax. The dangerous words were right there in the strings, the filter saw them, the writes never happened. Read-only mode works.

But no. It doesn't.

Start With the EXEC Check

The guard checks for nine keywords, and my query has to get past every one of them. Let's start with the execution check.

That check looks for EXEC followed by a space. But T-SQL lets me call EXEC(@s) with no space -- a parenthesis instead -- and it runs the same. So '%EXEC %' should miss it. Here is a harmless SELECT run that way, so there is nothing to block except the EXEC call itself:

EXEC dbo.usp_ReadOnlyGuard
     @query = N'DECLARE @s NVARCHAR(200) = N''SELECT UserName FROM dbo.names;''; EXEC(@s);';

No errors. It ran.

The guard never recognized an 'EXEC' because EXEC( has no space after it. That is the execution check beaten. One down.

Now Hide the Verb

Beating the EXEC check alone is not enough. This one is for 'UPDATE' which is also in the list and the guard flags '%UPDATE %'. So in this one, I will hide it by building it from two pieces -- N'UP' + N'DATE' -- which SQL Server joins into UPDATE only at runtime, long after the guard has checked the text.

Put both tricks together -- the no-space EXEC( and the split verb:

EXEC dbo.usp_ReadOnlyGuard
     @query = N'DECLARE @s NVARCHAR(200) = N''UP'' + N''DATE dbo.names SET UserName = N''''gotcha'''';''; EXEC(@s);';

No error. Check your data:

SELECT UserName FROM dbo.names;

This is the string the guard checked once SQL Server resolved the outer quotes:

DECLARE @s NVARCHAR(200) = N'UP' + N'DATE dbo.names SET UserName = N''gotcha'';'; EXEC(@s);

Not one blocked keyword in it. No UPDATE -- that word does not exist until UP and DATE join at runtime. No EXEC with a space -- it is EXEC(. The guard checked it, matched nothing, and ran it -- and your data changed.

That is a write in read-only mode, and UPDATE is the gentle version. The guard just ran a string I built at runtime, and that string can hold anything -- changing a column today, adding my login to the sysadmin role tomorrow. I could go on. The guard is reading one version of the string, and SQL Server is running another.

The researcher who found the Copilot bug got to the same place through a different door -- DECLARE @p sysname='sp_executesql'; EXEC @p, running the procedure through a variable so the blocklist never saw its name. Different keyword, same lesson. Once any route to dynamic execution survives the filter, read-only is over.

Why This Is the Whole Problem

Here is the table I wish had been on a wall in Redmond.

Control	What it checks	Gets talked around?
Keyword blocklist	The text of the string	Yes -- rename it, split it, wrap it
A GRANT	What the login is allowed to do	No

A blocklist is a bouncer at the door who only knows the names on his list. A permission is a locked door. The researcher's own fix says it: read-only for an AI agent has to be 'a security invariant (like a permission)', not a model instruction or a fragile keyword filter.

If my usp_ReadOnlyGuard had instead executed under a properly restricted security context, with SELECT access but no effective permissions to modify data or elevate privileges, my bypasses would have failed immediately. Not because the string looked wrong, but because the write was never permitted in the first place. End of story. The filter asks 'does this look dangerous?' The permission model asks 'is this allowed?' That is the distinction that matters.

Now the Part That Lands on Your SQL Server

Everything above was one misaligned string. The Copilot attack has a second half that does not need you to type anything at all.

SQL Copilot reads database instructions that live as extended properties -- database-wide instructions called CONSTITUTION.md, and object-level instructions called AGENTS.md. Copilot discovers them automatically and folds them into its context. They are added with sp_addextendedproperty, and -- this is the part that matters -- the permission to attach one can be lower than the permission of the person who later opens Copilot against that object.

So a user with ALTER on a table plants instructions. A DBA connected as sysadmin opens Copilot later and asks about that table. Copilot loads the planted instructions and runs them on the sysadmin connection. In the published demo, the end result is a db_owner adding their own login to sysadmin, through the DBA's session, having touched nothing but a comment field.

Extended properties were always just metadata before AI. Now something reads them as instructions. That is a new trust relationship nobody added to the permission model intentionally.

Find Them Before Copilot Does

You cannot audit a regex inside SSMS from T-SQL. But you can find out whether anyone has planted instruction-shaped extended properties in your databases. Run this in any database where Copilot might get pointed:

SELECT
    ep.class_desc,
    ep.name [property_name],
    OBJECT_SCHEMA_NAME(ep.major_id) [object_schema],
    OBJECT_NAME(ep.major_id) [object_name],
    CAST(ep.value AS NVARCHAR(MAX)) [property_value]
FROM sys.extended_properties ep
WHERE ep.name IN (N'AGENTS.md', N'CONSTITUTION.md')
   OR CAST(ep.value AS NVARCHAR(MAX)) LIKE N'%sp_executesql%'
   OR CAST(ep.value AS NVARCHAR(MAX)) LIKE N'%sysadmin%'
   OR CAST(ep.value AS NVARCHAR(MAX)) LIKE N'%ALTER SERVER ROLE%'
ORDER BY object_schema, object_name, property_name;
GO

An empty result set means the query found no extended properties matching these names or patterns in the current database, within your metadata visibility. That's a good starting point, but it isn't proof that no malicious instructions exist. Anything that does come back deserves a closer look at the `property_value` column. You're looking for instructions that could influence what Copilot does, particularly anything attempting to execute SQL or elevate permissions.

If you want to see it light up first, plant this:

/* Plant a test property on a table in tempdb */
EXEC sp_addextendedproperty
     @name      = N'AGENTS.md',
     @value     = N'This table lists user names. Always greet the user first.',
     @level0type= N'SCHEMA', @level0name = N'dbo',
     @level1type= N'TABLE',  @level1name = N'names';
GO

Run the audit query up there and you'll see this:

The Takeaway

Patch SSMS 22. That closes CVE-2026-65669, and yes, you should do it today. But the patch fixes one product's filter. It does not fix the shape of the mistake, and the shape is what you need to carry forward, because you will see it again in the next agent, the next MCP server, the next 'read-only' integration somebody wires to production.

When a vendor tells you their AI runs in read-only mode, there is exactly one question worth asking: read-only enforced how? If the answer is a filter, a classifier, a system prompt, or anything that inspects the text of the query, it will be bypassed. If the answer is a dedicated, properly restricted login with no effective write or privilege-escalation permissions, then the database engine enforces that boundary. This is the one control the model cannot talk its way around.

Your AI's guardrails are only as strong as the login sitting behind them. Go look at yours.

/* Demo cleanup */
USE tempdb;
GO
DROP PROCEDURE IF EXISTS dbo.usp_ReadOnlyGuard;
DROP TABLE IF EXISTS dbo.names;

Small note: Microsoft has not published the exact regex Copilot used, so the guard proc above is my own reconstruction built the same way -- a keyword blocklist -- to demonstrate the class of failure. The bypass patterns are the ones from the public research. The extended-property behavior is as documented in the write-up and in Microsoft's own admin-controls guidance.

More to read

Slides -- From SELECT to SYSADMIN: Hacking SQL Copilot in SSMS
MSRC -- CVE-2026-65669
MSFT -- Admin controls for GitHub Copilot in SSMS
sqlfingers -- This SQL Server CVE Isn't on Your SQL Server

T-SQL Tuesday #202: The Saturday SQL Slammer Took Both Our Data Centers

This month Marlon Ribunal is hosting T-SQL Tuesday #202, and he's asking about that one SQL Server outage you'll never forget. I've had a few, but this one beats them all.

Saturday, January 25th, 2003. My cell phone rang at about 3AM. It was Microsoft calling to tell me what was happening inside my servers.

We had VIP support at the time, which is why I heard it from Redmond before I heard it from my own building. I called our head of IT expecting to wake him -- but he was already on site with his Team, trying to work out what the hell was going on.

What Was Actually Happening

SQL Slammer. First detected on the internet at 05:30 GMT that morning, which put my phone call roughly about three hours into it.

It was a single 404-byte UDP packet aimed at port 1434, and it sent so many copies of itself that servers and routers buckled and network traffic slowed to a crawl. No payload, no user interaction. Just pure replication exploiting a buffer overflow in Microsoft SQL Server 2000 and MSDE database products. Because the worm lived entirely in memory, a restart wiped it, but an unpatched reboot invited it right back in.

The sheer math made it the fastest-spreading worm in history. It doubled its infected host count every 8.5 seconds, tearing through 75,000 victims in under ten minutes.

The tragedy? The patch had been live since July 24, 2002 (MS02-039). It had already been sitting on the shelf for six months on the morning the world woke up to it.

How It Got In

Our firewall held. Port 1434 was not open to the internet and nothing came through the perimeter.

It came in over the VPN. One of our senior developers had a connection open, doing legitimate work, the way he did most weekends. That connection was authenticated, sanctioned, and exactly what we had built the VPN to allow.

Once it was inside, there was nothing between hosts to slow it down. It started with one or two SQL Servers and then it had the entire network.

The Routers Went First

The sheer volume of UDP scan traffic was overwhelming, and the routers went down under it. Not degraded -- our network lead's words were that they had halted to a stop. We couldn't even log in to manage them.

That's a particular kind of bad. The equipment you would use to contain a network problem is on the far side of the network problem. Everything we would normally reach for was unreachable.

From where we sat, the systems went down one by one as the internal network collapsed around them -- until both sites were completely down.

Both Sites

Chicago was primary and New York was the DR site. Financial services, so Monday's market open was not a time that could be negotiated.

We lost both sites.

That is the part I have never forgotten. Geographic redundancy is built for a site going away -- fire, flood, power, a backhoe through a fiber run. It is not built for something that gets into the network and reaches both your primary and DR sites in the same instant. New York wasn't a fallback that weekend. It was a second casualty.

The Recovery

We shut everything down. The IT Team took the routers offline and disconnected the edge switches and distribution routers from the core, to give us a network quiet enough to work in.

Then we recovered from the servers outward. One at a time. More than 400 physical servers across the two sites.

There was no clever fix available. The worm was memory-resident, so the machine came back clean, and it would be reinfected in seconds if we put it back on a network that still had infected hosts. The only way through was to do them in order, keep the clean ones isolated from the dirty ones, and not lose track of which was which at any point during recovery.

The IT Team and the DBA Team worked it together, Saturday into Sunday into Monday. Full recovery by about 4AM Monday morning. We opened when the market did and nobody outside the building knew.

Getting the Servers Back Was the Easy Half

Recovering 400+ servers was the part with a clear finish line. You know when an operating system is back online. The harder problem was everything that had to be true before we could begin trading at Monday's open.

In many cases the state was simply gone. Trader permissions and account data had to be repopulated from backups so that we could account for open orders -- and open orders don't close just because your data center did.

Then came reconciliation with the exchanges, verifying current state before we could open clean. That is not just a database task. It is a full business process, and it had to be done end-to-end before recovery was complete.

The Ashtray

Sometime during that weekend the owner of our company came down to L2 and handed me an ashtray, so I could smoke while working the recovery. Twenty-three years later, that is still the detail I remember first.

What I Took From It

The patch had existed for six months. That is the sentence everyone quotes about Slammer, and it is true, but it was never the interesting part to me.

The interesting part is that our perimeter did its job and it did not matter. Port 1434 was closed to the internet, and the worm still came in over an approved VPN connection opened by one of our most senior developers doing legitimate work on a Saturday. Nobody did anything wrong.

That still happens, and it does not need a worm. I wrote about a SQL Server CVE on Wednesday that lives in SSMS rather than in the database engine. The vulnerable machine is the workstation somebody administers from, and your SQL Server patch inventory never looks at it -- for the same reason a developer's laptop wasn't on anyone's list in 2003.

More to Read

T-SQL Tuesday #202 Invitation -- That One SQL Server Outage You'll Never Forget
MSFT Security Bulletin MS02-039
F-Secure -- Worm:W32/Slammer
SQL Slammer
sqlfingers -- This SQL Server CVE Isn't on Your SQL Server

This SQL Server CVE Isn't on Your SQL Server

I'm in Italy right now, which is why I've been quiet for a couple of weeks. MSFT picked yesterday to ship the biggest Patch Tuesday on record. SecurityWeek counts 974 CVEs, and 62 of them land in Microsoft's SQL bucket. That's a lot of patching to sort through, but one of them caught my attention for a completely different reason.

CVE-2026-65669 has a CVSS score of 9.6, and MSFT titles it Microsoft SQL Server Elevation of Privilege Vulnerability. Reading that title, I would naturally expect to be looking for a vulnerable SQL Server build -- but I wouldn't be. The vulnerability is in SQL Copilot inside SSMS 22, which means the affected software could be sitting on a DBA's workstation rather than on a SQL Server at all.

That's where this one gets interesting.

Your SQL Server Can Be Fully Patched

Every step of my normal SQL Server patch process starts with a server. I pull the build, check the servicing path, determine what applies, prioritize it, schedule the work and eventually verify that the instance was patched. A vulnerability scanner working against the server estate is doing essentially the same thing from a different direction.

CVE-2026-65669 can completely escape that process because the affected product isn't necessarily installed on any of the servers I'm checking. The SQL Server itself can be current, the vulnerability scan can report it clean, and both statements can be accurate while a vulnerable copy of SSMS is sitting on a completely separate workstation used to administer the SQL Server(s).

The compliance report isn't wrong. It answered the questions we asked -- we just didn't ask about the machines running SSMS.

The Permissions Are the Bigger Story

According to Automox's analysis, an attacker can convince a user to submit specially crafted instructions to SQL Copilot, allowing the assistant to bypass its intended read-only restrictions and perform database activity using the permissions of the account already connected through SSMS. ZDI describes the same basic chain: get the user to submit specially crafted instructions to SQL Copilot, and the resulting database access occurs at that user's permission level.

That last part matters more to me than the 9.6 score. This vulnerability doesn't grant permissions. It uses the ones already on the connection. If somebody opens SSMS against production as db_owner, that's the authority sitting behind the session. If they connect with an account limited to the SELECT permissions actually required for their work, the potential impact is very different.

We've been talking about least privilege since long before anybody put Copilot in Management Studio. The difference now is that the person at the keyboard is no longer the only factor we have to consider when deciding how much authority an interactive connection should have.

The Patch Doesn't Fix the Permission Problem

Obviously, the affected copies of SSMS need to be updated. That's the immediate remediation for this vulnerability. What the patch doesn't change is the permission model behind those connections.

If DBAs and developers routinely connect to production with write permissions when they're only there to query data, those permissions will still exist after SSMS is patched. The CVE didn't create that exposure; it just gave us a very good reason to look at it more closely.

Find Out Who Is Connecting Through SSMS

You can't inventory the SSMS version installed on somebody's workstation from T-SQL, but SQL Server can tell you about the Management Studio sessions currently connected to it. Run this during normal working hours, because sys.dm_exec_sessions only tells you what is connected now:

SELECT
    s.login_name,
    s.program_name,
    s.host_name,
    COUNT_BIG(*) SessionCount,
    MIN(s.login_time) EarliestLogin
FROM sys.dm_exec_sessions s
WHERE s.is_user_process = 1
AND s.program_name LIKE N'%Management Studio%'
GROUP BY
    s.login_name,
    s.program_name,
    s.host_name
ORDER BY
    s.login_name,
    s.host_name;
GO

There is an important caveat here. program_name is supplied by the client, so this is useful inventory information, not something I would ever treat as a security control. What I want from the query is much simpler -- who is routinely pointing Management Studio at this SQL Server, and from which machines?

Those machines are now part of my SQL patching inventory.

Then Look at What Those Accounts Can Do

This is where I think the CVE becomes more useful than simply another item on the September patch list. Once I know which identities are being used for interactive SSMS connections to production, I want to know what authority those identities actually have.

And I don't mean just looking for db_owner. Effective access can come from server roles, database roles, direct grants, custom roles, Windows group membership and ownership. The useful question isn't whether a login belongs to one particular dangerous role. It's what can this identity actually do once it connects?

Don't Forget the Jump Box

The first place I'd look is the shared administrative server or jump box, if you have one. Those machines tend to sit in an awkward spot operationally. They're not SQL Servers, so the SQL Server patch inventory may not include them, and they're not normal end-user workstations either.

At the same time, they're often exactly where SSMS is installed, where production connections are saved, and where some of the most privileged database work in the environment takes place. If I'm looking for SSMS 22 installations that matter, I'm starting there.

What I'm Taking Away From This One

There are a lot of SQL-related CVEs to deal with this month, and the applicable SQL Server patches obviously still need to be evaluated, tested and deployed. But CVE-2026-65669 exposed something different in the way I normally think about SQL Server vulnerability management.

My process starts with the SQL Server. This vulnerability doesn't.

I could inventory every SQL Server, verify every build, install every applicable update and produce a completely accurate compliance report without ever examining the machine containing the vulnerable software. That's not really a patching failure. It's a scope failure.

So I'm adding another question to the process -- what tools are being used to administer SQL Server, where are they installed, and what permissions sit behind those connections?

Patch the SQL Servers and patch SSMS. Then take a look at the accounts people are using when they open SSMS against production. The CVE will eventually disappear from the vulnerability report. Those permissions won't.

More to Read

MSRC -- CVE-2026-65669
ZDI -- The September 2026 Security Update Review
Automox -- Patch Tuesday September 2026 CVE Analysis
SecurityWeek -- MSFT Patches Record 974 Vulnerabilities

You Have a Columnstore Index. But Is Your Data Actually in Columnstore?

MSSQLTips ran a good piece this month on when a columnstore index does not make sense -- the case where your queries pull back too many columns to benefit from columnar storage. Fair point, and a design question worth asking.

But there is another question I would ask first: is the columnstore index actually storing your data as columnstore?

Having a clustered columnstore index on a table does not mean every row in that table is sitting in a healthy compressed rowgroup. Depending on how the data was loaded, a significant number of rows can still be sitting in rowstore, or compressed into rowgroups far smaller than you expected. Before you decide columnstore was the wrong design, there is one DMV worth checking.

The number that matters is 1,048,576

A compressed rowgroup can hold up to 1,048,576 rows. That number gives you a useful benchmark when you start looking at rowgroup health. If your compressed rowgroups are consistently much smaller, or a significant number of rows have not been compressed at all, the next question is why. SQL Server gives you that answer. A rowgroup may still be sitting in the delta store, or it may have been compressed before it reached the maximum size. Those are different conditions, and the distinction matters when you decide what, if anything, needs to be fixed.

The delta store

Small inserts do not go directly into compressed columnstore format. They land in a delta rowgroup, which is a rowstore B-tree. For bulk loads, 102,400 rows is the important threshold: a sufficiently large batch can bypass the delta store and go directly into a compressed rowgroup, while smaller batches are inserted into the delta store.

That does not mean every small batch remains there indefinitely. As delta rowgroups fill, they close and the tuple mover can compress them. But the load pattern still matters. An application that continually inserts small batches can leave rows in OPEN or CLOSED delta rowgroups -- meaning some of the data behind that columnstore index is still physically stored as rowstore.

That is the first thing I want to know when I troubleshoot one.

Trimming

The other condition is a rowgroup that did get compressed, but closed before reaching 1,048,576 rows. SQL Server calls that trimming, and trim_reason_desc tells you why it happened.

A trimmed rowgroup is not automatically a problem. Some reasons are simply a consequence of how the data was loaded. Others point to memory pressure, dictionary limits, or maintenance activity. The important part is that you do not have to guess why the rowgroup is smaller than expected.

trim_reason_desc	What it means
NO_TRIM	The rowgroup reached the maximum row count.
BULKLOAD	The bulk-load batch size limited the row count
REORG	Forced compression as part of a REORG
DICTIONARY_SIZE	Dictionary size limited how many rows could be compressed together
MEMORY_LIMITATION	Available memory limited the size of the compressed rowgroup
RESIDUAL_ROW_GROUP	Remaining rows at the end of an index build
STATS_MISMATCH, SPILLOVER, AUTO_MERGE	Additional documented trim reasons -- see the DMV reference

This is where the DMV becomes more useful than simply counting rows. Two tables can both have undersized compressed rowgroups for completely different reasons. BULKLOAD points back to the size of the load. MEMORY_LIMITATION tells you SQL Server could not build the larger rowgroup with the memory available. DICTIONARY_SIZE tells you the dictionary itself became the limiting factor.

Same symptom -- smaller rowgroups -- but very different explanations. That is why I want the trim reason before I start trying to fix anything.

The query

This is the query I want first. Run it against the database and let the rowgroups tell you what condition you actually have:

SELECT OBJECT_NAME(rg.object_id) AS table_name,
       i.name AS index_name,
       rg.row_group_id,
       rg.state_desc,
       rg.total_rows,
       rg.deleted_rows,
       rg.trim_reason_desc,
       rg.transition_to_compressed_state_desc,
       rg.size_in_bytes
FROM sys.dm_db_column_store_row_group_physical_stats rg JOIN sys.indexes i
  ON  rg.object_id = i.object_id
  AND rg.index_id  = i.index_id
ORDER BY table_name, rg.row_group_id;

Four columns give me most of what I need. state_desc tells me whether the rowgroup is OPEN, CLOSED, or COMPRESSED. total_rows tells me how many rows made it into that rowgroup. trim_reason_desc tells me why a compressed rowgroup closed before reaching the maximum. And deleted_rows tells me how much logically deleted data is still sitting inside it.

Taken together, those values answer the questions I started with -- Are the rows actually compressed? If they are, what do the rowgroups look like? And if they are smaller than expected, why?

Watching it happen

The easiest way to see the difference is to give SQL Server the same data two different ways.

I am going to load 500,000 identical source rows into two clustered columnstore tables. The first gets the rows in ten 50,000-row batches. The second gets all 500,000 rows in one statement. Nothing else changes.

I am not measuring elapsed time or logical reads here. That is not the question. I just want to see where SQL Server puts the rows and what the rowgroup DMV says about them.

-- Scratch database only. Row counts are capped in the INSERTs.
USE tempdb;
GO

DROP TABLE IF EXISTS dbo.CCI_Trickle;
DROP TABLE IF EXISTS dbo.CCI_OneShot;
DROP TABLE IF EXISTS dbo.LoadStaging;
GO

CREATE TABLE dbo.LoadStaging
(
    OrderID INT NOT NULL,
    CustomerID INT NOT NULL,
    OrderDate DATE NOT NULL,
    OrderTotal DECIMAL(9,2) NOT NULL
);
GO

-- 500,000 staging rows, hard capped.
INSERT dbo.LoadStaging (OrderID, CustomerID, OrderDate, OrderTotal)
SELECT TOP (500000)
       ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
       ABS(CHECKSUM(NEWID())) % 5000,
       DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 900, '2024-01-01'),
       (ABS(CHECKSUM(NEWID())) % 100000) / 100.0
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
CROSS JOIN sys.all_objects c;
GO

Table 1 -- the trickle load

The first table gets its 500,000 rows in ten batches of 50,000. Each individual load is below the 102,400-row threshold for bypassing the delta store, so these rows enter through delta rowgroups rather than going directly into compressed columnstore rowgroups.

Notice what I am not assuming. Ten inserts do not automatically produce ten delta rowgroups. SQL Server can continue adding rows to an existing OPEN delta rowgroup until it fills. The DMV will show us how those 500,000 rows were actually organized.

CREATE TABLE dbo.CCI_Trickle
(
    OrderID INT NOT NULL,
    CustomerID INT NOT NULL,
    OrderDate DATE NOT NULL,
    OrderTotal DECIMAL(9,2) NOT NULL,
    INDEX cci_CCI_Trickle CLUSTERED COLUMNSTORE
);
GO

DECLARE @Batch INT = 0;

WHILE @Batch < 10
BEGIN
    INSERT dbo.CCI_Trickle (OrderID, CustomerID, OrderDate, OrderTotal)
    SELECT OrderID, CustomerID, OrderDate, OrderTotal
    FROM dbo.LoadStaging
    WHERE OrderID >  (@Batch * 50000)
      AND OrderID <= ((@Batch + 1) * 50000);

    SET @Batch = @Batch + 1;
END
GO

Table 2 -- the same rows, one statement

The second table gets exactly the same 500,000 rows, but this time they arrive in a single load. Because the batch is well above the 102,400-row threshold, SQL Server can bypass the delta store and build compressed rowgroups directly.

That does not mean I expect one 500,000-row compressed rowgroup. SQL Server still controls how the load is divided into rowgroups. What matters for this test is that the rows are eligible to go directly into compressed columnstore storage rather than entering through the delta store first.

CREATE TABLE dbo.CCI_OneShot
(
    OrderID INT NOT NULL,
    CustomerID INT NOT NULL,
    OrderDate DATE NOT NULL,
    OrderTotal DECIMAL(9,2) NOT NULL,
    INDEX cci_CCI_OneShot CLUSTERED COLUMNSTORE
);
GO

INSERT dbo.CCI_OneShot (OrderID, CustomerID, OrderDate, OrderTotal)
SELECT OrderID, CustomerID, OrderDate, OrderTotal
FROM dbo.LoadStaging;
GO

Now run the diagnostic against both tables. This is what mine returned:

There it is.

Both tables contain exactly 500,000 rows. The one-shot load produced a COMPRESSED rowgroup immediately. It is smaller than the 1,048,576-row maximum, and SQL Server tells us exactly why: BULKLOAD. The incoming batch contained 500,000 rows, so that batch determined the size of the compressed rowgroup.

The trickle-loaded table is a completely different story. All 500,000 rows are sitting in one OPEN delta rowgroup. The table has a clustered columnstore index, but at this moment every row we loaded into it is still physically stored in rowstore.

That is the distinction I was looking for. If all I knew was that both tables had clustered columnstore indexes and both contained 500,000 rows, I would be missing the most important difference between them. The DMV exposes it immediately.

Forcing the issue

At this point the trickle-loaded table has 500,000 rows sitting in an OPEN delta rowgroup. If I do not want to wait for SQL Server to close and compress that rowgroup on its own, I can force compression:

ALTER INDEX cci_CCI_Trickle ON dbo.CCI_Trickle
REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);
GO

Now run the DMV again. The important thing to watch is whether the rowgroup moves from OPEN to COMPRESSED, and what SQL Server records in trim_reason_desc.

Here is what mine returned:

The 500,000 trickle-loaded rows are now compressed. SQL Server created a new compressed rowgroup and records REORG as the trim reason, with REORG_FORCED as the transition to compressed state. That tells me exactly how this rowgroup got here. It did not fill naturally, and it was not compressed directly by the original load. I forced the OPEN delta rowgroup into compressed columnstore storage with REORGANIZE.

And now the two tables have arrived at essentially the same physical result by two very different paths. One went directly into a compressed rowgroup because the original load was large enough. The other spent its first life entirely in the delta store until I explicitly forced compression.

DROP TABLE IF EXISTS dbo.CCI_Trickle;
DROP TABLE IF EXISTS dbo.CCI_OneShot;
DROP TABLE IF EXISTS dbo.LoadStaging;
GO

What to do with the answer

What I see	What I look at next
OPEN or CLOSED delta rowgroups holding a lot of rows	Those rows are still in rowstore. Look at the load pattern and whether REORGANIZE ... COMPRESS_ALL_ROW_GROUPS belongs in maintenance.
Many COMPRESSED rowgroups trimmed by BULKLOAD	Look at the size of the incoming batches. Smaller bulk loads can produce smaller compressed rowgroups.
trim MEMORY_LIMITATION	The rowgroup was limited by available memory during compression. Investigate the memory conditions around the load or index operation.
trim DICTIONARY_SIZE	Dictionary size stopped the rowgroup from growing further. Look at the columns and data driving dictionary pressure.
A high number of deleted_rows	Logically deleted rows are still occupying the compressed rowgroup. Evaluate whether columnstore maintenance is warranted.

The reason I keep this query in the health check kit is that it separates conditions that look identical from the outside. Seeing a clustered columnstore index on a table tells me how the table was designed. It does not tell me where the rows are actually sitting.

Our two test tables made that pretty clear. Same 500,000 rows. Same table definition. Same clustered columnstore index. One load put all 500,000 rows directly into compressed columnstore storage. The other left all 500,000 sitting in an OPEN delta rowgroup as rowstore.

A columnstore index that is wrong for the workload needs a design conversation. A columnstore index whose rows are not getting compressed needs a different conversation entirely.

Before I blame columnstore, I want to know which conversation I am having.

More to Read

MSFT Learn: sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)
MSFT Learn: Columnstore indexes -- Data loading guidance
SQLCAT: Columnstore row group merge policy and index maintenance improvements
MSSQLTips: When a Columnstore Index Does Not Make Sense

CU8 Is Here. Your Monitoring Query Is the New Casualty.

Cumulative Update 8 for SQL Server 2025 landed on August 13th, build 17.0.4075.5. Eleven fixes. Three known issues. One of those known issues arrived with the July updates, and it is the kind that finds you at the worst possible moment, because the thing it breaks is the query you run when something is already going wrong.

Query sys.dm_exec_requests while a database is recovering and SQL Server may raise an access violation, write a stack dump, and terminate that session. Per KB5104822, this can happen during a RESTORE, during startup recovery, or before an availability group replica finishes coming online.

Here is the error log signature, as published by Microsoft in the KB:

The database '<DatabaseName>' is marked RESTORING and is in a state that does not allow recovery to be run.
***Stack Dump being sent to <SQLServerLogFolder>\SQLDump0215.txt
SqlDumpExceptionHandler: Process 77 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
*******************************************************************************

BEGIN STACK DUMP:
  07/30/26 07:37:23 spid 77

Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION
Access Violation occurred reading address 00000000000000F0
Input Buffer <size> bytes -
          <dbo.sp_YourMonitoringStoredProcedure>

Read that last line again. That is Microsoft's own placeholder in their own KB article. They named the input buffer sp_YourMonitoringStoredProcedure because they know exactly who is calling this DMV in a loop.

The Fix and the Bug Are the Same Code Change

This did not arrive out of nowhere. It arrived attached to something people asked for.

In the July CUs -- SQL Server 2025 CU7 and SQL Server 2022 CU26, both released July 16th -- Microsoft shipped a fix for monitoring queries that use sys.dm_exec_requests or sys.sysprocesses and intermittently fail against a secondary replica with error 976 or error 978. That is real annoying if you monitor a readable secondary, and it is a reasonable thing to fix.

KB5104822 describes the resulting problem plainly: "This issue occurs because of a change that causes internal in-memory structures to be referenced before they're fully initialized".

So the code change that stopped your monitoring query from erroring on a secondary is the same code change that can now crash it against a recovering database. You do not get to keep one and decline the other. Trace flag 4696 opts you out, and it opts you out of both.

Why This Lands Harder Than It Reads

A DMV that faults during database recovery sounds narrow. It is not, and here's why.

Look at the three windows Microsoft lists -- a RESTORE, startup recovery, and an AG replica coming online. Every one of those is a window where a DBA or a monitoring platform is actively watching. Nobody polls sys.dm_exec_requests harder than during a restore that is taking longer than expected, or a service restart, or a failover. The exposure is not spread evenly across your week. It is concentrated in exactly the minutes you are paying attention.

Then there is the part that made me sit up. Your patching window manufactures the condition. The restart the install requires puts the instance into startup recovery on the new build, and on an AG the replicas come online one at a time. A scheduled monitoring sweep that fires in that window is your first exposure, minutes after you introduced it.

Worth being clear on what this does and does not take down. Per KB5104822, the AV terminates the process, not the instance -- "SQL Server is terminating this process". Your instance is not going down. But a stack dump is not free. Dump generation costs I/O and time on a server that is, by definition, already busy recovering something.

Who Is Exposed

Version	Build	Released	Status
SQL Server 2025 CU8	17.0.4075.5	Aug 13, 2026	Known issue, open
SQL Server 2025 CU7	17.0.4065.4	Jul 16, 2026	Known issue, open
SQL Server 2022 CU26	16.0.4265.3	Jul 16, 2026	Known issue, open

Microsoft's position as of CU8 is that they are aware of the issue and investigating a fix. No fix build to point at yet, and CU8 shipping without one tells you it did not make the August train.

Find Out Where You Stand

Three questions, three answers. Start with the build, because everything else depends on it.

1. What am I actually running?

SELECT
    SERVERPROPERTY('ProductVersion') AS Build,
    SERVERPROPERTY('ProductUpdateLevel') AS CU,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('Edition') AS Edition;
GO

If Build comes back 17.0.4065.4 like mine, or 17.0.4075.5, or 16.0.4265.3, you are on an affected build.

2. Is anything in a recovering state right now?

SELECT
    d.name,
    d.state_desc,
    d.recovery_model_desc
FROM sys.databases d
WHERE d.state_desc <> 'ONLINE'
ORDER BY d.name;
GO

Empty result set is what you want. This is also the check worth running before you start a monitoring sweep, which I come back to below.

3. Is trace flag 4696 already on?

DBCC TRACESTATUS(4696, -1);
GO

Your Three Options

KB5104822 gives exactly three mitigations, and there is no free one in the set. Pick your poison deliberately.

Mitigation	What you get	What it costs
Trace flag 4696	Opts out of the code change. No AV.	Monitoring queries against sys.dm_exec_requests or sys.sysprocesses on secondary replicas can return errors 976 or 978 again, exactly as before July.
Avoid the DMV during recovery	Keeps the July fix. No trace flag.	Requires you to change your monitoring code, and it is a timing problem, not a settings problem.
Uninstall the update	Back to a known state.	You also give up every security fix in the July and August packages. For most shops this is not a real option.

My read: trace flag 4696 is the sane default for anyone whose estate has availability groups, log shipping, or regular restores -- which is most of us. A monitoring query that returns an error is a nuisance you can see and log. A session that gets terminated mid-sweep during a failover is a nuisance you find out about later, from the dump directory.

-- Global, effective immediately
DBCC TRACEON (4696, -1);

-- Or make it survive a restart, as a startup parameter:
-- -T4696

Test it before production. You are trading one documented behavior for another documented behavior, and which one hurts you less depends on whether your secondaries are readable.

A Guard For Your Monitoring Job

If you would rather keep the July fix and skip the trace flag, the shape of the mitigation is to not run the DMV query while anything is recovering. Something like this:

/* UNVERIFIED -- pattern only. I have not reproduced the AV,
   and this has not been tested against an affected build. */

IF EXISTS
(
    SELECT 1
    FROM sys.databases
    WHERE state_desc IN ('RECOVERING', 'RECOVERY_PENDING', 'RESTORING')
)
BEGIN
    /* Skip the sweep this cycle and say so, loudly */
    RAISERROR ('Monitoring sweep skipped -- database(s) in a recovering state.', 10, 1) WITH LOG;
END
ELSE
BEGIN
    SELECT
        r.session_id,
        r.status,
        r.command,
        r.wait_type,
        r.wait_time,
        r.blocking_session_id
    FROM sys.dm_exec_requests r
    WHERE r.session_id > 50;
END;
GO

Be honest with yourself about what that buys you. It is a check-then-act, and the state can change between the check and the query. It narrows the window, but it doesn't close it. And it means your monitoring goes quiet during precisely the events you most want monitored, which is its own kind of failure. I am showing it because it is the shape of Microsoft's second mitigation, not because I think it is a good outcome.

And While You Are In There

CU8 carries two other known issues and one of them is an old friend.

SESSION_CONTEXT in parallel plans. Still there. Same text as it has carried for years -- incorrect results or AV dump files in parallel query plans, because of how SESSION_CONTEXT interacts with parallel execution threads when the session is reset for reuse. I wrote about it in March, and again when CU6 shipped without fixing it. Trace flag 11042 remains the workaround. That is coming up on five years.

Bottom Line

None of this is an argument against patching. The July packages carried security content and you need it. It is an argument for reading the Known Issues section before you schedule the window, and for a specific ordering on your patch nights.

If you patch an availability group this month, the replicas coming back online are the exact condition described in the KB. Decide what your monitoring is doing during that window before you start, not after you find the dump files. Set the trace flag, or pause the sweep -- or at the very minimum, just know the risk is there and go look at the log afterward.

The uncomfortable part of this one is not that a DMV can fault. It is that the fault arrived as a fix, shipped in a package everybody had a good reason to install, and lands on the tool you reach for when something is already broken.

More to Read

MSFT -- KB5104822, Cumulative Update 8 for SQL Server 2025
MSFT -- KB5096981, Cumulative Update 7 for SQL Server 2025
MSFT -- KB5093420, Cumulative Update 26 for SQL Server 2022
MSFT Tech Community -- Cumulative Update 8 for SQL Server 2025 RTM
MSFT -- SESSION_CONTEXT, Known Issues
sqlfingers -- CU6 Is Here. SESSION_CONTEXT Is Still Broken in Parallel
sqlfingers -- SESSION_CONTEXT: Three Years, Two Bugs, One Workaround

Brent and I Wrote About Temp Tables Yesterday. We Found Different Things.

Brent Ozar and I both published temp table posts yesterday for T-SQL Tuesday #201. We both built a lab. We both argued from logical reads. We both ended up with the temp table winning.

And we found two completely different reasons why.

That is more interesting than either post on its own, so let me put them side by side.

What Brent Found

His post is When CTEs Don't Work, Try Temp Tables, and his rule is stated up front: "Default to CTEs. When SQL Server gets that process wrong, switch to temp tables."

His demo runs against the big Stack Overflow database on SQL Server 2025. Find the most popular value in Users.Location, then return the top 250 people in that location by Reputation. He builds indexes on both columns first, so the engine has what it needs.

The CTE version reads 478,982 pages -- more pages than exist in the entire clustered index of the table it is querying.

The cause is an estimate. SQL Server works out that it is looking for exactly one location, and gets that part right. What it cannot work out is which one, or that the one it is looking for is the most heavily populated value in the column rather than a typical one. So it estimates 14 rows, finds vastly more, and pays for the difference in key lookups. Brent counted 113,399 of them.

Swap the CTE for a temp table and the reads drop by two thirds.

His explanation of why is the part I want to sit with. When the second statement compiled, SQL Server had no statistics on the temp table, so it built them. Those statistics told it the one row in there with Location = India. It then looked India up in the statistics on Users.Location, recognized it as a heavily populated value, and compiled a plan suited to exactly that.

Brent's temp table won on statistics. They told the Optimizer which value it was really dealing with, and it chose a different plan shape on the strength of that.

What I Found

My demo was a dashboard-shaped query -- top 100 customers by trailing-twelve-month spend, shown next to the population average and maximum, filtered to those above the average. The aggregate is referenced four separate times.

The CTE version read 26,328 pages against tblOrders. The temp table version read 6,582. That ratio is 4.0000, and the plan says why: four separate Index Scan operators against the base table in one plan, against one in the other. A CTE is a named subquery, and the Optimizer expands its definition inline at every reference.

Then I checked the estimates, expecting them to explain the win.

My demo	Estimated	Actual	Miss
CTE version (nested loops join)	24,997.4	24,994	0.01%
Temp table version (scan of #CustomerTotals)	15,000	24,994	40% low

Backwards. The CTE version had a nearly perfect estimate. The temp table version came in 40 percent low -- 15,000 being exactly 30 percent of the 50,000 rows in the temp table, which is the fallback SQL Server reaches for on an inequality when it cannot know the value at compile time. In this case it couldn't, because I was filtering on a local variable.

That handicap was mine, not the temp table's -- write the filter as a subquery against the temp table instead of a variable and the guess goes away. I left it in because it did not matter.

My temp table won by 8x on total logical reads while carrying the worse estimate. It did not win by knowing more. It won by doing less.

Two Different Failure Modes

Which puts the two demos side by side like this.

	Brent's case	My case
What the CTE cost	A bad estimate, paid for in key lookups	The same aggregate evaluated four times
Estimate quality with the temp table	Better	Worse
Why the temp table won	New statistics, new plan, fitted to the real value	One pass instead of four, and smaller plans
Changes with your data distribution?	Yes	Not in this demo

I went into my lab expecting to find his answer. Reading his post showed me what I had found instead.

One of These You Can See Coming

Brent's opening argument for CTEs is that they let SQL Server pick a processing order suited to your current data distribution, on your current version. By his own account, the engine assumes a typically-sized location and gets the largest one in the table, so the gap between estimate and reality is a property of the data rather than of the query. Which is why his rule fits it so well -- run it, read the plan, find the first place estimates and actuals diverge badly, then act.

Mine is not data-dependent. Four references to a CTE means the Optimizer expands it four times regardless of what is in the table. Reload the data, change the distribution, rebuild the statistics -- still four, unless the Optimizer decides to spool, which it did not do here. There is nothing to discover, because the cost is a property of how the query is written rather than of what it is querying.

Which means the two cases want different habits.

Brent's case, you measure. It shows up once the plan runs and the actuals come back, and his advice to read right to left looking for the first big divergence is exactly how you catch it.

Mine, you count. Open the query, count the references to the CTE. If the answer is more than one, you already know the work is being repeated, before you execute anything. No plan required.

His is the harder of the two to catch, and the plan-reading habit he teaches is how you catch it. Mine announces itself in the query text -- if you're lucky.

Where We Land

Not far apart. Brent's position is default to CTEs and switch when SQL Server gets it wrong. Mine was that a temp table is a foe when it is a reflex and a friend when it is a decision. I'd almost say those are the same instinct wearing different clothes.

He is careful to say his is one case and not a rule. He points out that you are effectively getting an OPTION (RECOMPILE) out of the deal, and that the fresh plan he benefited from does not always happen, thanks to temp table caching.

Two cases, two mechanisms, both measured, published the same day, neither of us having seen the other's post first.

Jeff asked whether temp tables were friend or foe. Two of us answered yes, for entirely different reasons. That may be the most honest answer the question has.

More to Read

Brent Ozar: When CTEs Don't Work, Try Temp Tables -- #TSQL2sday
Jeff Taylor: T-SQL Tuesday #201 Invitation -- Temp Tables, Friend or Foe?
sqlfingers: Temp Tables -- Foe When It's a Reflex, Friend When It's a Decision
Brent Ozar: Paul White Explains Temp Table Caching 3 Ways