Here is an example you can using the pubs database that demonstrates how you can view the type of update that has occurred.
USE pubs
GO
DBCC TRACEON (3604)
GO
DBCC TRACEON (323)
GO
SET SHOWPLAN ON
GO
UPDATE stores SET state = ‘UT’ WHERE stor_id = ‘6380’
GO
SET SHOWPLAN OFF
GO
SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs
GO
The above code results in the following:
STEP 1
The type of query is UPDATE
The update mode is direct
FROM TABLE
stores
Nested iteration
Using Clustered Index
TO TABLE
stores
Update: in-place, clust, safeind[0]=0x1
(1 row(s) affected)
STEP 1
The type of query is SETOFF
TRAN_ID LOG_RECORD
————– ———-
…
0x380300000000 0
0x380300000000 9
0x380300000000 30
You can see from the results that in this case the type of query is “UPDATE,” the update mode is “direct” and that the “In-place” method of modification is used. Look at the last three rows from the transaction log below. Notice that when the In-place update method is used, that only one record is updated and that only three rows are placed into the transaction log:
– BEGIN TRANSACTION
– Modify Row
– COMMIT TRANSACTION
On-Page Delete/Insert Method
This method of modification is used when In-place update could normally be used, but one or more of the following conditions are present which prevent it from being used:
- There is update trigger on the updated table.
- The updated table participates in the replication process.
- The size of the record was changed.
Here is an example:
USE pubs
GO
DBCC TRACEON (3604)
GO
DBCC TRACEON (323)
GO
SET SHOWPLAN ON
GO
UPDATE jobs SET job_desc = ‘Updated row’ WHERE job_id = 1
GO
SET SHOWPLAN OFF
GO
SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs
GO
These are the results of the above code:
STEP 1
The type of query is UPDATE
The update mode is direct
FROM TABLE
jobs
Nested iteration
Using Clustered Index
TO TABLE
jobs
Update: on-page delete/insert, clust, safeind[0]=0x1
(1 row(s) affected)
STEP 1
The type of query is SETOFF
TRAN_ID LOG_RECORD
————– ———-
…
0x3b0300001800 0
0x3b0300001800 5
0x3b0300001800 4
0x3b0300001800 30
You can see from the above results that the above code produced an “update” query, that the update mode is “direct,” and the “On-page delete/insert” method of modification is used. Look at the last four rows from the transaction log below. In this case, when a single row is updated, four rows are written to the transaction log. Compared to the previous update method, more overhead is required for the On-page delete/insert method.
– BEGIN TRANSACTION
– Delete Row
– Insert Row
– COMMIT TRANSACTION