Tuesday, April 30, 2019

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

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?

No comments:

Post a Comment

linux - How to SSH to ec2 instance in VPC private subnet via NAT server

I have created a VPC in aws with a public subnet and a private subnet. The private subnet does not have direct access to external network. S...