.WRITE clause performance optimization I have found that you can do high performance updates on varchar(max),nvarchar(max),varbinary(max) columns using the .WRITE clause(example here and here). Unfortunately, there is nothing mentioned about performance optimization in the official documentation.

Could anyone tell is there any optimization at all? Has anyone made some performance test? Also if there is optimization I guess it will affect only specific situations, too.
The WRITE method is minimally logged. If you use regular UPDATE statement, it would result in overwriting the entire string using FULL LOGGING. This would become inefficient when dealing with large updates.

To support update for large value data types, the UPDATE syntax supports .WRITE method. This will result in less Transaction log due to its nature of minimal logging - including insert or appending new data.

Note : The UPDATE using WRITE method will fail if the target LOB is NULL.

Below is a quick and dirty repro showing the transaction log generated using REGULAR UPDATE and UPDATE using .WRITE method.

--- create table

create table dbo.testWRITEUpdate (
    ID int identity(1, 1)
    ,COMMENTS varchar(MAX) not null
    )
go

insert into dbo.testWRITEUpdate (COMMENTS)
values (REPLICATE(CAST('KIN' as varchar(max)), 9000))

checkpoint
go


--- Regular Update:

set statistics io on
 begin tran
update dbo.testWRITEUpdate
set COMMENTS =  STUFF(COMMENTS, 9, 55, '$$$$$(((())))))))))))))____GarbageData____ Entered ____')
where ID  =1

set statistics io off
go

commit tran


-- stats IO output 

 Table 'testWRITEUpdate'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 15, lob physical reads 0, lob read-ahead reads 6.


-- Transaction Log Generated --> 6 Log records with 54580 bytes:



Update using WRITE method :

begin tran
set statistics io on
update dbo.testWRITEUpdate
set COMMENTS.WRITE('$$$$$(((())))))))))))))____GarbageData____ Entered ____',9,55)
where ID  =1
set statistics io off

commit tran
go


-- Stats IO 

Table 'testWRITEUpdate'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 1, lob physical reads 0, lob read-ahead reads 0.


-- Transaction Log Generated --> 3 log records with 464 bytes:



The above test proves that the WRITE method provides more benefit in terms of Transaction log generated as it is minimally logged operation + incurring less logical reads.

Note: This is just scratching the surface, there can be more tests done to actually prove in terms of performance if WRITE method is performing better than the regular UPDATE especially when dealing with VARCHAR, NVARCHAR OR VARBINARY datatypes.



Update (As requested by OP):

Below is the query for finding the sys.dm_tran_database_transactions

SELECT [database_transaction_log_bytes_used] FROM sys.dm_tran_database_transactions
WHERE [database_id] = DB_ID ('test_kin');
GO


--- or a more detailed version :

SELECT DTST.[session_id], 
 DES.[login_name] AS [Login Name], 
 DB_NAME (DTDT.database_id) AS [Database], 
 DTDT.[database_transaction_begin_time] AS [Begin Time], 
 -- DATEDIFF(ms,DTDT.[database_transaction_begin_time], GETDATE()) AS [Duration ms], 
 CASE DTAT.transaction_type 
   WHEN 1 THEN 'Read/write' 
    WHEN 2 THEN 'Read-only' 
    WHEN 3 THEN 'System' 
    WHEN 4 THEN 'Distributed' 
  END AS [Transaction Type], 
  CASE DTAT.transaction_state 
    WHEN 0 THEN 'Not fully initialized' 
    WHEN 1 THEN 'Initialized, not started' 
    WHEN 2 THEN 'Active' 
    WHEN 3 THEN 'Ended' 
    WHEN 4 THEN 'Commit initiated' 
    WHEN 5 THEN 'Prepared, awaiting resolution' 
    WHEN 6 THEN 'Committed' 
    WHEN 7 THEN 'Rolling back' 
    WHEN 8 THEN 'Rolled back' 
  END AS [Transaction State], 
 DTDT.[database_transaction_log_record_count] AS [Log Records], 
 DTDT.[database_transaction_log_bytes_used] AS [Log Bytes Used], 
 DTDT.[database_transaction_log_bytes_reserved] AS [Log Bytes RSVPd], 
 DEST.[text] AS [Last Transaction Text], 
 DEQP.[query_plan] AS [Last Query Plan] 
FROM sys.dm_tran_database_transactions DTDT 
 INNER JOIN sys.dm_tran_session_transactions DTST 
   ON DTST.[transaction_id] = DTDT.[transaction_id] 
 INNER JOIN sys.[dm_tran_active_transactions] DTAT 
   ON DTST.[transaction_id] = DTAT.[transaction_id] 
 INNER JOIN sys.[dm_exec_sessions] DES 
   ON DES.[session_id] = DTST.[session_id] 
 INNER JOIN sys.dm_exec_connections DEC 
   ON DEC.[session_id] = DTST.[session_id] 
 LEFT JOIN sys.dm_exec_requests DER 
   ON DER.[session_id] = DTST.[session_id] 
 CROSS APPLY sys.dm_exec_sql_text (DEC.[most_recent_sql_handle]) AS DEST 
 OUTER APPLY sys.dm_exec_query_plan (DER.[plan_handle]) AS DEQP 
ORDER BY DTDT.[database_transaction_log_bytes_used] DESC;
-- ORDER BY [Duration ms] DESC;