What SQL Server Query will set perfmon counter "Longest Transaction Running Time" > 0

Question

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?

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

Answer 2

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

Answer 3

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