I agree on everything that Sean has said.
I guess from my part is that your DISTINCT on 125 million rows is generating problems. This might help, and proper indexing should help even more.
SELECT 'OREF' AppCode
,OLSEntitlement.LiveIDEncryptedKey
,'Subscription' ActivationModel
,GETDATE() DMLUpdatedTimestamp
,CAST(Machine.MachineKey AS VARCHAR(50)) COLLATE Latin1_General_CS_AS ActID
,Machine.MachineKey
,Machine.MachineType
,OLSEntitlement.PartnerEntitlementId
,N'No data from PAIntel Activations AND CP,but OLS has data' ExceptionReason
,N'No PAIntel Activations and CP Data' ExceptionType
,0 AS DMLIsDeletedFlag
-- 1696399 (1710621).Get the EntitlementID from extract table instead of exceptions,this takes care to poplate the accumulate table with new EntitlementID
-- even though the first exception record was in old format.Also this will help dedupe script to remove the entitlementid in case of mixed formats.
,Machine.EntitlementId AS EntitlementID
FROM DML.ExtOLSMachine Machine
JOIN DML.ExceptionsTrackingTable OLSEntitlement ON OLSEntitlement.EntitlementId = Machine.EntitlementId
WHERE OLSEntitlement.DMLIsDeletedFlag = 0
AND NOT EXISTS (
SELECT 1
FROM DML.ExceptionsTrackingTable
WHERE ExceptionType IN (
'No PAIntel Activations and CP Data'
,'No PAIntel Activations'
)
AND ActivationModel = 'Subscription'
AND Machine.MachineKey = ExceptionsTrackingTable.ActID
)
AND NOT EXISTS (
SELECT 1
FROM DML.ExtOLSEntitlement Entitlement
WHERE Entitlement.EntitlementId = Machine.EntitlementId
)
For better suggestions, include the execution plan and DDL for tables and indexes.
EDIT: Corrected errors in the code posted.