Update Methods Used in MS SQL 6.5

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:





  SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs






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:





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






This is the results:





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 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:





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






This is the results:





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 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:





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






This is the results:





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 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