SQL Server Wait Types - Professional SQL Server 2005 Performance Tuning - page 86

committed value (version) when a resource is locked for update, which can dramatically reduce locking
and deadlocking problems without the need to change any applications. For inserts, updates, and deletes,
there is no behavior change and locks are acquired as normal.

As Table 4-1 earlier stated, the full snapshot isolation level supports repeatable reads and doesn't allow
phantoms. The downside is that update conflicts can occur when the data to be changed inside a snapshot
transaction is changed concurrently by another transaction. Conflicts are detected automatically though,
and the snapshot transaction is rolled back to prevent a lost update.

Versioned rows have an overhead of 14 bytes (see Figure 4-9), which is used to store an instance-wide
unique identification of the transaction that made the change called XSN and a row identifier (RID). Only
the modified rows are stored, not the entire page. Rows from different tables may be stored together on
the same page in the version store. Multiple versions of a row are tracked by the incremental XSN.

Version overhead

Version overhead

Data Row XSN RID

Data Row XSN RID

6 bytes 8 bytes

Figure 4-9

The version store is in TempDB so if you do switch to using snapshots make sure that you have it
sized well and on a fast disk subsystem. Snapshot isolation can benefit applications that have heavy
read/writer blocking but if there isn't much on the system before switching then the overhead of
versioning may prove to affect performance negatively.

Monitoring for Deadlocks
By default deadlock messaging is quite minimal. Deadlocks are reported back to the deadlock victim
with the following error message, but nothing is written to the SQL Server errorlog.

Msg 1205, Level 13, State 51, Line 1
Transaction (Process ID 52) was deadlocked on lock resources with another pro-
cess and has been chosen as the deadlock victim. Rerun the transaction.

If you experience frequent deadlocking, you can enable trace flag 1222 , which prints full deadlock
information to the error log ( -1 means apply the flag to all connections):

DBCC traceon (-1,1222)

Trace flag 1222 is new to SQL Server 2005. It enables a more complete deadlock detection output than
the 1204 flag that was used in SQL Server 2000, although both will work in SQL Server 2005.