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