How is the underlying table clustered?

What the most common WHERE conditions used when you read this table?

The best, most complete solution overall is to best cluster...

I did get less execution time cost using COUNT() rather than COUNT_BIG():

SELECT
DB_NAME(database_id) AS db_name,
COUNT(*) / 128.0 AS buffer_pool_size_mb
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id <> 32767...

Yes.

;WITH cte_dups AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY name ORDER BY id DESC) AS row_num /* keep the HIGHEST/LAST id */
    FROM...

Try it this way:

SELECT *
FROM (
  SELECT Sequence#, Member#, Group#
  WHERE Group# = 'PP0018'
) AS iq1
WHERE Sequence# <> CASE...

How specifically?  That's a lot of LEFT JOINs.  Is it safe to use NOLOCK on those tables?  If so, have you tried that?  (Let...

Yes.  You can say either:

In the old AdventureWorks2016 database
In ye olde AdventureWorks2016 database

but I don't think they should be mixed 😉

Without seeing the actual MERGE, can't get into specifics.

But could you try batching it yourself.  For example, if the table being MREGEd has a unique clustering key, then...

I think it's more clear and more flexible to just test each and add up a "not null" count accordingly:

CASE WHEN
    CASE WHEN...

Maybe this?:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
ALTER Procedure [dbo].[sp_MarkAsLeaver]
    @username varchar(50),
    @LeaverDetails varchar(100),
    @RowsUpdated int output,
   ...