How Updates are Performed in SQL Server 6.5

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

 

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]=0×1

(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

 

Continues…

Pages: 1 2 3




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 |