How Updates are Performed in SQL Server 6.5

SQL Server 6.5 can use two different methods to update records, depending on the circumstances. They include:
  • 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]=0×1

(1 row(s) affected)

STEP 1
The type of query is SETOFF
TRAN_ID LOG_RECORD 
————– ———- 


0×380300000000 0 
0×380300000000 9 
0×380300000000 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…

Pages: 1 2




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |