Microsoft – SQLServer – Transaction Trending
Like everything else about MS SQL Server, Microsoft has done and is doing a lot of things to increase the over-arching instrumentation of the product.
One area, I am recently aware of is what insights one can gain along the area of on-going Transactions.
Who issued them, how far along they are, how many records are affected, etc….
So here is what I ended up with from reading a Web Posting from MS Blogger Sakthivel Chidambaram – What is WRITELOG waittype and how to troubleshoot and fix this wait in SQL Server”
--dadeniji select tblTranDatabase.transaction_id , db_name(tblTranDatabase.database_id) as databaseName , tblTranActive.name transactionName , tblTranSession.session_id as sessionID , tblSession.login_name as loginName , tblSession.host_name as hostName , tblTranDatabase.database_transaction_log_record_count as TransactionRecordCount , tblSession.logical_reads readsLogical , tblSession.writes as writesLogical , tblSession.row_count as SessionRowCount , tblTranActive.transaction_begin_time , datediff( minute , tblTranActive.transaction_begin_time , GETUTCDATE() ) as durationInMinutes , tblTranDatabase.database_transaction_replicate_record_count as replicateCount , case when (tblTranActive.transaction_type =1) then 'Read\Write Transaction' when (tblTranActive.transaction_type =2) then 'Read-Only Transaction' when (tblTranActive.transaction_type =3) then 'System Transaction' when (tblTranActive.transaction_type =4) then 'Distributed Transaction' end as TransactionType , case when (tblTranActive.transaction_state =1) then 'Transaction has not been completely initialized' when (tblTranActive.transaction_state =2) then 'Transaction has been initialized, but has not started' when (tblTranActive.transaction_state =3) then 'Transaction is active' when (tblTranActive.transaction_state =4) then 'Transaction has ended (Read-only Transaction)' end as TransactionState , tblTranActive.transaction_uow , case when (tblTranActive.dtc_state =1) then 'Active' when (tblTranActive.dtc_state =2) then 'Prepared' when (tblTranActive.dtc_state =3) then 'Committed' when (tblTranActive.dtc_state =4) then 'Aborted' when (tblTranActive.dtc_state =5) then 'Recovered' end as DTCState from sys.dm_tran_database_transactions tblTranDatabase inner join sys.dm_tran_session_transactions tblTranSession on tblTranDatabase.transaction_id = tblTranSession.transaction_id inner join sys.dm_tran_active_transactions tblTranActive on tblTranDatabase.transaction_id = tblTranActive.transaction_id inner join sys.dm_exec_sessions tblSession on tblTranSession.session_id = tblSession.session_id order by tblTranDatabase.database_transaction_log_record_count desc
A couple of takeaways:
- As much as possible use Transactions, smaller ones especially. Even sometimes when you do not think you need a Transaction, see if you can use
- There are many samples on the NET that offers credible guide as to how to use small Transact Loops around updates \ deletes. Usually, they use update/delete top N
- Also, as SQL Server supports named Transactions, use them; as they are far easier to debug (Sample) :
begin transaction trnProcessCarryWater ..... ..... ..... commit transaction trnProcessCarryWater