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: 544
sql-server
|performance-monitoring
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