Friday, April 24, 2026

Deadlock Capture: No Polling, No New XE Session, Just system_health

Back in September 2024 I wrote about why the SQL Server 1205 error wasn't hitting the Error Log, and how to flip is_event_logged = 1 so it would. I ended that post saying I'd follow up with the deadlock capture itself. This is that post. Two years late. But worth the wait... 😉

The objective is simple. Capture every deadlock into a DBA table, email a notification when one occurs — and do it all without adding anything heavy to the server. No dedicated Extended Events session to maintain. No Agent job polling the server every X minutes. Nothing runs until a deadlock actually happens. Very lightweight.

What 'non-invasive' means here

The final deployment has exactly four moving parts:

ObjectPurpose
dbo.DeadlockEventsLanding table for captured deadlocks
dbo.usp_CaptureDeadlockReads new deadlocks, logs them, emails the notification
DBA_CaptureDeadlockUnscheduled Agent job that calls the proc
Deadlock_AlertAgent alert on message 1205 that fires the job

No new Extended Events session. We piggyback on system_health, the default XE session that has been running on every SQL Server instance since 2012 and already captures xml_deadlock_report. If you're unfamiliar, go get familiar. It is there right now, on every server you manage, collecting system data that you can use to help troubleshoot performance issues. How convenient is that?! I'm serious. If you don't use it, start. I cannot count the number of questions it has answered for me.

The chain of events: SQL Server's deadlock monitor picks a victim and raises error 1205. Because we ran sp_altermessage 1205 WITH_LOG in the 2024 setup, 1205 now hits the Error Log. The Agent alert fires on message 1205 and starts the DBA_CaptureDeadlock job. The job runs usp_CaptureDeadlock, which reads the system_health event file for any new deadlock events, parses victim and survivor details, logs them, and emails a notification. Purely event-driven. Nothing is running every X minutes. No deadlocks means nothing is running.

Prerequisites

Database Mail must be configured with a profile. A DBA database exists to hold the plumbing, and 1205 must be logged. That last one was the main reason behind my 2024 post -- if is_event_logged = 0 on message 1205, the Agent alert can never fire. Step 1 of the install script below checks this and fixes it if needed.

The install script

1. Confirm 1205 is logged.

IF NOT EXISTS (
    SELECT 1 FROM sys.messages
    WHERE message_id = 1205 AND language_id = 1033 AND is_event_logged = 1
)
BEGIN
    EXEC master.sys.sp_altermessage
         @message_id      = 1205,
         @parameter       = 'WITH_LOG',
         @parameter_value = 'true';
    PRINT 'Error 1205 now logged (WITH_LOG).';
END
ELSE
    PRINT 'Error 1205 already logged.  No change needed.';
GO

2. Create the landing table.

Columns for the parsed victim and survivor details (SPID, login, host, app, statement, lock mode), the full deadlock graph as XML, and a timestamp. Nothing fancy.

USE [DBA];
GO

IF OBJECT_ID('dbo.DeadlockEvents','U') IS NOT NULL DROP TABLE dbo.DeadlockEvents;
GO
CREATE TABLE dbo.DeadlockEvents (
    DeadlockID         INT IDENTITY(1,1) NOT NULL,
    EventTime          DATETIME2(3)      NOT NULL,
    DatabaseName       NVARCHAR(128)     NULL,
    VictimSPID         INT               NULL,
    VictimLogin        NVARCHAR(128)     NULL,
    VictimHost         NVARCHAR(128)     NULL,
    VictimApp          NVARCHAR(256)     NULL,
    VictimStatement    NVARCHAR(MAX)     NULL,
    VictimLockMode     NVARCHAR(20)      NULL,
    VictimWaitResource NVARCHAR(256)     NULL,
    SurvivorSPID       INT               NULL,
    SurvivorLogin      NVARCHAR(128)     NULL,
    SurvivorHost       NVARCHAR(128)     NULL,
    SurvivorApp        NVARCHAR(256)     NULL,
    SurvivorStatement  NVARCHAR(MAX)     NULL,
    SurvivorLockMode   NVARCHAR(20)      NULL,
    DeadlockGraph      XML               NOT NULL,
    DateRecorded       DATETIME2(3)      NOT NULL
         CONSTRAINT df_DeadlockEvents_DateRecorded DEFAULT (SYSDATETIME()),
    CONSTRAINT pkc_DeadlockEvents_DeadlockID PRIMARY KEY CLUSTERED (DeadlockID)
);
GO

3. Create the capture procedure.

This is where the work happens. The proc reads system_health for any xml_deadlock_report event newer than the most recent row in our table, parses victim and survivor from the XML, inserts new rows, and emails a notification. We use the latest EventTime already in the table as the cutoff for what's new. Simple Sally. Nothing else to maintain.

The victim is identified through the deadlock's victim-list/victimProcess/@id node.

CREATE OR ALTER PROCEDURE dbo.usp_CaptureDeadlock
AS
SET NOCOUNT ON;
/*
Reads system_health's event_file target for xml_deadlock_report events newer
than the most recent row in dbo.DeadlockEvents.  Logs them, emails a summary.

Called by DBA_CaptureDeadlock job, which is fired by Deadlock_Alert on error 1205.
*/
BEGIN
    -- Brief pause so system_health can flush the event to its file target.
    WAITFOR DELAY '00:00:05';

    DECLARE
        @LastEventTime DATETIME2(3),
        @Subject       NVARCHAR(255),
        @Body          NVARCHAR(MAX),
        @FrequencyLine NVARCHAR(200),
        @CountLastHour INT,
        @CountToday    INT;

    SELECT @LastEventTime = ISNULL(MAX(EventTime), '19000101')
    FROM dbo.DeadlockEvents;

    /* Pull deadlock events from the system_health file target.  The
       'system_health*.xel' pattern resolves relative to the default log
       directory -- no path hardcoding, portable across servers. */
    ;WITH FileDeadlocks AS (
        SELECT CAST(event_data AS XML) AS EventXML
        FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
        WHERE object_name = 'xml_deadlock_report'
    ),
    Parsed AS (
        SELECT
            DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()),
                    EventXML.value('(/event/@timestamp)[1]','DATETIME2(3)')) AS EventTime,
            EventXML AS FullEvent,
            EventXML.value(
                '(/event/data[@name="xml_report"]/value/deadlock/victim-list/victimProcess/@id)[1]',
                'NVARCHAR(50)') AS VictimID
        FROM FileDeadlocks
    )
    INSERT dbo.DeadlockEvents (
        EventTime, DatabaseName,
        VictimSPID,   VictimLogin,   VictimHost,   VictimApp,   VictimStatement,   VictimLockMode, VictimWaitResource,
        SurvivorSPID, SurvivorLogin, SurvivorHost, SurvivorApp, SurvivorStatement, SurvivorLockMode,
        DeadlockGraph
    )
    SELECT
        p.EventTime,
        v.n.value('@currentdbname','NVARCHAR(128)'),
        v.n.value('@spid','INT'),
        v.n.value('@loginname','NVARCHAR(128)'),
        v.n.value('@hostname','NVARCHAR(128)'),
        v.n.value('@clientapp','NVARCHAR(256)'),
        v.n.value('(inputbuf)[1]','NVARCHAR(MAX)'),
        v.n.value('@lockMode','NVARCHAR(20)'),
        v.n.value('@waitresource','NVARCHAR(256)'),
        s.n.value('@spid','INT'),
        s.n.value('@loginname','NVARCHAR(128)'),
        s.n.value('@hostname','NVARCHAR(128)'),
        s.n.value('@clientapp','NVARCHAR(256)'),
        s.n.value('(inputbuf)[1]','NVARCHAR(MAX)'),
        s.n.value('@lockMode','NVARCHAR(20)'),
        p.FullEvent
    FROM Parsed p
    CROSS APPLY p.FullEvent.nodes(
        '/event/data[@name="xml_report"]/value/deadlock/process-list/process[@id=sql:column("p.VictimID")]'
    ) v(n)
    CROSS APPLY p.FullEvent.nodes(
        '/event/data[@name="xml_report"]/value/deadlock/process-list/process[not(@id=sql:column("p.VictimID"))]'
    ) s(n)
    WHERE p.EventTime > @LastEventTime;

    IF @@ROWCOUNT = 0 RETURN;

    /* Frequency context -- tells the reader whether this is a one-off
       or part of a larger pattern, without opening the table. */
    SELECT
        @CountLastHour = SUM(CASE WHEN EventTime >= DATEADD(HOUR, -1, SYSDATETIME()) THEN 1 ELSE 0 END),
        @CountToday    = SUM(CASE WHEN EventTime >= CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME2(3)) THEN 1 ELSE 0 END)
    FROM dbo.DeadlockEvents;

    SET @FrequencyLine =
          CAST(@CountLastHour AS NVARCHAR(10)) + ' in the last hour, '
        + CAST(@CountToday    AS NVARCHAR(10)) + ' today.';

    SET @Body =
        '<html><body style="font-family:Verdana;font-size:11px;color:#000080;">'
      + '<h3>Deadlock captured on ' + @@SERVERNAME + '</h3>'
      + '<p style="color:#800000;"><b>Frequency:</b> ' + @FrequencyLine + '</p>';

    SELECT @Body = @Body
        + '<p><b>Event time:</b> ' + CONVERT(NVARCHAR(30), EventTime, 120) + '<br/>'
        + '<b>Database:</b> '   + ISNULL(DatabaseName,'-') + '<br/>'
        + '<b>Victim SPID:</b> ' + ISNULL(CAST(VictimSPID AS NVARCHAR(10)),'-')
        + ' (' + ISNULL(VictimLogin,'-') + ')<br/>'
        + '<b>Victim statement:</b><br/>'
        + '<pre style="font-family:Consolas;font-size:11px;">'
            + ISNULL(VictimStatement,'-') + '</pre>'
        + '<b>Survivor SPID:</b> ' + ISNULL(CAST(SurvivorSPID AS NVARCHAR(10)),'-')
        + ' (' + ISNULL(SurvivorLogin,'-') + ')</p>'
    FROM dbo.DeadlockEvents
    WHERE EventTime > @LastEventTime
    ORDER BY EventTime DESC;

    SET @Body = @Body + '<p>Full graph in <b>DBA.dbo.DeadlockEvents</b>.</p></body></html>';

    SET @Subject = @@SERVERNAME + ' - Deadlock captured';

    EXEC msdb.dbo.sp_send_dbmail
         @profile_name = 'SQLMailProfile',           -- <<< update
         @recipients   = 'DBATeam@YourCompany.com',  -- <<< update
         @subject      = @Subject,
         @body         = @Body,
         @body_format  = 'HTML',
         @importance   = 'High';
END

SET NOCOUNT OFF;

GO

4. Create the unscheduled Agent job.

It does nothing on its own. No schedule. It runs only when fired by the 1205 alert in the next step.

USE [msdb];
GO
IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = 'DBA_CaptureDeadlock')
    EXEC msdb.dbo.sp_delete_job @job_name = N'DBA_CaptureDeadlock';
GO

DECLARE @jobId BINARY(16);
EXEC msdb.dbo.sp_add_job 
     @job_name         = N'DBA_CaptureDeadlock',
     @enabled          = 0,
     @description      = N'Fired by Deadlock_Alert.  Reads system_health for new xml_deadlock_report events, logs to DBA.dbo.DeadlockEvents, emails a summary.  Not scheduled.',
     @category_name    = N'[Uncategorized (Local)]',
     @owner_login_name = N'sa',
     @job_id           = @jobId OUTPUT;

DECLARE @Log_Path VARCHAR(255)
SELECT @Log_Path =REPLACE(convert(varchar(1000),SERVERPROPERTY('ErrorLogFileName')),'\ERRORLOG','\')
SET @Log_Path = @Log_Path + 'DBA_CaptureDeadlock_$(ESCAPE_SQUOTE(STRTDT)).txt'

EXEC msdb.dbo.sp_add_jobstep 
     @job_id            = @jobId,
     @step_name         = N'Capture',
     @step_id           = 1,
     @subsystem         = N'TSQL',
     @command           = N'EXEC dbo.usp_CaptureDeadlock;',
     @database_name     = N'DBA',
     @output_file_name = @Log_Path,
     @on_success_action = 1,
     @on_fail_action    = 2;

EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)';
GO

5. Create the alert on message 1205.

SQL Server only allows one alert per message_id, so the script first clears out any pre-existing alert on 1205 -- whatever name it goes by -- before creating this one.

DECLARE @ExistingAlert NVARCHAR(128);
SELECT @ExistingAlert = name FROM msdb.dbo.sysalerts WHERE message_id = 1205;

IF @ExistingAlert IS NOT NULL
    EXEC msdb.dbo.sp_delete_alert @name = @ExistingAlert;
GO

EXEC msdb.dbo.sp_add_alert
     @name                         = N'Deadlock_Alert',
     @message_id                   = 1205,
     @severity                     = 0,
     @enabled                      = 0,
     @delay_between_responses      = 0,
     @include_event_description_in = 0,
     @job_name                     = N'DBA_CaptureDeadlock';
GO

6. Enable the alert and job.

EXEC msdb.dbo.sp_update_job   @job_name = N'DBA_CaptureDeadlock', @enabled = 1;
EXEC msdb.dbo.sp_update_alert @name     = N'Deadlock_Alert',     @enabled = 1;
GO

One gotcha worth knowing: file target, not ring buffer

The canonical Microsoft example for reading deadlocks from system_health uses the ring_buffer target. It looks clean, it's published in Microsoft's own docs, but it does not work reliably on SQL Server 2022. On my 2022 build, a query against system_health's ring_buffer target returns no rows at all for xml_deadlock_report events, but the same events are visible immediately in the file target. I tested and retested, and tested again.

This is a known issue with how the ring_buffer target serializes events. Jonathan Kehayias has a memorably-titled post about it: Why I hate the ring_buffer target in Extended Events. In short, use the file target. The proc above reads system_health*.xel directly through sys.fn_xe_file_target_read_file, and the file name pattern is resolved relative to the default log directory. No path hardcoding so it is easily usable across servers.

Test it

Create a tiny table, then run two sessions that update its rows in opposite order with HOLDLOCK. One will be chosen as the victim and killed. The alert fires, the job runs, the email notification is sent.

USE DBA;
IF OBJECT_ID('dbo.DeadlockTest','U') IS NOT NULL 
DROP TABLE dbo.DeadlockTest;
CREATE TABLE dbo.DeadlockTest (ID INT PRIMARY KEY, Val INT);
INSERT INTO dbo.DeadlockTest (ID, Val) VALUES (1, 0), (2, 0);
GO

Session 1 (first query window):

USE DBA;
BEGIN TRAN;
UPDATE dbo.DeadlockTest WITH (HOLDLOCK) SET Val = 1 WHERE ID = 1;
WAITFOR DELAY '00:00:05';
UPDATE dbo.DeadlockTest SET Val = 1 WHERE ID = 2;
COMMIT;

Session 2 (second query window, kicked off within the 5-second window):

USE DBA;
BEGIN TRAN;
UPDATE dbo.DeadlockTest WITH (HOLDLOCK) SET Val = 2 WHERE ID = 2;
WAITFOR DELAY '00:00:05';
UPDATE dbo.DeadlockTest SET Val = 2 WHERE ID = 1;
COMMIT;

One session dies with the familiar 1205:

Msg 1205, Level 13, State 51, Line 4
Transaction (Process ID 79) was deadlocked on lock resources with another process
and has been chosen as the deadlock victim. Rerun the transaction.

Verify

Confirm the alert fired, the job ran, and the row landed.

-- Did the alert fire?
SELECT name, last_occurrence_date, last_occurrence_time, occurrence_count
FROM msdb.dbo.sysalerts
WHERE name = 'Deadlock_Alert';

-- Did the job run?
SELECT TOP 5 run_date, run_time, run_status, message
FROM msdb.dbo.sysjobhistory h JOIN msdb.dbo.sysjobs j 
  ON h.job_id = j.job_id
WHERE j.name = 'DBA_CaptureDeadlock'
ORDER BY h.run_date DESC, h.run_time DESC;

-- And most importantly -- did we capture it?
SELECT TOP 5 *
FROM DBA.dbo.DeadlockEvents
ORDER BY EventTime DESC;

What the email looks like

Purposely minimal. Just enough for triage at a glance, with a frequency line to signal whether this is a one-off or a pattern. And the full deadlock graph is in the table with details for your review.

More to Read

Where is the 1205 error for the SQL Server Deadlock?!! -- the prerequisite post. 1205 has to be logged for the alert to fire.
SQL Server Deadlock Notifications -- an older post of mine using Service Broker and Event Notifications. Still valid, different architecture. Use whichever fits your environment.
MSSQLSERVER_1205 (Microsoft Learn) -- the official reference on error 1205.
Use the system_health session (Microsoft Learn) -- the default XE session we piggyback on.

Thursday, April 23, 2026

Four Quick Wins for Your SQL Server Host

Four Host Settings Every Dedicated SQL Server Should Get Right

Power Plan. Max Server Memory. Instant File Initialization. Lock Pages in Memory.

Four host-level settings. All documented. All decades old. All considered standard SQL Server best practices for production environments to maximize performance and stability. Yet I still find them missing again and again.

Here they are in the order that I check them.

1. Power Plan: Balanced

Windows ships with the Balanced power plan as the default. On a dedicated SQL Server host, that is wrong. Balanced throttles CPU frequency down when load is low and ramps it back up under demand. The ramp is not instant. On bursty SQL Server workloads, cores can run at reduced clock speeds even when queries are waiting for them.

The symptom in the wait stats is elevated SOS_SCHEDULER_YIELD and high signal wait percentages that do not match the actual workload intensity. You look at the numbers and think you have a CPU capacity problem? No. You just have a configuration problem.

This is not a theoretical impact. Glenn Berry at SQLskills has measured 20-25% performance differences between Balanced and High Performance on SQL Server workloads, and the effect persists across every Windows Server release since 2008.

Check it from an elevated command prompt on the server using powercfg:

If it returns Balanced, change it in Control Panel \ Power Options, like you see here. The change is immediate, no reboot required:

2. Max Server Memory set too low

Max Server Memory caps how much RAM SQL Server will use for its buffer pool and related caches. Set too low, SQL Server cannot cache as much data as the host has RAM to support, and you pay for it in disk reads that should have been memory hits.

The instance I was looking at had 64 GB of physical RAM on a dedicated SQL Server box. Max Server Memory was set to 38 GB. That is 26 GB of RAM sitting idle that SQL Server wasn't able to touch.

Check the current setting:

SELECT name, value_in_use 
FROM sys.configurations 
WHERE name IN ('max server memory (MB)', 'min server memory (MB)');

SELECT 
    physical_memory_kb / 1024 / 1024 AS PhysicalMemoryGB,
    committed_kb / 1024 / 1024 AS SQLCommittedGB,
    committed_target_kb / 1024 / 1024 AS SQLTargetGB
FROM sys.dm_os_sys_info;

Microsoft's current guidance is to set Max Server Memory to approximately 75% of physical RAM on a dedicated host, leaving the remainder for the OS and everything else. Brent Ozar covered this change in September 2025 — Microsoft's own installer now sets 75% by default when you pick the recommended memory option. On a 64 GB box, that lands around 48 GB. Brent's simpler rule of thumb is to leave 4 GB or 10% free, whichever is larger, and I tend to lean closer to that on boxes with lots of RAM. Either way, 38 GB allocation to a dedicated host with 64 GB physical is a little low.

Adjust it:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 58880;  -- ~57.5 GB
RECONFIGURE;

The change is immediate. No restart required.

3. Instant File Initialization (IFI)

When SQL Server grows a data file, Windows by default writes zeros across every byte of the newly allocated space before SQL Server can use it. A 1 GB data file growth means writing 1 GB of zeros to disk first. On slow storage, that can be painful.

Instant File Initialization skips the zero-writing step. The space is marked as allocated in the filesystem metadata and SQL Server uses it immediately. Growth is near-instant regardless of size.

IFI applies to data files only. Transaction log files always zero-initialize, with one narrow exception introduced in SQL Server 2022: log autogrowth events up to 64 MB can now benefit from IFI. For all practical purposes, treat IFI as 'speeds up data file growth.'

Check whether it is enabled:

SELECT instant_file_initialization_enabled 
FROM sys.dm_server_services 
WHERE servicename LIKE 'SQL Server (%';

If it returns N, the SQL Server service account does not have the Perform Volume Maintenance Tasks privilege.

To grant it, open Run and type secpol.msc to open Local Security Policy. Navigate to Local Policies \ User Rights Assignment, double-click 'Perform volume maintenance tasks,' and add the SQL Server service account. This will only take effect after SQL Server is restarted -- so you've got to plan this one and communicate it. Microsoft's instructions are here.

IFI pairs directly with oversized filegrowth increments. A data file set to grow by 1024MB without IFI stalls every growth event while Windows zeroes out the full gigabyte. The same file with IFI enabled grows in milliseconds. If you inherit an instance with large data file growth settings and have not enabled IFI, fixing IFI first makes the existing growth settings tolerable while you work on right-sizing the data files.

4. Lock Pages in Memory (LPIM)

Windows treats SQL Server's memory allocations like any other process. Under memory pressure, the OS can page portions of SQL Server's working set out to the pagefile. When SQL Server later needs those pages, it reads them back from disk. For a database engine whose performance model is built on keeping hot data in RAM, this can be catastrophic.

Lock Pages in Memory prevents that. With LPIM granted, SQL Server allocates its buffer pool using the AWE API and those pages are locked in physical RAM. Windows cannot page them out.

The smoking-gun symptom of not having LPIM, when it bites, is 'A significant part of sql server process memory has been paged out' in the SQL Server error log. It often shows up alongside sudden Page Life Expectancy cliffs that do not correspond to any workload change.

Check it:

SELECT sql_memory_model, sql_memory_model_desc 
FROM sys.dm_os_sys_info;

If sql_memory_model_desc returns CONVENTIONAL, then LPIM is not in effect. If it says LOCK_PAGES, then it is.

We enable this policy just like IFI: Run, type secpol.msc, navigate to Local Policies \ User Rights Assignment, look for 'Lock pages in memory', and add in the SQL Server service account. Microsoft's instructions are here.

LPIM also requires a SQL Server service restart. Pair it with the IFI change and you'll get them both with one restart.

One honest caveat: LPIM is a little more opinionated than the other three. On a dedicated SQL Server box with Max Server Memory set correctly, it is the right call. If a server hosts multiple applications beyond just SQL Server, or faces extreme memory pressure, it could starve the OS and cause instability. It is designed for dedicated database servers to prevent Windows from paging RAM to disk, but it can hinder performance if not paired with the appropriate maximum server memory limit. This is important.

LPIM is crucial on physical servers, but often debated or said to be 'unnecessary' for VMs, where the hypervisor manages memory pressure. VM or not, it is a setting that I include in my defaults.

The Pattern

Every one of these four settings are documented, well-known, and decades old. None of them are obscure. Yet I find them wrong more often than I find them right.

If you are running a health check on a new instance, check all four. Always.

And if you are looking at performance data that does not quite make sense — CPU pressure that feels out of proportion to the workload, I/O that seems slow against the storage tier, or memory that seems tight on a box with headroom — check these four before you start tuning queries. Host-level misconfigurations can often look like query-level symptoms until you rule them out.

More to read

Glenn Berry — Windows Power Plan Effects on Newer Intel Processors
Microsoft Learn — Server Memory Configuration Options
Brent Ozar — Microsoft Now Recommends You Set Max Memory to 75%
Brent Ozar — Instant File Initialization
Microsoft Learn — Enable the Lock Pages in Memory Option (Windows)

Friday, April 17, 2026

Patch Tuesday: SQL Server 2022 Gets a Network RCE

Patch Tuesday: SQL Server 2022 Gets a Network RCE

This week's Patch Tuesday landed three new SQL Server CVEs. Two are elevation-of-privilege bugs — familiar territory, we had three of those last month. The third one is different. CVE-2026-33120 is a remote code execution flaw in SQL Server 2022. CVSS 8.8. An authenticated, low-privileged login on the network can execute arbitrary code on your SQL Server.

Go. Patch. Now.

CVE-2026-33120 — The RCE

The short version:

MetricValue
CVSS 3.1 base8.8 (High)
Attack VectorNetwork
Attack ComplexityLow
Privileges RequiredLow
User InteractionNone
Exploit MaturityUnproven
CWECWE-822 (Untrusted Pointer Dereference)
AffectedSQL Server 2022, build < 16.0.1175.1

Microsoft's one-line description: "Untrusted pointer dereference in SQL Server allows an authorized attacker to execute code over a network."

In plain terms: the engine is taking a value supplied by a client session and treating it as a memory address — then reading or writing whatever is at that address without checking that it belongs to legitimate session memory. The attacker chooses the address. Whatever is there gets read or written. With the right alignment, that is code execution under the SQL Server service account context.

The gate is a valid login. That's it. No sysadmin. No db_owner. No user interaction. Just a network path and authentication.

If your 2022 instances have SQL Auth enabled, weak service-account passwords, or stale logins hanging around from old applications -- that is your attack surface. And if you read my post on the Larva-26002 BCP malware campaign, you already know what the front half of the attack chain looks like.

CISA's ADP scoring currently lists exploitation as 'none' and automation as 'no.' That window will close. Patch before it does.

The Two EoPs Riding Along

Both affect every currently supported version — 2016 SP3, 2017, 2019, 2022, 2025.

CVE-2026-32167 — SQL injection inside internal system procedures (CWE-89). SQL Server itself is constructing dynamic SQL in some of its own elevated routines without fully neutralizing special characters in the inputs. An attacker who already holds high database privileges can invoke the vulnerable procedure, inject T-SQL, and escalate from high-priv database user to sysadmin. CVSS is the lower end of High because the prerequisite is already an insider or a previously compromised account. The impact if they get there is total.

CVE-2026-32176 — elevation of privilege in the SQL Server engine. The accompanying KB describes bug reference 5029960 — a linked-server fix riding under this CVE — as allowing "a low-privileged SQL Server user to gain sysadmin permissions." Fix Area: SQL Server Engine. Component: PolyBase.

If you run linked servers — and we all run linked servers — this one is not optional.

The Patches

All three CVEs are addressed in the April 14, 2026 Patch Tuesday updates. The RCE (33120) is called out specifically in the SQL Server 2022 GDR KB. The two EoPs span 2016 through 2025.

Version Track KB Build
SQL Server 2016 SP3 GDR KB5084821 13.0.6485.1
SQL Server 2017 CU31 KB5084818 14.0.3525.1
SQL Server 2019 CU32 KB5084816 15.0.4465.1
SQL Server 2022 GDR KB5084815 16.0.1175.1
SQL Server 2022 CU24 KB5083252 16.0.4250.1
SQL Server 2025 CU3 KB5083245 17.0.4030.1

GDR track gets the GDR KB. CU track gets the CU KB. Don't switch tracks by accident. It is painful to undo.

One more thing. SQL Server 2016 hits end of support on July 14, 2026. These may be among the last CVEs it receives a patch for. If you are still running it, the clock is ticking very loudly now.

Check Your Build

SELECT SERVERPROPERTY('ProductVersion')     AS ProductVersion,
       SERVERPROPERTY('ProductLevel')       AS ProductLevel,
       SERVERPROPERTY('ProductUpdateLevel') AS CULevel,
       SERVERPROPERTY('Edition')            AS Edition;

Match your build to the table above. If you are below the target for your version and track, you NEED the patch.

More to Read:

CVE-2026-33120 — Microsoft SQL Server Remote Code Execution Vulnerability
CVE-2026-32167 — SQL Server Elevation of Privilege Vulnerability
CVE-2026-32176 — SQL Server Elevation of Privilege Vulnerability
sqlfingers: Patch Tuesday: Your sysadmin Role Was Up for Grabs (March)
sqlfingers: SQL Server 2016: 111 Days. The Last Patch Just Dropped.

Thursday, April 16, 2026

AI-Generated SQL Was Wrong. Nobody Noticed.

There is a shift happening right now that too many people are treating as harmless convenience.

People are starting to let tools write their SQL. Not snippets or suggestions, but full queries, and they are running them as-is.

That should concern you more than it does.

The Problem

Here is a simple example. A user asks Copilot for 'all customers who have not ordered in the last year'.

The generated query looks reasonable:

SELECT
    c.CustomerID,
    c.CustomerName
FROM dbo.Customers c LEFT JOIN dbo.Orders o
  ON c.CustomerID = o.CustomerID
WHERE
    o.OrderDate < DATEADD(YEAR, -1, GETDATE())
    OR o.OrderDate IS NULL
ORDER BY
    c.CustomerID;
GO 

It runs. Returns data. Everything looks fine. But it is wrong.

Why is it wrong?

Adams has a recent order, but why is Adams in the result set? It does not return only the customers who have not ordered in the last year. Instead, it returns:

•  Customers with old orders
•  Customers with no orders

Those are not the same as 'have not ordered in the last year'. Adams appears because the JOIN returns multiple rows per customer, and one of those rows is an older order, which satisfies the WHERE clause. That single matching row is enough to include Adams, even though he has ordered within the last year.

This is the correct query:

SELECT
    c.CustomerID,
    c.CustomerName
FROM dbo.Customers c
WHERE NOT EXISTS
(
    SELECT 1
    FROM dbo.Orders o
    WHERE o.CustomerID = c.CustomerID
      AND o.OrderDate >= DATEADD(YEAR, -1, GETDATE())
)
ORDER BY
    c.CustomerID;
GO

As you can see, the difference matters. A lot.

The Real Risk

That's a very small example and this is not about syntax errors. It is about logic errors that look correct, run fast, and quietly return the wrong data.

No error message. No warning. Just incorrect results.

Now let's scale up a bit:

•  Financial reporting
•  Auditing queries
•  Data exports
•  Application logic

If the query is wrong, everything downstream from it is also going to be wrong. Do you want your balance sheets or income statements to quietly include bad data?

It Gets Worse

The AI tools do not understand your data. Repeat. AI does not know your data, your business rules, your edge cases or your intent.

AI predicts patterns. That is all.

And sometimes those patterns are just convincing enough to pass review.

The DBA Problem

This is where things shift. The AI tool is not technically answering your question. It is generating a query that it predicts will match your question. It is a fine line between the two, but pattern matching is not the same as answering a question.

If AI misinterprets your request, misunderstands the schema, or guesses at relationships, the SQL can still run and return results that look reasonable.

If you're using AI to write your SQL, your job is no longer just to verify that it runs. It is also to confirm that the result actually answers the question that was asked.

The Bottom Line

If you did not write the query... You do not trust it blindly. Because 'it runs' is not validation, 'it looks right' is not proof, and 'it seems fine' is not a strategy.

More to Read

AI-generated code changes need debugging — Venturebeat
Closing the AI trust gap for developers
Why Text-to-SQL Fails — Omni
Text-to-SQL Accuracy: Why Semantic Errors Are the Real Problem — AI2SQL

Wednesday, April 15, 2026

SSMS 22: What's Different, What's Worth It, and What May Bite You

If you're still on SSMS v21 (or older), this post is for you. SSMS 22 is a bigger jump than before, and the configuration is nothing like what you're used to. Here's what to expect when you make the move, what's worth configuring, and what's going to surprise you.

Current version as of this writing: 22.5.0, released April 14, 2026.

Installation Is Different Now

SSMS 22 installs through the Visual Studio Installer — a small stub that downloads the rest. This is the same mechanism introduced in v21, so if you've done that before, no change. If you're coming from v20 or earlier, you need to know the installation is with the VS Installer.

The good news: SSMS 22 installs side-by-side with v19, v20, and v21. You don't have to uninstall anything first, and during setup you'll get the option to import your settings from a previous version. Take it — it carries over your registered servers, keyboard shortcuts, and most preferences. I did this a little hesitantly, but no regrets. Very quickly carried everything over from my existing SSMS.

During installation, check the boxes for the components you want. GitHub Copilot and the Query Hint Recommendation Tool are separate workloads — they're not installed by default. If you don't check the box during initial install, you can add it later by opening Visual Studio Installer, selecting Modify on your SSMS 22 installation, and checking Query Hint Recommendation Tool under Code Tools on the Workloads tab.

VERY IMPORTANT: The Encryption Default Changed

This is the one that's going to catch on-premises shops off guard. SSMS 22 defaults the connection dialog to Strict encryption (TDS 8.0). If your SQL Server instance doesn't have a valid SSL certificate configured, you'll get a connection error the first time you try to connect.

A connection was successfully established with the server, but then an error occurred during the login process.
(provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

The fix: in the connection dialog, change Encrypt from Strict to Optional for on-premises instances without a proper certificate. This is the right call for most on-prem environments. Strict encryption is primarily meaningful for Azure SQL and public-facing instances.

What's New

64-bit at last. SSMS was a 32-bit application for its entire life until now. That ceiling is gone. You can keep dozens of query tabs open without hitting out-of-memory errors, and Object Explorer loads deep hierarchies noticeably faster on larger environments.

Themes. Go to Tools\Themes and look at all the options! Cool Slate, Spicy Red, Juicy Plum, and more. Even better, proper dark mode is finally here, and it's consistent across the query editor, Object Explorer, and results grid. I did another writeup on the different flavors here.

Rename and color tabs. Right-click any query tab to rename it. You can also color-code tabs by file extension. Both of these can be very helpful when you're juggling multiple scripts across multiple servers. I really like this and I think it is long overdue.

Zoom the results grid. Ctrl+Scroll to zoom in and out on the results pane. Very cool for presenting or screen sharing without having to manually adjust font sizes everywhere.

Execution plan in a new tab. Right-click an execution plan and select 'Show Execution Plan in New Tab'. Keeps it open for later reference while you continue working in the query editor.

Group by schema in Object Explorer. New in 22.4.1. Toggle it with the icon between Refresh and Show Policy Health in Object Explorer, or set the default under Tools\Options\SQL Server Object Explorer\General. When you work in databases with a lot of schemas, this is a nice addition.

Export results in more formats. The results grid now supports saving to Excel, JSON, Markdown, and XML - not just CSV and text. How long have we been waiting for this? Find it in the Results tab, 'Save Results As' option.

SQL Server 2025 support. If you're on SQL Server 2025 and still running an older SSMS, IntelliSense is flagging valid T-SQL as errors and your execution plans aren't rendering correctly. v22 is the only version that fully understands 2025 syntax - native JSON, vector data types, the new index types, all of it. This alone is reason enough to upgrade.

GitHub Copilot: Now GA, Still Has Gotchas

Copilot in SSMS went GA with 22.4.1. Install it as a workload through Visual Studio Installer, then access it via View\GitHub Copilot Chat. You need an active GitHub Copilot subscription - there's a free tier with monthly limits.

Two modes: the chat window, and inline code completions in the query editor. The code completions are the more immediately useful of the two - they integrate with your existing workflow without requiring you to switch context. Takes a bit to get used to, but definitely helpful when you find the groove. I've covered code completions here and the chat window here.

Worth knowing: early versions had a bug where Copilot was generating 1,200+ API requests in a single day, eating through usage limits. That was fixed in 22.4.1.

Query Hint Recommendation Tool: Also Now GA

Also GA in 22.4.1. Once installed (see above), open it under Tools\Query Hint Recommendation Tool. Highlight a SELECT query, press Start, and it will systematically test query hints against your query and report which ones (if any) improve elapsed time.

Two things to know before you get too excited. Only one of which you can do anything about. First: it only works on single SELECT statements. No DML or stored procedures - only individual SELECTs. Second: the default minimum improvement threshold is 50%, which means it won't recommend a hint unless it cuts elapsed time in half. Change that to 5% under the tool settings or you'll get 'no hints recommended' on almost everything.

Settings Worth Changing

Open a query window on startup. Tools\Options\All Settings\Environment\Startup. Then in At Startup: set to 'Open Object Explorer and query window'. Otherwise SSMS opens to nothing every time. Not bad, I guess. If clicking 'New Query' is too much work.

Turn off the line ending consistency check. Tools\Options\All Settings\Environment\Documents: uncheck 'Check for consistent line endings on load'. This warning fires constantly if you work with .sql files that originated on Linux or mixed environments. It's just noise. Use this option to silence it. Just tested. It works!

Clean up tab names. Tools\Options\All Settings\Text Editor\Editor Tab and Status Bar: set 'Include Login Name' and 'Include Server Name' to false. Tab names become just the file name, which is all you need. Requires an SSMS restart.

Tab position and sorting. Tools\Options\All Settings\Environment\Tabs: tabs can now be displayed on the left side of the editor (between Object Explorer and the query pane), and sorted alphabetically. If you find yourself working with many open scripts, this is much more navigable than the default top bar. I like.

Presenter Mode. View\Presenter Mode\Toggle Presenter Mode. Increases icon, menu, and text sizes across the entire application. If you do any screen sharing or presenting, I would just leave this on permanently. It handles a lot of individual font adjustments in one switch.

What Doesn't Work Well

Redgate tools. SSMS 22.3 initially caused widespread crashes for anyone running SQL Prompt 11 or SQL Search, but it's been fully resolved. SQL Prompt 11.3.10 and later are explicitly confirmed compatible with SSMS 22.5, and Redgate has addressed compatibility on their side as well. If you're on Redgate tools, make sure you're on SQL Prompt 11.3.10 or later before upgrading to 22.5.

General slowness. Multiple users report SSMS 22 feeling slower than v20 or v21, specifically, the Object Explorer expansion. 22.5.0 includes startup time improvements, but the community feedback on performance is mixed. If you have a powerful machine you likely won't notice. If you're on a modest workstation, you're likely going to feel this.

Registered Servers connectivity. Some users report that right-click \ Connect All on a server group in Registered Servers leaves one or two servers in a perpetual 'expanding' state. I haven't been able to replicate this yet, but it's still worth knowing - especially if you use the CMS groups a lot like I do.

Third-party extensions. SSMS 22 doesn't officially support third-party extensions yet — no VSIX support, no marketplace. Microsoft is working on it with no committed timeline. If your workflow depends on non-Redgate SSMS extensions, verify compatibility before upgrading.

Bottom Line

If you're running SQL Server 2025, you need SSMS 22 — full stop. If you're on SQL Server 2019 or 2022 and heavily dependent on Redgate tools or third-party extensions, you should verify compatibility on a non-production instance pre-upgrade. For everyone else, 22.5.0 is stable, the 64-bit upgrade is real, and the new tab and results features are worth the switch. Just don't forget to change the encryption default before you try to connect to anything on-prem.

More to Read

SSMS 22 Release Notes (Microsoft Learn)
Known Issues in SSMS 22 (Microsoft Learn)
Download SSMS 22
My SSMS 22 Configuration - VladDBA (detailed settings walkthrough)

Monday, April 13, 2026

SQL Server 2025 Finally Has RegEx Available Without CLR

For two decades, getting regular expression support into SQL Server meant CLR assemblies, deployment headaches, and a whole lot of discussions with your security team. Or, you may have used the PATINDEX method and spent 45 minutes writing something that any other language handles in one line. Either way, you were doing a lot of work to get the job done.

SQL Server 2025 changes this. Native RegEx support is here, built directly into T-SQL, no CLR required. Seven new functions ship with the engine, and they cover the full range of what you actually need: matching, extracting, replacing, splitting, and counting.

The Seven Functions

Function What it does Type
REGEXP_LIKE Boolean match — does the string match the pattern? Scalar
REGEXP_COUNT Returns the number of times a pattern matches Scalar
REGEXP_INSTR Returns the position where the pattern matches (like CHARINDEX, with regex) Scalar
REGEXP_SUBSTR Extracts the matching substring Scalar
REGEXP_REPLACE Replaces matches with a specified string Scalar
REGEXP_MATCHES Returns all matches with position details Table-valued
REGEXP_SPLIT_TO_TABLE Splits a string on a pattern delimiter, returns rows Table-valued

One prerequisite to flag up front: REGEXP_LIKE, REGEXP_MATCHES, and REGEXP_SPLIT_TO_TABLE require database compatibility level 170. If you just installed SQL Server 2025 but haven't bumped your CL yet, those three won't work. Check yours and adjust if needed:

-- Check current compat level
SELECT name, compatibility_level
FROM sys.databases;

-- Bump to 170 if needed (SQL Server 2025)
ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 170;

The other four — REGEXP_COUNT, REGEXP_INSTR, REGEXP_SUBSTR, REGEXP_REPLACE — are available at all compatibility levels in SQL Server 2025.

REGEXP_LIKE in a CHECK Constraint

If you're building a new table, put the constraint in day one. Then, bad data never gets in, and you won't end up having to find and fix the junk at a later date. With the email check constraint, the engine enforces it on every insert and update without any application code, triggers, or stored procedures required. With REGEXP_LIKE now available in 2025 T-SQL, you can enforce a real email pattern directly in the column definition:

CREATE TABLE dbo.Contacts (
    ContactID  INT IDENTITY PRIMARY KEY,
    FullName   VARCHAR(100),
    Email      VARCHAR(200) CONSTRAINT chk_Email_Format
         CHECK (REGEXP_LIKE(Email, '^[a-zA-Z0-9._%+\-]+@[a-zA-Z0-9.\-]+\.[a-zA-Z]{2,}$')),
    Phone      VARCHAR(50)
);

Good data goes in fine:

INSERT INTO dbo.Contacts (FullName, Email, Phone)
VALUES
    ('Rebecca Lewis', 'rebecca@sqlfingers.com', '(555) 867-5309'),
    ('Jane Doe',      'jane.doe@somewhere.com', '5551234567');

Bad email gets rejected immediately:

INSERT INTO dbo.Contacts (FullName, Email, Phone)
VALUES ('Bob Slob', 'bob.slob@@notright', '555.123.4567');
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "chk_Email_Format".
The conflict occurred in database "DBA", table "dbo.Contacts", column 'Email'.

Very simple, easy data validation lives in the table definition - where it belongs.

REGEXP_LIKE: Find the Junk in an Existing Table

That's the clean-slate scenario. For the more common one, where you've inherited a table with no constraints in place, the approach is the same — REGEXP_LIKE will find the damage:

CREATE TABLE dbo.ContactsDirty (
    ContactID  INT IDENTITY PRIMARY KEY,
    FullName   VARCHAR(100),
    Email      VARCHAR(200),
    Phone      VARCHAR(50)
);

INSERT INTO dbo.ContactsDirty (FullName, Email, Phone)
VALUES
    ('Rebecca Lewis', 'rebecca@sqlfingers.com', '(555) 867-5309'),
    ('Bob Slob',      'bob.slob@@notright', '555.123.4567'),
    ('Jane Doe',      'jane.doe@somewhere.com',  '5551234567'),
    ('No Format Guy', 'noemail', '(555) 999-0000');

-- Find the bad rows
SELECT ContactID, FullName, Email
FROM dbo.ContactsDirty
WHERE NOT REGEXP_LIKE(Email, '^[a-zA-Z0-9._%+\-]+@[a-zA-Z0-9.\-]+\.[a-zA-Z]{2,}$');

Results:

Two rows of garbage, found instantly. No CHARINDEX nesting, no PATINDEX gymnastics.

REGEXP_REPLACE: Clean Up Messy Phone Data

Classic ETL problem: a phone number column with every possible format imaginable. Dashes, dots, parentheses, spaces — pick your poison. Use this to strip everything non-numeric in one pass:

SELECT
    Phone AS OriginalPhone,
    REGEXP_REPLACE(Phone, '[^0-9]', '') AS DigitsOnly
FROM dbo.ContactsDirty;

Results:

Or, maybe you want to reformat to a standard pattern while you're at it?

;WITH Stripped AS (
    SELECT ContactID, Phone,
           REGEXP_REPLACE(Phone, '[^0-9]', '') AS DigitsOnly
    FROM dbo.ContactsDirty
)
SELECT Phone,
       REGEXP_REPLACE(DigitsOnly, '^(\d{3})(\d{3})(\d{4})$', '(\1) \2-\3') AS Formatted
FROM Stripped;

REGEXP_SUBSTR: Extract Only What You Need

Here's a good one. Use REGEXP_SUBSTR to pull only what you need from the targeted string values. Domain, area code, whatever you need:

SELECT
    Email,
    REGEXP_SUBSTR(Email, '[^@]+$') AS Domain
FROM dbo.ContactsDirty
WHERE REGEXP_LIKE(Email, '^[a-zA-Z0-9._%+\-]+@[a-zA-Z0-9.\-]+\.[a-zA-Z]{2,}$');
SELECT
    Phone,
    REGEXP_SUBSTR(REGEXP_REPLACE(Phone, '[^0-9]', ''), '^\d{3}') AS AreaCode
FROM dbo.ContactsDirty;

Add the Constraint After Cleanup

Once the data is clean, add the constraint so it stays that way. You need to verify that all existing data is good first, or the ALTER TABLE will fail:

-- Confirm no bad rows remain before adding the constraint
SELECT ContactID, FullName, Email
FROM dbo.ContactsDirty
WHERE NOT REGEXP_LIKE(Email, '^[a-zA-Z0-9._%+\-]+@[a-zA-Z0-9.\-]+\.[a-zA-Z]{2,}$');

-- If that returns zero rows, you're clear to add the constraint
ALTER TABLE dbo.ContactsDirty
ADD CONSTRAINT chk_Email_Format
CHECK (REGEXP_LIKE(Email, '^[a-zA-Z0-9._%+\-]+@[a-zA-Z0-9.\-]+\.[a-zA-Z]{2,}$'));

Before You Go Wild: There are Some Gotchas

  • It's the RE2 library, not PCRE. SQL Server 2025 uses Google's RE2 regex engine. RE2 is fast and safe (no ReDoS vulnerability), but it does not support lookahead (?=...) or lookbehind (?<=...). If you've written regex in .NET or Python, those won't work here.

  • Not SARGable. REGEXP_LIKE in a WHERE clause will not use an index — it's a full scan. Use it for data cleaning, validation, ad-hoc queries, and ETL. Don't drop it on a large OLTP table and expect it to perform. Very important.

  • Don't replace working LIKE code. LIKE is significantly faster than REGEXP_LIKE for simple pattern matching. If LIKE does the job, keep it. Regex earns its place when the pattern is genuinely complex or when you need extract/replace behavior.

  • LOB types are partially supported. REGEXP_LIKE, REGEXP_COUNT, and REGEXP_INSTR support varchar(max)/nvarchar(max) up to 2MB. REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_MATCHES, and REGEXP_SPLIT_TO_TABLE do not support LOBs at all — RE2 library limitation.

Those are real constraints. None change the fact that RegEx in SQL Server 2025 is a long-overdue quality-of-life improvement for any DBAs who've spent the last two decades cobbling this together with CLR assemblies or creative PATINDEX methods.

More to Read

Work with Regular Expressions - SQL Server 2025 (Microsoft Learn)
What's New in SQL Server 2025 (Microsoft Learn)
Regular Expressions in SQL Server 2025 - Rudi Bruchez (deep dive on RE2 and limitations)

Misaligned Log IOs After Hyper-V Conversion: Cause and Solution

Every once in a while, SQL Server hands you an error message you've never seen before. This post is one of those cases.

The Error

My customer recently converted a physical server to Hyper-V. Shortly after turning everything back on and resuming the logshipping, this started appearing in the SQL Server error log:

There have been 256 misaligned log IOs which required falling back to synchronous IO.
The current IO is on file t:\MSSQL11.NAVISION\MSSQL\DATA\NEWPROD_5.ldf.

And then it kept coming. Every 256 misaligned IOs, SQL Server logs another one. Before a clean diagnoses was made, the count was up to 3,072 and climbing. You can see here, the counter is cumulative since the last SQL Server restart.

Measuring the Impact

The first thing I did was check write latency on that log file. This query scopes sys.dm_io_virtual_file_stats to the T: drive to see actual write latency:

SELECT 
    DB_NAME(vfs.database_id) AS database_name,
    mf.physical_name,
    vfs.num_of_writes,
    vfs.io_stall_write_ms,
    vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0) AS avg_write_stall_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs JOIN sys.master_files mf 
  ON vfs.database_id = mf.database_id 
  AND vfs.file_id = mf.file_id
WHERE mf.physical_name LIKE 'T:\%'
ORDER BY avg_write_stall_ms DESC;

Reminder: SQL Server 2012 uses io_stall_write_ms, not io_stall_write. Don't learn that the hard way.

Results:

DB Name Physical Name Writes IO Stall
(ms)
Avg Write
(ms)
PRODUCTION t:\MSSQL11.NAVISION\MSSQL\
DATA\NEWPROD_5.ldf
131,079 2,009,293 15

15ms average write latency on the transaction log. Healthy is under 5ms. Not catastrophic, but real, measurable, and directly in the transaction commit path.

What Is Actually Happening

SQL Server writes to the transaction log using async I/O. For async I/O to work, the write buffer must be aligned to the physical sector boundaries of the underlying storage. When that alignment is off, SQL Server can't use async I/O and falls back to synchronous I/O instead — meaning each log write must fully complete before the next one begins. Every transaction commit waits. This is not normal behaviour.

The question is: why did the alignment break?

Diagnosing the Cause

Storage was the obvious suspect after a Hyper-V conversion, so I started there. Partition offset misalignment is a known gotcha in these types of migrations, so that was my first stop. Run this on the VM from an elevated command prompt:

fsutil fsinfo sectorinfo T:

Here is what came back:

Two things stand out immediately.

First: Partition alignment on device: Aligned (0x000). The partition is properly aligned - so my offset misalignment theory is off the table.

Second, and this is the actual problem: LogicalBytesPerSector is 512, but PhysicalBytesPerSectorForAtomicity is 4096. The logical sector size, what the disk presents to the OS and what SQL Server used when it created the transaction log, is 512 bytes. The physical sector size that the Hyper-V virtual disk actually operates on is 4KB. SQL Server 2012 detects this mismatch on every log write and falls back to synchronous I/O as a safety measure. That is exactly what the error message is reporting.

This is a known consequence of P2V (physical-to-virtual) migrations. The original physical server had 512-byte physical sectors. The Hyper-V virtual disk presents 512-byte logical sectors for compatibility, but the underlying storage operates on 4KB physical sectors. The transaction log file was created in the 512-byte world and is now living in the 4KB world without knowing it.

The Fix: Trace Flag 1800

Microsoft documents this fix in KB3009974, which covers SQL Server 2012 explicitly. The KB title references log shipping, which is exactly what we have here, and the underlying cause is the 512-byte vs 4KB sector size mismatch — produced by the P2V conversion.

The fix is Trace Flag 1800, which tells SQL Server to write transaction log entries using 4K sector alignment going forward, matching what the virtual disk physically requires. Async I/O works correctly again because the writes are now properly aligned. This is not a downgrade — it is correcting the alignment so the intended behavior can resume.

In SQL Server Configuration Manager, navigate to SQL Server Services, right-click the instance, select Properties, go to the Startup Parameters tab, and add:

-T1800

Click Add, then OK. Restart the SQL Server service. A brief service restart is all that is required — no extended maintenance window, no data file changes — no partition rebuilds.

To verify the flag is active after the restart, run the following command in SQL Server Management Studio:

  
  DBCC TRACESTATUS(1800);

The Short Version

If you converted a physical SQL Server to Hyper-V and started seeing misaligned log IO warnings, run fsutil fsinfo sectorinfo against the drive hosting your transaction log. If LogicalBytesPerSector and PhysicalBytesPerSectorForAtomicity do not match, you have a sector size mismatch and this is likely your cause. Add -T1800 as a startup parameter and restart the service.

First time I've seen this one. Hopefully the last.

References

KB3009974 - Fix: Slow synchronization when disks have different sector sizes in SQL Server AG and Logshipping environments

Wednesday, April 8, 2026

Every Few Years, Someone Kills SQL Server. Here We Are Again.

LinkedIn is doing that thing again. 'Is SQL Server Dead?' posts are getting traction, the comment sections are lit, and I'm sure a lot of managers out there are forwarding one of them to a DBA with a question mark and no context. It happens every few years. NoSQL was going to kill it. The cloud was going to kill it. Now it's AI and Fabric. I've been doing this for 28 years. Let me save you some time.

No. SQL Server is not dead or even dying. But something is changing — and we need to understand what.

The Market Signal

Start with something real. Brent Ozar — who has been in the industry longer than many of those LinkedIn posters — wrote in February that his consulting pipeline nearly shut off in December. Not because SQL Server is irrelevant. But because clients started telling him: 'For 2026, any time we want to buy something, build something, or hire someone, we're going to try AI first and see what happens.'

That's not a LinkedIn hot take. That's a working consultant describing what his clients are actually saying. Pay attention to that.

What Microsoft Said in Atlanta

Eight thousand people descended on Atlanta in March for FabCon and SQLCon — the first time Microsoft's SQL community conference was co-located with the Fabric conference. The headline message from the keynote: 'SQL Server is not being replaced. It is being elevated.'

I believe them. SQL Server 2025 is reportedly growing faster than any previous version. The product is genuinely good. Microsoft isn't walking away from it.

But read the fine print. Every piece of the 'unified platform' story — the Database Hub, the migration experience, the single pane of glass across your entire database estate — runs through Azure Arc. If you want a seat at that table, your on-prem SQL Server needs to be Arc-enabled. Phone home to Azure. That's not a knock. It's just the architecture. Know what you're agreeing to before you agree to it.

What AI Actually Does to This Job

I've spent the last several months testing AI against real client workloads. Six posts on sp_BlitzCache and ChatGPT. The sp_BlitzIndex @AI parameter. Stored procedure refactoring. The MCP server. I'm not theorizing — I have results.

Here's what I found: AI handles the easy stuff faster than I do. Index suggestions on a clean, well-documented table? Great. Obvious query rewrites? Fine. Spotting missing statistics? Sure. If your job consists entirely of those things, yes, you should be paying attention to the market signal above.

But Brent said something in January that I think is exactly right: people doing mission-critical, secure, accurate database development on large existing databases will still struggle in 2026 due to undocumented databases and bad tooling. AI sees the query. The DBA knows the story. The DBA knows which index exists because of an outage three years ago. The DBA knows which stored procedure cannot be rewritten for political reasons. And they know why the schema is what it is — and why it cannot be changed.

That institutional knowledge is not in a prompt. It is not in a system message. It lives in the head of the person who has been sitting in that environment, and no amount of context window is going to replace that.

The On-Prem Majority

Here's the thing about FabCon: the people in that room skew heavily toward cloud-forward. They're already moving. They're already building on Fabric. They are not your clients, and very likely they are not you.

Observers at the Atlanta event noted that the SQL Server on-prem base was noticeably more visible than at FabCon Vienna in 2025, where most attendees seemed already committed to Azure SQL or cloud-first patterns. That's very telling. The on-prem majority showed up in Atlanta and made itself known — because it isn't going anywhere anytime soon.

The surveys back this up. Most production SQL Server environments are running hybrid: a mix of on-prem, rented data center, and cloud. That picture does not change overnight. And it certainly doesn't change because someone posted something on LinkedIn.

So What Is Actually Changing

The job description is shifting. Not disappearing — shifting. The shops that are going to feel it first are the ones where the DBA's value is mostly procedural: run the index job, apply the patch, restore the backup. If that's the bulk of what you do, AI is coming for your job — and it's coming faster than most people are ready for.

The shops that will be fine are the ones with a human who understands the environment at a level no tool can replicate. The one who can look at a blocking chain and know, without running a single query, which application is probably causing it. The one who remembers the last time someone tried to change that setting and what happened. The one who knows the business, not just the database.

That's not a description of someone who should be afraid of AI. It's a description of someone who should be using it — which is exactly what I've been doing. I stopped worrying about whether AI belongs in my toolkit. It does. It gets me where I need to go faster.

SQL Server has been declared "dying" or "replaced" since the rise of NoSQL a decade ago, yet it remains one of the top four most used databases worldwide in 2025–2026, alongside MySQL, PostgreSQL, and SQLite.

SQL Server has been around for 37 years. I'm betting it will outlast this conversation.

More to Read

The Tech Consulting Market Isn't Looking Good — Brent Ozar
Database Development with AI in 2026 — Brent Ozar
FabCon and SQLCon 2026: Unifying Databases and Fabric — Microsoft Azure Blog
SQL Server at FabCon and SQLCon 2026 — John Deardurff
Event Debrief: FabCon / SQLCon Atlanta 2026 — WhereScape
sp_BlitzCache + ChatGPT Series — sqlfingers.com

Tuesday, April 7, 2026

ADR Comes to TempDB in SQL Server 2025. Read This Before Enabling.

Two weeks ago I covered the Resource Governor changes in SQL Server 2025 — specifically, capping how much tempdb data space a workload group can consume. That was the data-file side. For the log side, SQL Server 2025 now lets you enable Accelerated Database Recovery (ADR) on tempdb. Enable it and cancelled queries stop grinding, the tempdb log stops bloating, and recovery gets faster. Sounds like an easy yes — but you've got to read the fine print.

What ADR Does — and Why It Matters for TempDB

ADR has been around for user databases since SQL Server 2019. The core idea: instead of a long, painful undo phase when a query is cancelled or a transaction rolls back, SQL Server maintains a Persistent Version Store (PVS) that lets it undo work almost instantly. No more watching Cancelling query... while the tempdb log blows. Rollback happens instantly regardless of how much work was in flight.

SQL Server 2025 extended the ADR support to the tempdb system database. The tempdb log has always been its own special kind of problem — runaway transactions, ETL jobs that hold space long after they should have cleaned up, startup times dragged out by whatever was open when things went sideways. ADR addresses all of it. Instant rollback, aggressive log truncation, predictable recovery. The concept is solid, but the execution has some rough edges you need to know about before touching production.

How to Enable It

ADR is available in both Standard and Enterprise editions, but disabled by default on tempdb, and must be configured:

ALTER DATABASE TempDB
SET ACCELERATED_DATABASE_RECOVERY = ON;
GO

To check whether it is currently enabled:

SELECT name, is_accelerated_database_recovery_on
FROM sys.databases
WHERE name = 'tempdb';

A restart is required to enable or disable ADR on tempdb. Not optional. TempDB is rebuilt from scratch on every service start, so ADR infrastructure has to be there at creation time. The documentation is clear on this. Plan a maintenance window.

What Happens to Tempdb Storage

When ADR is on, tempdb runs two independent version stores simultaneously: the traditional version store for row versions from user databases using RCSI or snapshot isolation, and the new PVS for tempdb transactions. Both live in the tempdb data files, and Microsoft is upfront that tempdb data space requirements will increase. If your tempdb drive is already tight, figure out the space before you enable this, not after. PVS size in tempdb can be monitored the same way as in any user database.

The Known Issues — Not Exactly Front and Center

Microsoft has documented several known issues with ADR on tempdb. What's notable is where that documentation lives — not on the main ADR feature page, not on the tempdb page, but tucked into the ADR troubleshooting article. A page many people never open before enabling a feature - or after. Here's what's in there:

Performance Degradation (Latch Contention)
In environments with high-frequency create/drop of temporary tables, enabling ADR on tempdb can cause severe throughput degradation. The culprit is latch contention on the sys.sysobjvalues internal system table as multiple processes compete for PVS access simultaneously. High-concurrency OLTP with per-session temp tables, stored procedures spinning up temp tables in loops, apps firing create/drop in rapid succession — all at risk. Heavy ETL that creates a few large temp tables and holds them is generally fine. It's the volume and velocity of DDL events that triggers the contention, not the data size. Under investigation, no fix date. See Monitor and Troubleshoot ADR — Microsoft Learn.

Increased Transaction Log Generation
ADR logs every row version written to the PVS. In practice, that means enabling it on tempdb can substantially increase transaction log generation — which is a bit ironic given that log reduction is one of the reasons you'd even turn this on. See Accelerated Database Recovery — Microsoft Learn.

Long-Running Transaction Risks
ADR makes rollbacks fast, but it doesn't make the PVS self-managing. Long-running active transactions block PVS cleanup, which means the PVS grows — and keeps growing — until those transactions close. Add SNAPSHOT or RCSI isolation to the mix and the problem compounds. Left unchecked, a long-running transaction can still fill tempdb right through the PVS. See Monitor and Troubleshoot ADR — Microsoft Learn.

When It's Worth Enabling

If your environment has real tempdb log pressure — regular blowouts on ETL runs, rollbacks that drag on long after a query is cancelled, startup times that vary wildly depending on what was running when things went down — this feature is aimed directly at you. It works. The concept is proven on user databases and the mechanics are the same here.

Before you enable it on TempDB:

•  Test against real workload patterns, not a sanitized demo environment.
•  Avoid large transactions that include heavy DDL operations.
•  Pre-size tempdb data files to account for PVS growth before flipping the switch.
•  Monitor PVS size and cleanup after enabling — don't assume it's managing itself.
•  If your workload involves heavy temp table churn at high concurrency, I'd hold off until the latch contention issue is resolved.

The Bottom Line

The Resource Governor caps and ADR on tempdb together give you real control over tempdb growth problems for the first time. They're complementary and each must be configured separately. The Resource Governor side is production-ready today. ADR on tempdb is promising but not ready yet. Microsoft is still working through the known issues. Keep an eye on the release notes and known issues, and when that latch contention fix ships, this becomes a much easier recommendation.

More to Read

TempDB Database — Microsoft Learn
Accelerated Database Recovery — Microsoft Learn
Monitor and Troubleshoot ADR — Microsoft Learn
Accelerated Database Recovery in SQL Server 2025 — Straight Path SQL
Resource Governor in SQL Server 2025 — sqlfingers.com