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.

    Continues…

    Leave a comment

    Your email address will not be published.