SQL Server "Long running transaction" performance counter: why no workee?

Posted by Sleepless on Server Fault See other posts from Server Fault or by Sleepless
Published on 2010-04-20T13:03:02Z Indexed on 2010/04/20 13:13 UTC
Read the original article Hit count: 514

Please explain to me the following observation:

I have the following piece of T-SQL code that I run from SSMS:

BEGIN TRAN
SELECT COUNT (*)
FROM m
WHERE m.[x] = 123456
   or m.[y] IN (SELECT f.x FROM f)
SELECT COUNT (*)
FROM m
WHERE m.[x] = 123456
   or m.[y] IN (SELECT f.x FROM f)
COMMIT TRAN

The query takes about twenty seconds to run. I have no other user queries running on the server.

Under these circumstances, I would expect the performance counter "MSSQL$SQLInstanceName:Transactions\Longest Transaction Running Time" to rise constantly up to a value of 20 and then drop rapidly. Instead, it rises to around 12 within two seconds and then oscillates between 12 and 14 for the duration of the query after which it drops again.

According to the MS docs, the counter measures "The length of time (in seconds) since the start of the transaction that has been active longer than any other current transaction." But apparently, it doesn't. What gives?

© Server Fault or respective owner

Related posts about sql-server

Related posts about performance-monitoring