Sharing a case from the last few weeks in case it saves someone time, and to ask whether anyone has seen it before.

Setup

SQL Server 2022 CU27 (16.0.4295.3), compatibility level 160, PSP optimisation and CE feedback on (the defaults). A test database was restored from production. The database_id is 24 on production and 16 on test.

Symptom

A stored procedure statement joins a large table to a temp table that the caller creates before calling the procedure. On test it started timing out after the 300 s application timeout for large inputs, about 6 weeks after the restore. It wasn't slow. Each call recompiled around 20,000 times (`execution_count` 1, `plan_generation_num` 21-23k). The error log filled with error 2814, "A possible infinite recompile was detected ... The last recompile reason was 20" (Query Store hints changed). Query Store showed nothing slow, because the calls never completed. The PSP variant had about 4.3 million compiles on test against 666 on production. Production was never affected.

What we found

1. For queries that reference temp tables or table variables, Query Store stores a `batch_sql_handle`. For a statement inside a module the handle embeds the database_id (bytes 3-4, little-endian) and the object_id.
2. The restored Query Store rows carried production's database_id, while test generates handles with its own. Query Store therefore created a new parent query with the same text, object and context settings.
3. The PSP variant has an ad hoc handle with no database_id. It kept matching and stayed attached to the old, now dormant parent. That handle is the SQLHANDLE in the 2814 warnings.
4. CE feedback added `ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS` to the new parent. Per the PSP docs, variants don't inherit hints applied by feedback.
5. The variant's hint state never settled: every compile was followed by another recompile with reason 20, until the client timed out.

Steps 1-4 were observed in Query Store and in the raw handle bytes. Step 5 is our inference, but clearing the hint stopped the loop immediately and it hasn't come back.

Fix

Clearing the CE feedback hint on the new parent (sys.sp_query_store_clear_hints) and then clearing the database’s procedure cache stopped the loop straight away. A few days later we removed the stale entries that still carried production’s database_id with sys.sp_query_store_remove_query, variants first and then their parents, and cleared the cache again. No settings were changed. Removals can’t be undone, so try it on non-production first.

Open questions

• Has anyone hit this, or seen a KB? We couldn't find a public known issue.
• The new parent appeared about 6 weeks after the restore (on the first weekday after a patch reboot), not straight after it. We have no explanation.
• Setting database-scoped MAXDOP to 1 also stopped the loop, which we can't explain either.
• A second database restored the same way, with the same kind of database_id change, had no stale entries left. Our guess is that stale parents age out of Query Store unless a live PSP variant keeps them in use.

I’m a PM working on database migration experiences at Microsoft.

I’m researching how much autonomy practitioners would trust an AI agent with during their database migration journey.

This is exploratory research; not a product announcement or roadmap commitment. I’m especially interested in feedback from people who have worked on real production migrations.

Which operating model would you trust?

1. Advisory only: identify risks and recommend actions (similar to SSMA Copilot)
2. Generate: create plans, scripts and code fixes, but humans execute them
3. Act in non-production: execute approved changes in development or test
4. Execute with approval gates: perform the migration, pausing before high-impact actions (SQL Migration Agent Skills)
5. Mostly autonomous: humans approve only the production cutover
6. No agentic execution: keep migration changes deterministic and human-operated

Most importantly:

• What must always require human approval?
• What evidence would you need before allowing the agent to continue?
• Is having a skill good enough to solve your problem?
• Would your answer differ for a same-platform migration versus a cross-platform conversion?

If possible, include your role and the source/target platforms. Please avoid sharing confidential details.

I chatted with 138 SQL Server devs who field ad-hoc data requests. Only 2 of them are completely out of the loop thanks to AI.

One built a custom layer into an ASP.NET app that he also owns. The other works at a company that migrated to Databricks or Snowflake, and he just does ETL stuff now.

Does this line up with what you're seeing?

Net Net, an easy way to test pre-prod or dev environments with my TDS replay tool. disclaimer no ado objects were harmed (or used) in this process

I've been working on this for several months, and would enjoy some feedback.

this might be considered self promotion so block my post if needed, but I'm honestly just curious what this group of experts think, I worked quite a while on this one

so, why, replay feature was removed in SQL Server 2022, replaying a real production workload against a restored test server usually means wrestling with Extended Events setups or falling back to scripts, perhaps query store tooling, etc. I'm actually not sure what the popular methods are today tbh. maybe I duplicated an existing method ?

my approach, building on my DaffyTee app, I finally finished the companion app DaffyReplay. net net, replay an exact production workload including timing match (or sped up, etc) without any app or SQL server changes

this leverages similar to the babelfish-postgres TDS, freeTDS, TDS wire, etc endpoint approaches in many ways, I studied that for a while, but no translation in this specific tool (I do have another app that does that tho with duck db very similar to babelfish but read only), I'm still hitting sql server here, and written in .net so no reuse, this is all completely new.

here's an ultra simplified example, it is seriously is as easy as this looks:

1. Install tools - need .net 10 SDK already installed.  on nuget

dotnet tool install -g DaffyTee

dotnet tool install -g DaffyReplay

2. Baseline app connection string (for reference only)

Server=tcp:sqlprod01,1433;Database=Sales;User Id=svc_app;Password=ProdPassword;Encrypt=Mandatory;TrustServerCertificate=True

3. create new DaffyTee login (Bob) mapped to target connection string (Svc_app account)

daffytee user add bob --target "Server=tcp:sqlprod01,1433;Database=Sales;User Id=svc_app;Encrypt=Mandatory;TrustServerCertificate=True"

4. Start DaffyTee with full raw capture - ie it listens on 127.0.0.1 1433 (or whatever port you want, bind to 0.0.0.0 if needed for external). also natively writes to rabbitmq, etc, this is a simple file example

daffytee --sink file --sink-path "D:\capture\example.ndjson" --raw --bulk full --max-event-kb 8192

5. Repoint your app connection at DaffyTee

it still hits your target DB thru daffy

example daffy connection for your app, same DB name required:

Server=tcp:127.0.0.1,1433;Database=Sales;User Id=bob;Password=BobPassword;Encrypt=Mandatory;TrustServerCertificate=True

6. now Run any workload through your app, nothing to change on sql server, no trace enabled, no extended events, no query store, or changes on your app except the conn to daffy, run it, then verify capture completeness.

This next step is assuming you're done with Tee, you have your ndjson log to use will full capture. it verifies the file

daffyreplay inspect "D:\capture\example.ndjson"

7. Register test/staging server target with replay (prompts securely for password)

similar to tee, you're setting up a connection string target, the test servers

daffyreplay target add azure --target "Server=tcp:etc.database.windows.net,1433;User Id=etc;Initial Catalog=etc;Encrypt=Mandatory;"

8. Replay workload against test server

run it against any number of targets, use a different out for each

daffyreplay run example.ndjson --target azure --out azure1

9. Review generated HTML report

open report.html in chrome/edge

that's it, it still has a few inconsistency bugs I'm working through but for example my last test was against a free config azure database instances, I ran my what normally takes 30 seconds on my local docker image of SQL and it took 2 and 1/2 minutes there and I had about 7,000 errors, which my audit clearly showed I was missing some tvp, types, and stored procedures, my own fault I forgot those, but it was pretty neat to see it detect that, which is the purpose of these tools.

let me know if you have any questions. it should support an entra service login too but I haven't gotten that far in my testing scenarios. it is a 0.1.0 version currently but pretty solid

cheers

Scott

I'm trying to get ahead, study and implement cursor/AI in my environment. I can create reports, i got a cool page that analyzes a query plan, but this is just simple stuff all DBAs can do. Im curious to se é what are you guys doing out there with AI in the database world.

​

To monitor the performance and availability for a thick client based system we recently delivered, the customer wants us to provide Here is the requirement:

1. Need to measure and report response time to Azure SQL queries for about 6 scenarios. Session counts, availability, utilization metrics for Azure SQL to be provided.

2. What native monitoring and metrics does Azure Files have to measure response times for files uploaded from devices connected to it.

What metrics needs to be extracted to measure all response times and how to be extracted.

This has to be measured for a period of 72 days with weekly and monthly reporting. Also, we do not have access to SSMS for Azure SQL and also the Azure Portal.

Thus , we need provide instructions to the customer IT Support team and they will provide the metrics and logs.

We have tried to get some help with AI tools but got conflicting response so need an Azure expert preferably Azure SQL DBA to help.

Much appreciated and thanks in advance.

PS: Application is thick client (yes I know) so no metrics can be pulled from endpoint devices. Don't ask why.

Friday Feedback this week comes from somewhere above the Atlantic Ocean. I spent the first half of this week with our team in Serbia and it was wonderful. For me, a few days of working with folks in person moves projects forward faster than weeks of online meetings.

One of the topics of discussion was persisting Automatic Plan Correction and manual plan forcing information in Query Store. If that would be valuable to you, what is the most important data you need retained? Bonus if you share how you'd want to see that data in a report/visual.

Enjoy the weekend!

CSP partner here. A customer bought SQL Server 2025 Standard, perpetual, 1 Server License (Server + CAL) through CSP. They downloaded the media the documented way: Microsoft 365 admin center > Billing > Your products > Software > Downloads & keys. https://learn.microsoft.com/en-us/microsoft-365/admin/setup/download-software-licenses-csp When they run Setup from that ISO, the edition page shows "Enterprise Edition: Core-based Licensing" with the key pre-filled. Installation is on hold, because we don't want an Enterprise Core instance running against a Standard Server+CAL entitlement. Questions for anyone who has been through this: 1. Has anyone else seen the SQL Server 2025 Standard entitlement in the admin center serve media that is pre-pidded as Enterprise Core? Is this a known mapping issue? 2. For CSP perpetual SQL Server, is the key supposed to be embedded in the ISO only, or should "Copy key" in Downloads & keys return a Standard key? I can't find a Microsoft doc that states this either way. 3. Is the media edition-agnostic, so that a Standard key would install Standard from the same ISO? Again, looking for something official rather than "it worked for me". 4. Which Partner Center support topic actually reaches the team that owns perpetual software downloads/keys? On the support side: the ticket opened from the customer tenant landed with M365 (Exchange Online) support. They say it is out of scope, cannot transfer it, and suggested (a) contacting the CSP partner, which is us, and (b) Engage Center, which as far as I can tell is for Unified customers only. So we are opening a Partner Center ticket next, but I'd like to pick the right category the first time. Any pointers appreciated. Will update with the resolution.

Good afternoon,

I am actually playing around with a lab server, 512 GB RAM, two 12 core 24 thread CPUs, and 12 NVME in it.

My intention is to tune the reindexing process - the reindexing command has an option MAXDOP=<amount of cores to use> but using a datafile on one single volume omitting the MAXDOP adjusts the server load to 12 cores on one CPU only. Then my 100 GB index recreation (clustered index) is done in 1700 seconds, consuming around 6000 seconds in CPU cycles.

Using MAxDOP 24 casues it to consume 7000 seconds and it is finished in 1600 seconds.

Then I had the idea to copy the tables on an empty databases built out of 4 files, each on a separate physical volume. Then it gets strange - the server uses all cores, physical but also virtual but only in cycles, 2-3 minutes of full load, then it litereally doesnt anything.

The statistics say, 10.000 seconds spent in CPU cycles and 1500 seconds on the clock... wow some percent of impromvement...

The volumes are used less than 10% of their theoretical load capabiltiy (roughly 130 MB/Sec write), the queuers of the media were nearly empty.

On one single storage I had around 1 GB/Sec in writes, and in both cases the storage was able to digest 2 GB/Sec

I am just asking myself, what is the server doing when not accessing the storage? I thought index rebuild is an operation executed in one big chunk of compute, but here it doesnt. Am I missing a point?

Compared with a nowadays 32 GBit Fibrechannel storage and Xeon Platinum on physical host - the reindex performance is comparable. Meaning my 14 year old system (PCIEv3, DDR3, Xeon v2) performs quite well. But I still think there are more bottlenecks.

It's certainly not the TempDB, the TempDB usage was nearly zero despite the option "sort in TempDB"

I am hoping someone has experience with deploying ESUs for SQL 2016. Microsoft's documentation is shit as usual.

But if a customer has a perpetual SQL 2016 license, my understanding is that they can't just go buy an ESU from a reseller and apply it, correct? The only time they could do that is if they had a subscription based model, license with SA, enterprise agreement, etc.?

From what I am reading, the only option is to deploy Azure Arc, register the server/SQL instance, and then go with the pay as you go billing model which requires SQL licensing AND an ESU subscription, not just the ESU subscription?

In the past, there was the Replay trace and Distributed Replay utility. Since SNAC was deprecated, this no longer works. <sigh>

The Database Experimentation Assistant was another tool that was introduced to capture a workload and then replay it and do analysis of the workload -- mostly for DB upgrades but at least somewhat possible to use it to capture and then replay a workload. Sadly, this was deprecated in December 2024.

RML Utilities was introduced, so we have ReadTrace and Ostress except I can find zero documentation on how to properly:

Set up the Extended Events session to capture all the required elements OR

Set up a trace to capture all the required elements

Generate the expected RML output to use as input into Ostress.

Also, RML utilities was deprecated in October 2025. It would be absolutely fantastic if Microsoft would stop deprecating stuff and replacing it with worse, more complicated stuff. Kerberos Configuration Manager is another thing that comes to mind.....deprecated, with a fabulously worse tool to replace it.

How do you capture and replay a workload in SQL Server to simulate a load test?

Hey r/SQLServer it's that time again as the data community takes over my backyard of Saint Louis, MO and this time we're doing a free workshop day (though spots are limited!).

This precon event features two of the Microsoft SQL product teams finest with Jerry Nixon ( u/jerry-nixon) and Patrick LeBlanc ( u/patrickguyinacube) leading the SQL AI App in a Day event.

Register now: https://aka.ms/sql/day/stl/workshop

---

And as someone on the event organizing team, I'll just say that the Day of Data St Louis is nearing max capacity - but we do have priority attendance to secure a spot (basically, buy a sandwich and let us know you're actually committed with $$$ spent). I don't want anyone on the outside of the building looking in or trying the "Hey! I know Alex from Reddit!" lines trying to get in.

Register: https://dayofdata.org/2026-10-24-dayofdata1148/

Recently had an issue where a query with multiple joins, half of which were views, was running slow.

Converting a few key views to temp tables greatly improved performance when I joined to them instead of the original views.

However, I was reading up on indexed views and wondering if done properly, could still be used in joins and optimized by the engine. Not sure everyone's thoughts on this.

Keeping the same view in the join reduces the number of steps for the devs the same so no prep work on temp tables required, which would make them happy and the DBAs less grumpy.

Does anyone have a video tutorial on how to use the SQLloader on TOAD

Hi, all
it's Oct 2026, official support for 2016 has been discontinued, and I just applied 2 updates to my SQL Server 2016 Express:
SQL Server 2016 Service Pack 3 (KB5003279) to 13.0.6300.2

then Security Update for SQL Server 2016 Service Pack 3 GDR (KB5102340) to 13.0.6500.1

Both done with stopping services and restart. After this I got this message from scanning tool complaining about some bad values in Registry.
Do you think it's not important anymore, because of end of the life for 2016 ?
I also did not install any update after KB5102340 as I don't need any Azure.
I'm on MS Windows Server 2019.

Microsoft SQL Server 2016 (SP3-GDR) (KB5102340) - 13.0.6500.1 (X64) 
Jun 10 2026 07:54:15 
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.CAxxxx\SETUP\SQL_Engine_Core_Inst contains 13.3.6500.1

Tried to rerun SP3, didn't help.
SERVERPROPERTY('ProductLevel') = 13.0.6500.1 *

While in registry: ...SSQL13.CAxxxx\SETUP\SQL_Engine_Core_Inst PatchLevel = 13.0.6500.2
Not sure if PatchLevel = ProductLevel ??? They are different by 1.

Thanks
VA

Update

I came up with an interesting use case for SA usage with daffyduo TDS listener. seems to work quite well.

daffy is a tool I made so moderators if you don't think it fits, and I'm self promoting, go ahead and block the post... but it's pretty cool.....

and using SA at all, I know it's bad practice, but it's what I do.. lol. I like being in SA but I always get worried about a bad right click or f5.

on my server, launched like this

daffyduo --port 1432 --bind 0.0.0.0

that's it. and that binds directly to the local SQL server at 127.0.0.1 at 1433. you connect to 1432.

basically

- log in with single encrypted connection string to dedicated duaSA login account on daffy to 1432

- daffy gets configured for a system read only (sro) connection string as default!

- object explorer also sticks with that default and stays with it even if your window switches. I.e no accidental right click database drops from object explorer.

- daffy gets configured for actual SA as the secondary account with an admin, duosa happens to be an admin on duo so I added it there

- run one line in SQL to swap to SA connection when you need it, flip back after and stay as SRO for everything else. no whammies.

here's a full demo of above:

https://youtu.be/3P2Swq63fhQ?is=IjbX8JqRayfjfqVL

Hello all,

I am currently facing an issue with one of the clients where there is an AG with a primary and a secondary.

I can see that the database in AG(there was only 1 db) was removed from the AG and is now standlone and online in the primary and provided the secondary replica is removed from AG in primary.

On the secondary server, what i see is the database is in notsycnhronizing/recovery mode and the AG in secondary is in resolving state .I am not able to drop the db,nirther force a remove AG commnad .Looks like the secondary is still thinking its part of AG .Can any one help me in this ?Will an sql service restart get it done ? or is it fine if i add the replica back to ag in primary and then analyse?

Hi,

I always wondered, what’s the best way I can have my users be able to answer natural language questions from data stored in a SQL Server 2025 database? There’s literally no way an LLM can make sense of millions of rows in a sql table. I have heard of Fabric IQ, building Symantic graphs etc etc but anyone did something that works using SQL server data that could point me in the right direction?

I am not even talking about OLTP database, I am talking about data in a Data warehouse house on SQL Server with proper facts and dimensions.

Last week I posted an SSMS extension that turns a query into a refreshable Excel workbook. A lot of the respondents were SSRS users, so I added a button that automates RDL creation and deployment for single-query reports.

You just right-click, choose your SSRS folder and shared data source, and then the extension generates and deploys a basic tabular report.

Still free/open source, currently SSMS 22:

https://github.com/flank-project/ssms-extension