Summary for Performance Tuning Using SQL Server DMVs Book / Chapter 4:Transactions

Chapter 4: Transactions

• READ COMMITTED: These locks prevent another transaction from modifying that data while the query is in progress, but do not block other readers. In READ COMMITTED mode, shared read locks are released as soon as a query completes, so data modification transactions can proceed at that point, even if the transaction to which the query belongs is still open. Therefore, non-repeatable reads are possible; if the same data is read twice in the same transaction, the answer may be different. If this is not acceptable, the isolation level can be made more restrictive. The REPEATABLE READ level will ensure that all the rows that were read cannot be modified or deleted until the transaction which reads them completes. However, even this level does not prevent new rows (called phantom rows) being inserted that satisfy the query criteria, meaning that reads are not, in the true sense, repeatable. To prevent this, you could switch to SERIALIZABLE, the most restrictive isolation level of all, which basically ensures that a transaction is completely isolated from the effects of any other transaction in the database … P117-118.
• The sys.dm_tran_locks DMV provides information regarding both the resource on which the lock is being held (or has been requested), and the owner of the request. Description of the useful columns in this DMV are listed in P120-122.
• Lock types: SQL Server can lock a number of different types of resource, the most obvious being tables (OBJECT locks), pages (PAGE locks), rows (RID locks), and keys (KEY locks) (other common types in P124), in order of increasing granularity. The more granular the lock, the higher the degree of concurrent access that can be supported. However, with that comes a higher memory overhead, from having to manage a large number of individual locks. SQL Server automatically chooses locks of the highest possible granularity, suitable for the given workload. However, if too many individual locks are being held, SQL Server may use lock escalation to reduce the total number of locks being held. While this will result in lower overhead on SQL Server, the cost will be lower concurrency. If processes are running on your servers that are causing lock escalation, it's worth investigating whether the escalation is justified, or if SQL tuning can be performed to prevent it … P123.
• SQL Server employs five different lock modes, in order to control how concurrent transactions interact. These modes are exposed in the request_mode column of the sys.dm_tran_locks DMV. Lock modes are described in P125. 
• Listing 4.5: Which sessions are causing blocking and what statement are they running. A better one and adds to listing 4.5 is Listing 4.6, it allows investigating locking and blocking based on waiting tasks [need to be fixed: replace “2 AS [blocked_command]” with “2) AS [blocked_command]”]. You can add to resulting columns and get the name of the database by “db_name(DTL.[resource_database_id]) DatabaseName”.
• Listing 4.14: identifies all CURRENT ACTIVE transactions and their physical effect on the databases' transaction logs. This is especially useful when seeking out transactions that may be causing explosive transaction log growth.
• Snapshot Isolation and the tempdb Version Store: Each time a row is modified in a database running under snapshot isolation, a version of the row from prior to the modification is stored within tempdb in a version store. Read transactions targeting the affected rows will use the row version(s) from the tempdb, while the writing transaction will modify the actual table data. Update locks are issued for writes, and when a read transaction encounters such a lock, it is diverted to the version store. This ensures that write transactions do not block reads nor the inverse.  … P148 
• Snapshot isolation introduces two new modes of operation:
• SNAPSHOT mode isolates read transactions from modifications that committed after the transaction began
• READ_COMMITTED_SNAPSHOT mode isolates read transactions from modifications which committed after the current statement began…. P149 – 151 show how to enable and initiate the two modes.
• P151 the writer shows how to “Investigating snapshot isolation”. He shows how to use sys.dm_tran_active_snapshot_database_transactions, sys.dm_tran_currrent_snapshot, and sys.dm_tran_transactions_snapshot to get the “current snapshot activity”, then how to use sys.dm_tran_version_store, and sys.dm_tran_top_version_generators to get “Version store usage”.

Reference: http://www.amazon.com/Performance-Tuning-Server-Dynamic-Management/dp/1906434476