Update Methods Used in MS SQL 6.5



Introduction

Direct update

  • In-place update

  • On-page delete/insert

  • Full delete/insert

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


    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



    »


    See All Articles by Columnist
    Alexander Chigrik

    Alexander Chigrik
    Alexander Chigrik
    I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

    Latest Articles