How Updates are Performed in SQL Server 6.5

  • Direct Update
  • Deferred Update
  • Each method affects performance in a different way, as we shall see in this article. A Direct Update includes three different methods of modification:

    • In-place update
    • On-page delete/insert
    • Full delete/insert

    Think of these above three methods as different ways to implement a Direct Update. The method selected by SQL Server depends on a variety of factors, which will be discussed later.

    On the other hand, a Deferred Update (discussed in more detail later) always uses the Full delete/insert method of modification.

    If you want to see how SQL Server is performing a particular update, you can if you turn on the SET SHOWPLAN ON statement in ISQL/W and then run the following code to see what commands were written to the transaction log. SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs  WHERE op – is the transaction log operation.

    Here 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” In addition, you can also use the trace flags 3604 and 323 in order to a more detailed description of which Update methods are used by SQL Server 6.5. 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 to see a detailed description of the various update methods used by SQL Server 6.5.

    Direct Update

    A Direct Update performs faster than a Deferred Updated because less SQL Server overhead is incurred, as we shall see. If at all possible, try to produce code that use Direct Updates instead of Deferred Updates for optimum performance.

    For a Direct Update to occur, the following rules must be followed:

    • The UPDATE cannot affect the column(s) that participates in a clustered index.

    And for multi-row updates:

    • The UPDATE cannot affect nullable columns.
    • The UPDATE cannot affect columns with variable length.
    • The table cannot include a column with the timestamp datatype.
    • The updated column cannot participate in a unique non-clustered index.
    • The updated column cannot participate in a non-unique, non-clustered index
      if the index used to find rows contains updated column.

    As we have already discussed, a Direct Update can be implemented by one of three different update methods.

    In-Place Update Method

    Of the three update methods available in a Direct Update, an In-Place Update is the uses the least overhead, and is the fastest to execute. When it is used, the data is modified in its original location, and only one row is written to the transaction
    log with “MODIFY” state. In order for an In-Place Update to occur, the following rules must be followed:

    • The UPDATE cannot affect any columns that participate 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 non-unique non-clustered index only if the index key is a fixed-width column.
    • The updated column(s) can participate in a unique non-clustered index only if the index key is fixed-width and the WHERE clause criteria must have an exact match.
    • The new row cannot include differing bytes by more than 50 percent of the original row size, and the total number of non-contiguous differing bytes is not more than 24.

    For multi-row updates:

    • The updated column must be fixed length.
    • The updated column cannot participate in a unique non-clustered index.
    • The updated column can participate in a non-unique non-clustered 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.

    Here is an example you can using the pubs database that demonstrates how you can view the type of update that has occurred. 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  The above code results in the following: 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 from the results that in this case the type of query is “UPDATE,” the update mode is “direct” and that the “In-place” method of modification is used. Look at the last three rows from the transaction log below. Notice that when the In-place update method is used, that only one record is updated and that only three rows are placed into the transaction log: – BEGIN TRANSACTION
    – Modify Row
    – COMMIT TRANSACTION

     

    Continues…

    Leave a comment

    Your email address will not be published.