Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

Check the DMVs which this is running and see what the CPU time reports for the query. I would assume that this matches.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.