|
|
| |
Update Methods Used in SQL Server 6.5
Alexander Chigrik
chigrik@mssqlcity.com
General Concepts
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]=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
On-Page Delete/Insert Method
This method of modification is used when In-place update could normally be used, but one or more of the following conditions are
present which prevent it from being used:
- There is update trigger on the updated table.
- The updated table participates in the replication process.
- The size of the record was changed.
Here is an 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
|
These are the results of the above code:
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 from the above results that the above code produced an
"update" query, that the update mode is "direct," and the "On-page delete/insert" method of modification is used. Look at the last four rows from the transaction
log below. In this case, when a single row is updated, four rows are written to the transaction log. Compared
to the previous update method, more overhead is required for the On-page
delete/insert method.
- BEGIN TRANSACTION
- Delete Row
- Insert Row
- COMMIT TRANSACTION
Full Delete/Insert
This method of modification is used with the Direct Update mode when there is no room to insert a new row on the updated page. To deal with this, SQL Server 6.5 must create a new row on a new
page, which incurs the most overhead of all of the three update methods
supported by the Direct Update mode. This method of modification is always used with Deferred
Updates, which is discussed below.
Deferred Updates
When a Deferred Update is used, the rows are placed into the transaction log with
the appropriate 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. Once it finishes the delete operation, it applies the
insert operation, and only after that rows are placed on a data page.
Rules for a Deferred Update to occur, include:
- The UPDATE affects the column(s) that participates in a clustered index.
For multi-row 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 non-clustered index.
- The updated column(s) participates in a non-unique, non-clustered index, if the index used to find rows contains updated column(s).
Here is an 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
|
Here are the results of the above code:
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 from the results above, that in this case the type of query is "UPDATE," the update mode
is "deferred" and the "full delete/insert" method of modification is used. The above example produces 14 rows in the translation log, making this update method the most costly of all.
- 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
As you can see, the different update methods used in SQL Server can
significantly affect SQL Server's overhead and performance. The moral of
this story is that if at all possible; try to write your code so as to
use an update method with as small as overhead as possible. Of
course this is not always possible, but the more you know about how
SQL Server 6.5 updates records, the better prepared you are to write
code to take advantage of how it works internally.
|
|
|
