Large update taking too long; looks like it is updating more records than I have I am trying to do an update on a table that has 4 million records. The main table is indexed on SerNum a unique field. I use a temp table to update from that also has a unique index on SerNum. I am told this should happen fast but it's taking hours. It's been running for 2 hours and if I add up the rows affected I get 5,260,000 which is weird because I only have 3,869,000 records in the table. The select into the temp table shows it has 3,390,954. Here is what I have so far can this be more efficient? 

USE IMB_TraceData
GO

IF OBJECT_ID('tempdb.dbo.PRIMARY KEY (lname,') IS NOT NULL
    DROP TABLE #MaxDate;
GO

-- ******************************************
-- Create Work Table
-- ******************************************
SELECT * INTO #MaxDate
FROM
    (
    SELECT  *
    FROM    (
            SELECT  *, 
            ROW_NUMBER() OVER (PARTITION BY TR_SerNum 
            ORDER BY TR_Scan_Date DESC) AS rn
            FROM  Trace_Return_Table
            ) a
    WHERE   a.rn = 1
    ) AS n
GO
CREATE INDEX IDX_SerNum ON #MaxDate(TR_SerNum)
GO

DECLARE  @UPDATES TABLE (PK_ID int NOT NULL Primary KEY)
DECLARE @x INT

SET @x = 1
SET ROWCOUNT 10000

WHILE @x &gt; 0
    BEGIN
        BEGIN TRANSACTION
        UPDATE TOP (10000) IMB_TBO_Data.dbo.The_Big_One 
        SET TR_Scan_Date = n.TR_Scan_Date,
            TR_Facility_Id = n.TR_Facility_Id,
            TR_Operation_Code = n.TR_Operation_Code
        OUTPUT inserted.HT_SerNum INTO @Updates
        FROM IMB_TBO_Data.dbo.The_Big_One t
        INNER JOIN #MaxDate n
            ON t.HT_SerNum = n.TR_SerNum
        WHERE NOT EXISTS 
        (SELECT * FROM @UPDATES AS Updates
        WHERE UPDates.PK_ID = t.HT_SerNum)
        SET @x = @@ROWCOUNT
        COMMIT TRANSACTION
    END

SET ROWCOUNT 0
Well, after a few iterations your @Updates table variable probably has become huge (since you're inserting data into it in every iteration ), and this is probably leaking memory. Plus, on every iteration, you're doing an EXISTS check for every single row in the tables which are a product of the join statement, against the @Updates table.

Might I suggest this approach here for the while part of your code, it works because the [rn] column is sequential, so you actually have a index you can just use for the chunk based update

--Keep this original code as is --
USE IMB_TraceData
GO

IF OBJECT_ID('tempdb.dbo.PRIMARY KEY (lname,') IS NOT NULL
    DROP TABLE #MaxDate;
GO

-- ******************************************
-- Create Work Table
-- ******************************************
SELECT * INTO #MaxDate
FROM
    (
    SELECT  RowNum = row_number() OVER (ORDER BY (SELECT 0)),*
    FROM    (
            SELECT  *, 
            ROW_NUMBER() OVER (PARTITION BY TR_SerNum 
            ORDER BY TR_Scan_Date DESC) AS rn
            FROM  Trace_Return_Table
            ) a
    WHERE   a.rn = 1
    ) AS n
GO
CREATE CLUSTERED INDEX IDX_RowNum ON #MaxDate(RowNum)
CREATE INDEX IDX_SerNum ON #MaxDate(TR_SerNum)
GO
-- original code ends --

-- modified code follows --
DECLARE @tRow BIGINT = 0
DECLARE @tMax BIGINT = (SELECT MAX(RowNum) FROM #MaxDate)

WHILE @tRow&lt; @tMax
        BEGIN
          set @tRow = @tRow + 10000
            BEGIN TRANSACTION
            UPDATE t
            SET TR_Scan_Date = n.TR_Scan_Date,
                TR_Facility_Id = n.TR_Facility_Id,
                TR_Operation_Code = n.TR_Operation_Code
            FROM IMB_TBO_Data.dbo.The_Big_One t
            INNER JOIN #MaxDate n ON t.HT_SerNum = n.TR_SerNum
            WHERE n.RowNum between (@tRow - 10000) AND @tRow

            COMMIT TRANSACTION
        END


My bet is, this will run a lot faster.