Extended Events. https://www.simple-talk.com/sql/database-administration/getting-started-with-extended-events-in-sql-server-2012/

I suspect it's because you are not the replication process, and hence your inserts are treated normally,

Err, yes, that's what I just said.

What's called a bookmark lookup in SQL 2000 and before...

Bookmark lookup = SQL 2000 and before name. In 2005 and above it's called either a RID lookup or a key lookup.

A lot of people mix up parameters and variables. The code posted has local variables, not parameters.

As written, the cause is likely lack of parameter sniffing (variables can't be sniffed),...

They're probably polling sys.dm_os_waiting_tasks, or have an extended event session on wait_completed

???

It's not Redgate that can decrypt it. The tool just makes it easy to do so. Anyone with sysadmin access can decrypt the procedures.

There's no way to encrypt 'securely'. SQL...

You can't index a varchar(max) and the index on the varchar(800) will only be useful if there's no leading wildcard, and maybe not even then.

sys.stats. sysindexes is deprecated. (yes, it holds most of the info, but has none of the post-SQL 2000 properties)

1) BoL does say there's overhead, and it can be quite nasty. It's IO-subsystem related, so moving snapshots (or the source DB) to a faster drive may help.

2) No, there...

It doesn't have to. Run it when you feel you need to.

So we are...

Cthulhu for president! Why vote for the lesser evil?

Any uncommitted modifications roll back and the connection is terminated. While rolling back, it shows as an active session in ROLLBACK. Whether the SELECT aborts immediately or not I'm not...

It's a system process, You can't kill it and you can't change what it does. Leave it alone.