How Updates are Performed in SQL Server 6.5 – Page 2

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

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.

]]>

Leave a comment Cancel reply