What "best practices"?  Can you elaborate on these, and where you learned these?

The block sizes and an availability group do not matter. You can certainly have one server/disk with a block sized differently than the other server.

He also was only testing a table...

No, it doesn't, actually.

Using ID "A1" in your example, there are 2...

• This reply was modified 3 years, 5 months ago by Michael L John.

How do you persuade your...

Remember, there is no such thing as a nested transaction.

If there are begin/commit/rollback in either of those procs, the final commit/rollback will not occur until the "parent" proc.

I had...

By removing this data, are you destroying data that may need to be preserved?  The data you want to remove looks like notes of some sort.

While you may want to...

-38.  Wow. The coldest I was ever out in -13 on a camping trip.  In a tent.

It's on it's way.  I have my blankets and footsie warmers ready.

[/quote]Which version of SQL Server are you using?[/quote]

2019. My laptop is 32 GB ram with an 8 core i7 processor

Well, here we go.

I used this to load the table. Took a few milliseconds

 INSERT INTO #Big (id, Dictid)
 SELECT TOP (1000000)
     ...

Change it to a table valued function.

Thanks for taking the time to post feedback, JJ.  It IS interesting to see which hoops and how high the flames are that other people have to go through.

And...

Whew. I have a headache just reading this.

Here is one statement that may help with the cost justification. "It we cannot afford a support call to MS because of cost,...

Funny you should bring that up.  That was *exactly* my first response to our Help Desk.  The answer I got back was that our Help Desk has no resources...