{"id":1001,"date":"2010-09-30T00:00:00","date_gmt":"2010-09-30T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/investigating-transactions-using-dynamic-management-objects\/"},"modified":"2021-08-24T13:40:26","modified_gmt":"2021-08-24T13:40:26","slug":"investigating-transactions-using-dynamic-management-objects","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/investigating-transactions-using-dynamic-management-objects\/","title":{"rendered":"Investigating Transactions Using Dynamic Management Objects"},"content":{"rendered":"
\n

Every statement executed against SQL Server is transactional, and SQL Server implements various transaction isolation levels, to ensure the ACID (Atomicity, Consistency, Isolation, and Durability) properties of these transactions. In practical terms, this means that it uses locks and latches to mediate transactional access to shared database resources, and so prevent “interference” between the transactions. When a transaction encounters a locked resource it must, of course, wait for the resource to become free before proceeding. If such blocking occurs frequently or is protracted, it greatly restricts the number of concurrent users that can access the system.<\/p>\n

This article explains how to use the transaction-related Dynamic Management Objects (DMOs) to locate the transactions that are causing locking and blocking issues on their SQL Server instances, and the sessions to which they belong. Having located the offending transactions, the DBA can then take steps to alleviate the blocking, either by tuning the SQL or, if this is not possible, by careful scheduling of the more resource-hungry and\/or longer-running business reports, so that they occur separately from the main Online Transaction Processing (OLTP) load.<\/p>\n

We’ll also examine how to use the transaction-related DMOs to, for example, investigate long-running transactions that may be preventing transaction log truncation, and transactions that are causing large amounts of data to be written to the transaction log, making it grow rapidly in size.<\/p>\n

What is a transaction, anyway?<\/h1>\n

Microsoft Books Online is as good a place as any to start, for a definition of the term, transaction:<\/p>\n

A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID properties, to qualify as a transaction.<\/i><\/p>\n

For a transaction to pass the ACID Test, all of its data modifications must complete or be rolled back (Atomic<\/i>); the end result must be that all data and supporting structures such as indexes must be consistent with the rules that apply to them (Consistent<\/i>). A transaction cannot be impacted by any other transactions occurring concurrently (Isolated<\/i>); the results of the transaction being permanently recorded in the RDBMS (Durability<\/i>).<\/p>\n

As noted in the introduction to this article, every statement executed against SQL Server is transactional. If we issue a single SQL statement, an implicit transaction is run under the covers, which auto-starts and auto-completes. If we use explicit BEGIN<\/span> TRAN<\/span> \/ COMMIT<\/span> TRAN<\/span> commands, then we can group together in an explicit transaction a set of statements that must fail or succeed together. This is easily demonstrated by the series of queries shown in Listing 1 and the resulting output.<\/p>\n

\tSELECT\u00a0 DTAT.transaction_id\r\n\tFROM\u00a0\u00a0\u00a0 sys.dm_tran_active_transactions DTAT\r\n\tWHERE\u00a0\u00a0 DTAT.name  <>  'worktable' ; \r\n\t\r\n\tSELECT\u00a0 DTAT.transaction_id\r\n\tFROM\u00a0\u00a0\u00a0 sys.dm_tran_active_transactions DTAT\r\n\tWHERE\u00a0\u00a0 DTAT.name  <>  'worktable' ; \r\n\t\r\n\tBEGIN  TRAN \r\n\tSELECT\u00a0 DTAT.transaction_id\r\n\tFROM\u00a0\u00a0\u00a0 sys.dm_tran_active_transactions DTAT\r\n\tWHERE\u00a0\u00a0 DTAT.name  <>  'worktable' ; \r\n\t\r\n\tSELECT\u00a0 DTAT.transaction_id\r\n\tFROM\u00a0\u00a0\u00a0 sys.dm_tran_active_transactions DTAT\r\n\tWHERE\u00a0\u00a0 DTAT.name  <>  'worktable' ; \r\n\tCOMMIT TRAN\r\n\t\r\n\ttransaction_id\r\n\t--------------------\r\n\t18949550\r\n\t...\r\n\t18949551\r\n\t...\r\n\t18949552\r\n\t...\r\n\t18949552\r\n<\/pre>\n
Listing 1: All statements within SQL Server are transactional.<\/p>\n
According to the results of these queries, any statements executed outside of an explicit transaction will execute as separate transactions, and each will result in a row with a unique  transaction_id<\/span> in our result sets. All statements executed within an explicit transaction will be reported with a single  transaction_id<\/span>.<\/p>\n
\n
This article is adapted from a chapter of ‘Performance Tuning with SQL Server Dynamic Management Views’ by Louis Davidson and Tim Ford. To learn more about this book, visit the Amazon page<\/a>.<\/p>\n<\/div>\n
\n
SQL Server will attempt to ensure that each unit of work, be it a single-statement implicit transaction, or any number of individual SQL statements within an explicit transaction, conforms to the ACID test characteristics.<\/p>\n
What we hope to demonstrate, in this article, is how to observe these units of work via the DMOs. Since the lifespan of these transactions is measured in milliseconds, when everything is going right, the focus will be on those transactions that are having difficulty completing in a timely fashion, whether due to contention for resources, poor tuning, or other issues.<\/p>\n
Investigating Locking and Blocking<\/h1>\n
Locking is an integral aspect of any RDBMS. Locks control how transactions are allowed to interact, impact, and impede one another when running simultaneously against common objects. Unless you restrict data access to one user at a time, clearly not a viable option, locks are necessary to the smooth functioning of any RDBMS.<\/p>\n
Locks are to be neither feared nor shunned but, nevertheless, can cause problems for the reckless or unwary. When using SQL Server’s default isolation level,  READ<\/span>  COMMITTED<\/span>, shared read<\/b> locks are acquired during data reads. These locks prevent another transaction from modifying that data while the query is in progress, but do not block other readers. Furthermore, “dirty reads” are forbidden so SQL Server acquires exclusive locks<\/b> during updates, to prevent a transaction from reading data that has been modified by another transaction, but not committed. Of course, this means that if one transaction (A) encounters data that is being modified by another transaction (B), then transaction A is blocked; it needs access to a resource that is locked by B and cannot proceed until B either commits or rolls back.<\/p>\n
As noted, this is normal behavior but, in conditions of highly concurrent user access, the potential for blocking will increase. The situation will be exacerbated by, for example, transactions that are longer than they need to be or are poorly written, causing locks to be held for longer than necessary. As locking and blocking increase, so does the overhead on the RDBMS and the end result is a significant reduction in concurrency.<\/p>\n
READ UNCOMMITTED – Don’t do it<\/b>
\n If you want concurrency at all costs, then  READ<\/span>  UNCOMMITTED<\/span> isolation level will shun locks as far as possible. This mode allows dirty reads, so use it at the expense of your data consistency and integrity.<\/p>\n
In  READ<\/span>  COMMITTED<\/span> 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<\/span>  READ<\/span> level will ensure that all the rows that were read cannot be modified or deleted until the transaction which read 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<\/span>, 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.<\/p>\n
However, as the isolation level becomes more restrictive, so the use of locks becomes more prevalent and the likelihood of blocking, and even deadlocking, where transaction A is waiting for a resource that is held by B, and vice versa, increases dramatically. Note also, that it is not only competing modifications that can cause a deadlock. It is just as possible for a modification to deadlock with a reporting query. In short, DBAs need a way of investigating blocking on their SQL Server instances.<\/p>\n
Snapshot isolation level<\/b>
\n The snapshot isolation level was introduced in SQL Server 2005 and eliminates blocking and deadlocking by using row versioning in the  tempdb<\/span> version store to maintain concurrency, rather than establishing locks on database objects. There exist five DMOs dedicated to investigation of this isolation level and of version store usage. These will not be covered in this article, but more information can be found in our book  Performance Tuning with SQL Server DMVs<\/a>, as well as in Books Online<\/a>.<\/p>\n
DMOs, Activity Monitor and sp_who2<\/h2>\n
Pre-SQL Server 2005, the only way a DBA could analyze blocking behavior in a SQL Server instance was to query the  sysprocesses<\/span> system table or to use  sp_who<\/span> and  sp_who2<\/span>. With SQL 2005, the situation has improved dramatically, and this information is now available through several new routes:<\/p>\n