Atrocious performance joining INSERTED and DELETED tables in a trigger

You could evaluate using explicit INNER MERGE JOIN or INNER HASH JOIN hints but given that you are presumably using these tables again later in the trigger you are probably better off just inserting the contents of inserted and deleted tables into indexed #temp tables and being done with it.

They do not get useful indexes created for them automatically.

I know this has been answered but it just popped up as recently active and I have run into this as well for tables with many millions of rows. While not discounting the accepted answer, I can at least add that my experience shows that a key factor in Trigger performance when doing similar tests (seeing if one or more columns have actually had their values changed) is whether or not the column(s) being tested were actually part of the UPDATE statement. I found that comparing columns between the inserted and deleted tables that were in fact not part of the UPDATE statement put a huge drag on performance that was otherwise not there if those fields were part of the UPDATE statement (regardless of their value actually being changed). Why do all of that work (i.e. a query to compare N fields across X rows) to determine if anything has changed if you can logically rule out the possibility of any of those columns being changed, which is obviously not possible if they were not present in the SET clause of the UPDATE statement.

The solution that I employed was to use the UPDATE() function which only works inside of Triggers. This built-in function tells you if a column was specified in the UPDATE statement and can be used to exit the Trigger if the columns that you are concerned about are not part of the UPDATE. This can be used in conjunction with a SELECT to determine if those columns, assuming that they are present in the UPDATE, have actual changes. I have code at the top of several audit triggers that looks like:

-- exit on updates that do not update the only 3 columns we ETL
IF (
     EXISTS(SELECT 1 FROM DELETED) -- this is an UPDATE (Trigger is AFTER INSERT, UPDATE)
     AND (
            NOT (UPDATE(Column3) OR UPDATE(Column7)
                 OR UPDATE(Column11)) -- the columns we care about are not being updated
            OR NOT EXISTS(
                        SELECT 1
                        FROM INSERTED ins
                        INNER JOIN DELETED del
                                ON del.KeyField1 = ins.KeyField1
                                AND del.KeyField2 = ins.KeyField2
                        WHERE ins.Column3 <> del.Column3
                                 COLLATE Latin1_General_100_CS_AS -- case-sensitive compare
                        OR    ISNULL(ins.Column7, -99) <> 
                                 ISNULL(del.Column7, -99) -- NULLable INT field
                        OR    ins.[Column11] <> del.[Column11] -- NOT NULL INT field
                      )
          )
    )
BEGIN
    RETURN;
END;

This logic will proceed to the rest of the trigger if:

1. The operation is an INSERT
2. At least one of the relevant fields is in the SET clause of an UPDATE and at least one of those columns in one row has changed

The NOT (UPDATE...) OR NOT EXISTS() might look odd or backwards, but it is designed to avoid doing the SELECT on the inserted and deleted tables if none of the relevant columns are part of the UPDATE.

Depending on your needs, the COLUMNS_UPDATED() function is another option to determine which columns are part of the UPDATE statement.

I might try to rewrite using if exists

IF EXISTS (SELECT TOP 1 i.CUSTNMBR     
            FROM INSERTED i         
            INNER JOIN DELETED d             
            ON i.CUSTNMBR = d.CUSTNMBR and d.custclass = 'Misc'  
            WHERE d.CUSTCLAS <>i.CUSTCLAS)    
BEGIN

--do your triggerstuff here
END