The standard pattern for getting the first day of the month is:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

Stick to that pattern, don't corrupt it with -1 tricks!  That just makes it more...

Apparently, the morning coffee hasn't kicked in for me yet today... what would the CREATE...

Or re-create the index so that it allows multiple NULLs but no other dups.

select s.c1,s.c2,s.c3,  COUNT(*)*IIF(m.c1 IS NULL,0,1) cnt
  from stg_tbl s
  left join main_tbl m 
         on...

But my god MSDTC can play havoc with you when you do remote updates.

Yes, quite true.  Thus, when you can, with relatively low amounts of data, don't do remote updates. ...

Replication has far more headaches than a simple linked server.  I'm not against replication when it's really needed, of course, but I don't see that need here.  I, too, would...

Ok, for a fellow DBA.  I actually "stole" this from a Paul White article.  To be honest, I wasn't aware that the cursor setting prevented allocation scan reads until I...

You can get phantom reads and repeat reads using the default iso level of READ COMMITTED.  The only thing NOLOCK adds is dirty reads.  And you can greatly reduce the...

I do use SET DATEFORMAT as needed.  I agree, it's much easier to do that than to try to rewrite a script.  Then reset it asap to its original value...

I'd strongly advise against messing with DATEFIRST setting.  The code below works under any/all DATEFIRST settings.

declare @date_to_calc_week_of date
set @date_to_calc_week_of = '20191028'

;with cte_date_calcs as (
   ...

You don't need multiple counts.

DROP TABLE IF EXISTS #actions;
CREATE TABLE #actions ( action nvarchar(10) NULL )

MERGE
...
OUTPUT $ACTION into #actions
...

DECLARE @insert_count int
DECLARE @update_count int

SELECT @insert_count = SUM(CASE WHEN...

Keys locks are normal with a clustered index (in fact, they must be key locks, since you can't get rid locks on a ci).  But I wouldn't expect that many...

I think something like this is what you need:

select @v1 = max(case when type = 1 then amt end),
@v2 = max(case when type = 2 then amt...

No, doesn't sound normal.  How did you determine the locking that was occurring?  Does the table have a lot of partitions?

No, that is purely the physical name.

On a cluster, SERVERPROPERTY('MachineName') shows the instance name.

For a non-clustered named instance, if that doesn't show the instance name, then use:

SERVERPROPERTY('InstanceName')