Full Delete/Insert
This method of modification is used with the Direct Update mode when there is no room to insert a new row on the updated page. To deal with this, SQL Server 6.5 must create a new row on a new page, which incurs the most overhead of all of the three update methods supported by the Direct Update mode. This method of modification is always used with Deferred Updates, which is discussed below.
Deferred Updates
When a Deferred Update is used, the rows are placed into the transaction log with the appropriate no-op states (“Deferred Update step 1 delete record” and “Deferred Update step 1 insert record” states), then SQL Server returns to the beginning of the transaction and starts applying the delete operations. Once it finishes the delete operation, it applies the insert operation, and only after that rows are placed on a data page.
Rules for a Deferred Update to occur, include:
- The UPDATE affects the column(s) that participates in a clustered index.
For multi-row updates:
- The UPDATE affect the nullable column(s).
- The UPDATE affect the column(s) with variable length.
- The table includes a column with the timestamp datatype.
- The updated column(s) participates in a unique non-clustered index.
- The updated column(s) participates in a non-unique, non-clustered index, if the index used to find rows contains updated column(s).
Here is an example:
USE pubs
GO
DBCC TRACEON (3604)
GO
DBCC TRACEON (323)
GO
SET SHOWPLAN ON
GO
UPDATE discounts SET lowqty = 100
GO
SET SHOWPLAN OFF
GO
SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs
GO
Here are the results of the above code:
STEP 1
The type of query is UPDATE
The update mode is deferred
FROM TABLE
discounts
Nested iteration
Table Scan
TO TABLE
discounts
Update: full delete/insert, deferred mode, no clust, safeind[0]=0xfe
Update: full delete/insert, deferred mode, no clust, safeind[0]=0xfe
Update: full delete/insert, deferred mode, no clust, safeind[0]=0xfe
(3 row(s) affected)
STEP 1
The type of query is SETOFF
TRAN_ID LOG_RECORD
————– ———-
…
0x140300000f00 0
0x140300000f00 12
0x140300000f00 11
0x140300000f00 12
0x140300000f00 11
0x140300000f00 12
0x140300000f00 11
0x140300000f00 5
0x140300000f00 5
0x140300000f00 5
0x140300000f00 6
0x140300000f00 6
0x140300000f00 6
0x140300000f00 30
You can see from the results above, that in this case the type of query is “UPDATE,” the update mode is “deferred” and the “full delete/insert” method of modification is used. The above example produces 14 rows in the translation log, making this update method the most costly of all.
– BEGIN TRANSACTION
– Deferred Update step 1 delete record
– Deferred Update step 1 insert record
– Deferred Update step 1 delete record
– Deferred Update step 1 insert record
– Deferred Update step 1 delete record
– Deferred Update step 1 insert record
– Delete Row
– Delete Row
– Delete Row
– Deferred Update step 2 insert record
– Deferred Update step 2 insert record
– Deferred Update step 2 insert record
-COMMIT TRANSACTION
As you can see, the different update methods used in SQL Server can significantly affect SQL Server’s overhead and performance. The moral of this story is that if at all possible; try to write your code so as to use an update method with as small as overhead as possible. Of course this is not always possible, but the more you know about how SQL Server 6.5 updates records, the better prepared you are to write code to take advantage of how it works internally.
]]>