SQL Update with join involving NVARCHAR(MAX) column balloons TEMPDB

The following query causes the SQL Server 2014 TEMPDB.MDF to grow to 40Gb and takes about an hour to run.

Similar queries for columns that are not NVARCHAR(MAX) take a few minutes.

The table being updated has approximately 7 million rows.

Adding the AND C.SQLStmt IS NOT NULL to the end improved things to get to this point.

Any help would be appreciated.

UPDATE R 
SET    R.SQLStmt = C.SQLStmt 
FROM SampleResults R 
  JOIN SampleTests T ON T.SampleCode = R.SampleCode 
                    AND T.TestPosition = R.TestPosition 
  JOIN TestComponents C ON T.TestCode = C.TestCode 
                       AND T.TestVersion = C.AuditNumber 
                       AND R.ComponentColumn = C.ComponentColumn 
                       AND R.ComponentRow = C.ComponentRow 
WHERE T.AuditFlag = 0 
AND   R.AuditFlag = 0 
AND   C.SQLStmt IS NOT NULL

This is following on from this question, which I don't have enough reputation to comment on.

The execution plan is:

Update
Cost: 0%
 |
Cluseterd Index Updated
[SampleResults].[pk_SampleResults]
Cost: 27%
 |
Top
COST: 0%
 |
Sort
(Distinct Sort)
Cost: 31%
 |
Nested Loops   Index Seek (NonClustered)
(Inner Join) - [SampleTests].[SampleTestsAuditFlag...
Cost: 0%       Cost: 10%
 |
Nested Loops   Key Lookup (Clustered)
(Inner Join) - [SampleResults].[pk_SampleResults]...
Cost: 0%       Cost: 15%
 |
Nested Loops   Index Seek (NonClustered)
(Inner Join) - [SampleResults].[SampleResultsCompo...
Cost: 0%       Cost: 9%
 |
Filter
Cost: 0%
 |
Clustered Index Scan (Clustered)
[TestComponents].[pk_TestComponents...
Cost: 9%

Solution

Because the maximum size of the actual data stored in the column was only 392 characters, the final solution was:

1) Temporarily alter the size of both the source and destination columns (SampleResults.SQLStmt and TestComponents.SQLStmt) to 392 characters. 2) Do the update. 3) Alter the size of both the source and destination columns to their new more sensible size of 4000 characters.

DECLARE @tempSQLStmtLength SMALLINT = (SELECT MAX(LEN(SQlStmt)) FROM TestComponents WHERE SQlStmt IS NOT NULL)
IF @tempSQLStmtLength IS NOT NULL AND @tempSQLStmtLength > 0
BEGIN
  -- Temporarily reduce size of SQLStmt columns so that update is as quick as possible.
  EXEC('ALTER TABLE SampleResults ALTER COLUMN SQLStmt NVARCHAR(' + @tempSQLStmtLength +') NULL')
  EXEC('ALTER TABLE TestComponents ALTER COLUMN SQLStmt NVARCHAR(' + @tempSQLStmtLength + ') NULL')

  -- Perform update.
  UPDATE R SET R.SQLStmt = C.SQLStmt FROM SampleResults R JOIN SampleTests T ON T.SampleCode = R.SampleCode AND T.TestPosition = R.TestPosition JOIN TestComponents C ON (T.TestCode = C.TestCode OR T.TestCode + '-' + CAST(T.TestPosition AS VARCHAR(5)) + '-' + T.SampleCode = C.TestCode) AND T.TestVersion = C.AuditNumber AND R.ComponentColumn = C.ComponentColumn AND R.ComponentRow = C.ComponentRow WHERE T.AuditFlag = 0 AND R.AuditFlag = 0 AND C.SQLStmt IS NOT NULL

  -- Now that update is finished, set SQLStmt columns to their final new size.
  ALTER TABLE SampleResults ALTER COLUMN SQLStmt NVARCHAR(4000) NULL
  ALTER TABLE TestComponents ALTER COLUMN SQLStmt NVARCHAR(4000) NULL
END;

TEMPDB.MDB only grew to 2.8Gb and took a few minutes.