Microsoft® SQL Server® 2012 Bible

1092

Part VIII: Performance Tuning and Optimization

In the seventh step of a transaction, the code closes the transaction:

COMMIT TRANSACTION

Next, the commit entry is written to the transaction log and the transaction-log

commit entry is confi rmed.

Data-File Update

With the transaction safely stored in the transaction log, the last operation is to write the

data modifi cation to the data fi le, as shown in Figure 47-12.

FIGURE 47-12

As one of the last steps, the data modifi cation is written to the data fi le.

Data Pages

In

RAM

T Data File Write

Data File

T-Log

Delete

Insert

Commit

9) Confirm

8) Write

Ahead

1) Begin in

Consistent State

11) Transaction is

marked as written

to the data file

10) Write when

it gets time,

tag in T-LOG 12)Finish in

Consistent State

4) Write to

Data Page

2) Begin Tran

3) Update

7)Commit Tran

SQL Update

In the background, when a checkpoint occurs (a SQL Server internal event) the lazy writer

runs, or a worker thread initiates the work, SQL Server writes any dirty (modifi ed) data

pages to the data fi le. It tries to fi nd sequential pages to improve the performance of the

write. Even though this is listed here as step 10, this can happen at nearly anytime during

the transaction or after it depending on the amount of data changed and the memory pres-

sure on the system. SQL Server receives a “write complete” message from Windows.

At the conclusion of the background write operation, SQL Server marks the oldest open

transaction in the transaction log. All older, committed transactions have been confi rmed

in the data fi le and are now confi rmed in the transaction log. The DBCC OpenTran com-

mand reports the oldest open transaction.

Transaction Complete

The sequence comes full circle and returns the database to a consistent state, which brings

you to the fi nal step in a transaction: The database fi nishes in a consistent state.

c47.indd 1092c47.indd 1092 7/31/2012 10:23:33 AM7/31/2012 10:23:33 AM

http://www.it-ebooks.info