Update Methods Used in MS SQL 6.5

Introduction

Direct update

Deferred update

Literature

Introduction

There are two update modes in SQL Server 6.5:
  – Direct update
  – Deferred update
Direct update contains three methods of modification:
  – In-place update
  – On-page delete/insert
  – Full delete/insert
Deferred update mode always uses Full delete/insert method of modification.
To see the update modes you can use SET SHOWPLAN ON statement. To see what
commands were written into the transaction log, you can use the following
statement:
__ATLAS_TABLE_BLOCK__0__
WHERE op – is the transaction log operation.
There are some op values:
op = 0  – is “BEGIN TRANSACTION”
op = 4  – is “Insert Row”
op = 5  – is “Delete Row”
op = 6  – is “Deferred Update step 2 insert record”
op = 9  – is “Modify Row”
op = 11 – is “Deferred Update step 1 insert record”
op = 12 – is “Deferred Update step 1 delete record”
op = 30 – is “COMMIT TRANSACTION”
I use the following trace flags: 3604 and 323 for getting more
detail description of update methods.
Trace flag 3604 sends trace output to the client. This trace
flag is used only when setting trace flags with DBCC TRACEON
and DBCC TRACEOFF.
Trace flag 323 is undocumented trace flag. You can use it, if
you want to see detail description of update methods.

Direct update

More effective then Deffered update. Rules for a Direct update:
  – The UPDATE cannot affect the column(s) that participates in a clustered
index.
For multirow updates:
  – The UPDATE cannot affect the nullable column(s).
  – The UPDATE cannot affect the column(s) with variable length.
  – The table cannot include a column with the timestamp datatype.
  – The updated column cannot participate in a unique nonclustered index.
  – The updated column cannot participate in a nonunique nonclustered index,
    if the index used to find rows contains updated column.
When it is used, rows are placed into the transaction log and then on the
data pages.

In-place update

Is most effective method of modification. When it is used the data changed
on its physical place, and only one row will be written into the transaction
log with “MODIFY” state.
This is from SQL Server Books Online (Rules for an Update-In-Place):
  – The UPDATE cannot affect the column(s) that participates in a clustered
index.
  – The table cannot have an UPDATE trigger.
  – The table cannot be marked for replication.
For single-row updates:
  – The updated column(s) can be variable length, but the new total row size
must fit on the same page as the old row.
  – The updated column(s) can participate in a nonunique nonclustered index
only if the index key is a fixed-width column.
  – The updated column(s) can participate in a unique nonclustered index
only if the index key is fixed-width and the WHERE
    clause criteria must have an exact match (based on an unique index
match_the index could be the same as the updated
    column).
  – The new row cannot include differing bytes by more than 50 percent of
the original row size, and the total number of
    discontiguous differing bytes is not more than 24.
For multirow updates:
  – The updated column must be fixed length.
  – The updated column cannot participate in a unique nonclustered index.
  – The updated column can participate in a nonunique nonclustered index
only if the column is a fixed-width column (the index
    used to find rows cannot be the same as the updated column).
  – The table cannot include a column with the timestamp datatype.
This is the example:
__ATLAS_TABLE_BLOCK__1__
This is the results:
__ATLAS_TABLE_BLOCK__2__
You can see that in this case “The type of query is UPDATE”,
“The update mode is direct” and In-place method of modification
is used. Look at the last three rows from the transaction log,
so when used the In-place update method and only one record is
updated then only three rows will be placed into the transaction log:
  – BEGIN TRANSACTION
  – Modify Row
  – COMMIT TRANSACTION

On-page delete/insert

This method of modification is used when could be used In-place update,
but one or more of the following conditions are present:
  – There is update trigger on the updated table.
  – The updated table participate in the replication process.
  – The size of the record was changed.
This is the example:
__ATLAS_TABLE_BLOCK__3__
This is the results:
__ATLAS_TABLE_BLOCK__4__
You can see that in this case “The type of query is UPDATE”,
“The update mode is direct” and On-page delete/insert method
of modification is used. Look at the last four rows from the
transaction log, so when used the On-page delete/insert method
and only one record is updated then only four rows will be placed
into the transaction log:
  – BEGIN TRANSACTION
  – Delete Row
  – Insert Row
  – COMMIT TRANSACTION

Full delete/insert

This method of modification is used with Direct update mode when
there are no space for new inserted row on the updated page.
SQL Server 6.5 creates new row on the new page in this case. This
method of modification is always used with Deferred update mode.

Deferred update

When it is used, the rows are placed into the transaction log with
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,
after finishes the deletes operations, it applies the inserts operations
and only after that rows are placed on a data page.
Rules for a Deferred update:
  – The UPDATE affect the column(s) that participates in a clustered index.
For multirow 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 nonclustered index.
  – The updated column(s) participates in a nonunique nonclustered index,
    if the index used to find rows contains updated column(s).
This is the example:
__ATLAS_TABLE_BLOCK__5__
This is the results:
__ATLAS_TABLE_BLOCK__6__
You can see that in this case “The type of query is UPDATE”,
“The update mode is deferred” and full delete/insert method
of modification is used. Look at the last 14 rows from the
transaction log, so when used deferred update mode this rows will
be placed into the transaction log:
  – 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

Literature

1. SQL Server Books Online.
2. “Update Methods Used in SQL Server 6.0”
   http://support.microsoft.com/support/kb/articles/Q135/8/71.asp?LN=EN-US&SD=SO&FR=0
3. “Viewing Transaction Log”
   http://www.swynk.com/friends/achigrik/ViewLog.asp

»


See All Articles by ColumnistAlexander Chigrik