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?
Best Answer
Check the DMVs which this is running and see what the CPU time reports for the query. I would assume that this matches.