Implicit vs Explicit Transaction Performance

Question

Implicit vs Explicit Transaction Performance

TDuffy

SSCarpal Tunnel

Points: 4170
More actions
May 4, 2006 at 10:00 am

#91271

Two things..
1. Why would the following insert 1 million rows per minute as shown(explicit), and only 20K per minute when the begin and commit tran are removed (implicit)?
Declare @Now datetime, @EndTime datetime,@DELAY varchar(15)
set nocount on
select @Now = GetDate(), @EndTime = dateadd(mi,5, GetDate())
Begin tran
While @Now < @EndTime
Begin
  /*insert*/
  insert into Fail_Over_Test_EX.dbo.Fail_Over_Insert_Ex (TestVal)
  values('Test this insert')
  /*increment*/
  set @Now = GetDate()
End
commit tran
2. Does anyone know where I can the EventSubClass for the Transaction Log event descripitons? Trace outputs an integer, and I need to know what they mean.
Thanks in advance.
Terry

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gordon Pollokoff SSCommitted Points: 1930 More actions · Answer 1

#1: For each insert with implicit transactions, the SQL Server engine creates a transaction; with explicit transactions, all inserts are wrapped in a single transaction. This can best be seen by examining the Transaction trace object while each version of your script is running.

#2: From BOL description of the Transaction Event data columns

Event Sub Class

Microsoft® Distributed Transaction Coordinator (MS DTC) state. For more information, see the MS DTC documentation. Possible values include:

0 = GET_DTC_ADDRESS_SUB_CLASS

1 = PROPAGATE_XACT_SUB_CLASS

2 = DOWORK_SUB_CLASS

3 = CLOSE_CONN_SUB_CLASS

4 = DTC_VIRGIN_SUB_CLASS

5 = DTC_IDLE_SUB_CLASS

6 = DTC_BEG_DIST_SUB_CLASS

7 = DTC_ENLISTING_SUB_CLASS

8 = DTC_INT_ACTIVE_SUB_CLASS

9 = DTC_INT_COMMIT_SUB_CLASS

10 = DTC_INT_ABORT_SUB_CLASS

11 = DTC_INT_ASYNC_ABORT_SUB_CLASS

12 = DTC_ACTIVE_SUB_CLASS

13 = DTC_INIT_PREPARE_SUB_CLASS

14 = DTC_PREPARING_SUB_CLASS

15 = DTC_PREPARED_SUB_CLASS

16 = DTC_ABORTING_SUB_CLASS

17 = DTC_COMMITTING_SUB_CLASS

18 = DTC_DO_ASYNC_ABORT_SUB_CLASS

19 = DTC_DISASTER_SUB_CLASS

20 = DTC_DRAIN_ABORT_SUB_CLASS

21 = DTC_ASYNC_ABORT_SUB_CLASS

22 = DTC_TM_RECOVERY_SUB_CLASS

Gordon Pollokoff

"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones

TDuffy SSCarpal Tunnel Points: 4170 More actions · Answer 2

Thank you Gordon, but that is the list for DTC. I am looking for the same enumeration for the Transaction Log, event 54.

Terry

peterhe SSChampion Points: 11469 More actions · Answer 3

For implicit transaction, SQL server needs to write transaction log to disk (or powered disk cache) for each INSERT statment.

For explict transaction, SQL server can potentially have one I/O for transaction log for the one million insertion (upto memory and checkpoint).

As you nocticed, I/O on transaction logs has huge impact on performance.

TDuffy SSCarpal Tunnel Points: 4170 More actions · Answer 4

Thanks Peter.

That is my assumption and understanding also, and I appreciate the confirmation. Do you know of any good documentation on this?

Also, I ran a trace on each method, and there are some very different counts in ceratin sub events of the Transaction Log event. Do you know where I can find a translation for the numeric Event Sub Class memebers of the TransactionLog Event.

Thanks Again.