2

I'm trying to decide whether this performance counter is worthwhile monitoring: \\SERVER\MSSQL:Transactions\Longest Transaction Running Time.

So during testing I tried to create some long running transactions but I can't seem to get this counter to read anything greater than zero.

But I can't find a transaction or query that will do it. As a starting point, I used the example query (an insert in a transaction) in the help for DBCC OPENTRAN with no luck.

Is there a query that affects this counter?

3

3 Answers 3

1

Little late to the party. I believe the documentation has been updated to explain the answer to this in the last six years, but per my testing it isn't completely correct.

From Books Online: "The length of time (in seconds) since the start of the transaction that has been active longer than any other current transaction. This counter only shows activity when the database is under read committed snapshot isolation level. It does not log any activity if the database is in any other isolation level."

Per my testing, this is not quite correct. If RCSI is not enabled, but you are allowing snapshot, it will show the longest running transaction time for SNAPSHOT queries.

The other tricky thing is that the counter only updates every 60 seconds.

Basically, it's very useful for monitoring if something's going to block version store cleanup when you're using snapshot or RCSI.

3

This counter seems to be a little bit slow/ hit and miss on updating, and I'm not entirely convinced its doing exactly what its explanation suggests, however I seemed to get the most success by doing a select into from (a large table) in an open ended transaction.

Funnily enough issuing a rollback seemed to cause the counter stat to be updated immediately, commit was less effective and sometimes it would update without either. As I say I dont think this counter is exactly what we think, and might just be an approximation/ average.

Regards, Mark @retracement

1
  • Thanks Mark... sounds like it's not a counter to be trusted. Jul 29, 2010 at 17:34
1

This should give you a value in that counter. You'll obviously have to manually stop it.

SELECT '1' AS field1 INTO #tmp
BEGIN TRANSACTION
WHILE 1=1
BEGIN
    UPDATE #tmp SET field1=1
END
COMMIT TRANSACTION
DROP TABLE #tmp
4
  • No luck :-( You mentioned that it "should" give a value in that counter. Have you seen it on your own machine? Jul 29, 2010 at 13:53
  • @Michael: yep, tested it before I posted to make sure that I saw something.
    – squillman
    Jul 29, 2010 at 17:49
  • This also does not work for me on SQL Server 2005 Enterprise on Windows Server 2003 Enterprise. Jul 30, 2010 at 8:20
  • I got to see something on another server, but not consistently! Which is more weight to the conclusion not to trust the counter :-( squillman, thanks for your help. Jul 30, 2010 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.