Would have to see the query plan to be sure, but it seems like SQL may be doing an implicit conversion on the date column, which would cause big performance...

It seems like maybe something is blocking the "slow" run, although I'm not sure what it would be.

You don't need to do an explicit transaction.  Once you put the level into effect, it will stay in effect until/unless you explicitly change/override it.

SET TRANSACTION ISOLATION LEVEL...

Divide it by 1000, to add seconds, so it seems based on the value returned:

SELECT DATEADD(SECOND, (1551279347054/1000), '19700101')

If you need to keep the milliseconds, you can...

Did you look at / compare the query plans?

The estimated plan will be the actual plan.  What's missing from the estimated plan is the actual row counts; in an estimated plan, naturally the query didn't actually run, so...

Certain SQL settings being different can cause a separate execution plan to be required.  For example, QUOTED_IDENTIFIER ON vs QUOTED_IDENTIFIER OFF would definitely cause that.  Try explicitly setting all of...

If you want to start over on the design, then start over on the logical design process.  The existing table has no relevance to that process.

Lay out the...

INSERT INTO [dbo.Distinct_Login] (
  [Today], [DatabaseName], [lOGIN_TIME], [HOSTNAME],
  [CMD], [NT_USERNAME], [LOGINAME]
  )
select
    GETDATE() AS today, DB_NAME(dbid) AS DatabaseName, LOGIN_TIME, HOSTNAME,

Or have SSIS convert that value to a different date.  Typically 1900-01-01 is used (date 0 in SQL) for that, unless you have actual data that goes back that far,...

That's an odd requirement.  The same login could have several different processes active on SQL at the same time.  How do you want to determine which single one to list? ...

Something like this instead:

delete from C
from ods_tmus4.dbo.lgtc_shipmentsclaims_cpy C
where exists (
select *
from [Admin].[dbo].[Remove Duplicates_Final] D
Where D.[Tracking_Number] =...