From: Nils on
Hi All,

I have a SQL Server 2008 SP1 x64 Standard instance running on Windows Server
2008 R2. I am observing unexpected behaviour in connection with the
"MSSQL$SQLInstanceName:Transactions\Longest Transaction Running Time"
performance counter.

According to BOL, this counter measures "The length of time (in seconds)
since the start of the transaction that has been active longer than any other
current transaction."

Suppose the server is idle. Now I start the following code

WAITFOR DELAY '00:00:05'
CREATE TABLE #testtable
i1 int
INSERT #testtable VALUES (123)
WAITFOR DELAY '00:00:05'
DROP TABLE #testtable

as far as I understand, this code should create a transaction that takes 10
seconds to run.

Accordingly, when monitoring the performance counter in Perfmon, I would
expect the value to constantly rise up to a value of 10 and then instantly
drop back to zero.

Instead, the counter doesn't change at all. Why could that be?

Thank you and best regards
Nils Loeber

P.S. There is only one instance of SQL Server running on the machine.