Microsoft – SQLServer – Transaction Trending

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”

http://blogs.msdn.com/b/sqlsakthi/archive/2011/04/17/what-is-writelog-waittype-and-how-to-troubleshoot-and-fix-this-wait-in-sql-server.aspx

--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

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s