Managing SQL Server transactions is an important step toward ensuring smooth operations and avoiding locking and blocking errors.
Excerpted from “SQL Server DMV Starter Pack,” published by Red Gate Books (2010).
Looking into granular levels of managing SQL Server operations, you have to carefully consider how you manage transaction-related Dynamic Management Objects (DMOs). All of the Dynamic Management Views (DMVs) in the “transaction related” category of DMOs begin with “sys.dm_tran_” as an opening statement.
Ultimately, every statement executed against SQL Server is transactional. If you issue a single SQL statement, an implicit transaction is started under the covers. This statement will start and auto-complete. If you use explicit BEGIN TRAN / COMMIT TRAN commands, you can group these together as an explicit transaction—a set of statements that must fail or succeed together.
SQL Server implements various transaction isolation levels, to ensure the Atomicity, Consistency, Isolation and Durability (ACID) properties of these transactions. In practical terms, this means that it uses locks and latches to mediate transactional access to shared database resources and prevent “interference” between the transactions.
Generally speaking, your strategy and processes for investigating and managing SQL Server transactions will be limited to a few key questions:
Of these reasons, investigating locking and blocking is by far the most common use of these DMVs. An area of investigation that will continue to become increasingly common is into the activity generated when using the SNAPSHOT isolation level. The snapshot isolation level was introduced in SQL Server 2005. Snapshot isolation eliminates blocking and deadlocking by using a version store in the tempdb database to maintain concurrency, rather than establishing locks on database objects. There are a number of DMVs provided to investigate this isolation level.
Let’s now move on to the scripts. Unless stated otherwise, all of these queries work with SQL Server 2005, 2008 and 2008 R2, and all require VIEW SERVER STATE permission. This script uses two DMVs. The first is sys.dm_tran_database_transactions, which is described in SQL Server books online (BOL) as follows: “Returns information about transactions at the database level.”
The second is sys.dm_tran_session_transactions, which simply: “Returns correlation information for associated transactions and sessions.”
The terse description given for database_transactions rather belies its potential usefulness. The following script provides a query that shows, per session, which databases are in use by a transaction open by that session, whether the transaction has upgraded to read-write in any of the databases (by default most transactions are read-only), when the transaction upgraded to read-write for that database, how many log records written, and how many bytes were used on behalf of those log records:
SELECT st.session_id , DB_NAME(dt.database_id) AS database_name , CASE WHEN dt.database_transaction_begin_time IS NULL THEN 'read-only'
ELSE 'read-write' END AS transaction_state , dt.database_transaction_begin_time AS read_write_start_time , dt.database_transaction_log_record_count , dt.database_transaction_log_bytes_usedFROM sys.dm_tran_session_transactions AS st INNER JOIN sys.dm_tran_database_transactions AS dt
ON st.transaction_id = dt.transaction_idORDER BY st.session_id , database_name
These sorts of queries against database_transactions are very useful when monitoring things such as:
Our example script for the transaction-related category of DMVs uses the sys.dm_tran_locks DMV, which is described by BOL as follows:
“Returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. The columns in the result set are divided into two main groups: resource and request. The resource group describes the resource on which the lock request is being made, and the request group describes the lock request.”
This DMV is useful in helping to identify locking and blocking issues on your database instances:
-- Look at active Lock Manager resources for current database
SELECT request_session_id ,
DB_NAME(resource_database_id) AS [Database] , resource_type , resource_subtype , request_type , request_mode , resource_description , request_mode , request_owner_type
FROM sys.dm_tran_locksWHERE request_session_id > 50 AND resource_database_id = DB_ID() AND request_session_id <> @@SPIDORDER BY request_session_id ;
-- Look for blocking
SELECT tl.resource_type , tl.resource_database_id , tl.resource_associated_entity_id , tl.request_mode , tl.request_session_id , wt.blocking_session_id , wt.wait_type , wt.wait_duration_msFROM sys.dm_tran_locks AS tl INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_addressORDER BY wait_duration_ms DESC ;
The first query shows lock types and their status by SPID, filtered by the current database, and eliminating the current connection and the system SPIDs. The second query provides information regarding any blocking that might be occurring, instance-wide. Notice that this second query joins to the sys.dm_os_waiting_tasks DMV to obtain data on the length of time a process has been waiting, due to blocking, and on which resource.
Unless you have severe blocking issues, you'll typically need to run each of these queries multiple times to catch blocking. If you do identify two data modification statements, or a query and a data modification, which are embracing in severe blocking, or even deadlocks, then you’ll need to extract the SQL text for the queries, examine them, run them on a test system—with Profiler tracing running—and work out a way to tune the queries, or add indexes, to alleviate the problem.
Learn more about “SQL Server DMV Starter Pack” at red-gate.com/our-company/about/book-store.