cancel
Showing results for 
Search instead for 
Did you mean: 

Why does ReqTimeActive equal ReqTimeBlockLock for a blocked connection?

Breck_Carter
Participant
1,945

The following tests show that when a connection is blocked by a row lock soon after connecting, the connection-level ReqTimeActive and ReqTimeBlockLock properties BOTH grow, and BOTH more-or-less match the total elapsed time since connecting.

IMO the the ReqTimeActive is incorrect; it should be (close to) zero.

-- Test 1 using Version 16...

SELECT LEFT ( @@VERSION, 11 ) AS version,
       DATEDIFF ( SECOND, CONNECTION_PROPERTY ( 'LoginTime', 5 ), CURRENT TIMESTAMP ) AS connection_time,
       CAST ( ROUND ( CONNECTION_PROPERTY ( 'ReqTimeActive', 5 ), 0 ) AS INTEGER ) AS ReqTimeActive,
       CAST ( ROUND ( CONNECTION_PROPERTY ( 'ReqTimeBlockLock', 5 ), 0 ) AS INTEGER ) AS ReqTimeBlockLock;

version          connection_time ReqTimeActive ReqTimeBlockLock 
----------- -------------------- ------------- ---------------- 
16.0.0.1691                 5719          5698             5698

-- Test 2 using Version 12...

SELECT LEFT ( @@VERSION, 11 ) AS version,
       DATEDIFF ( SECOND, CONNECTION_PROPERTY ( 'LoginTime', 106 ), CURRENT TIMESTAMP ) AS connection_time,
       CAST ( ROUND ( CONNECTION_PROPERTY ( 'ReqTimeActive', 106 ), 0 ) AS INTEGER ) AS ReqTimeActive,
       CAST ( ROUND ( CONNECTION_PROPERTY ( 'ReqTimeBlockLock', 106 ), 0 ) AS INTEGER ) AS ReqTimeBlockLock;

version     connection_time      ReqTimeActive ReqTimeBlockLock 
----------- -------------------- ------------- ---------------- 
12.0.1.3298 129                  116           116              

Accepted Solutions (1)

Accepted Solutions (1)

Former Member

You can think of a connection as being in one of three categories of states (see ReqStatus):

  • Idle -- the connection is not currently processing a request. No work is being done in the server on behalf of the connection, but the application program could be busy so Idle might be misleading.
  • Unscheduled -- the connection wants work done but the server is busy processing other requests. The connection is waiting for a worker to free up to do work for it.
  • Active -- there is a worker dedicated to processing a statement for the connection. The worker might be busy using CPU or blocked (waiting for I/O, waiting for a row lock, waiting for an internal mutex, executing a WAITFOR, waiting for a web service call or proxy table, ...)

The sum of these three measures (ReqTimeUnschedule, ReqTimeActive, (idle)) should equal roughly the time since a connection was created. The server does not track idle time directly but you can compute it as a derived quantity.

While a connection is "Active" with a work assigned, there are further subdivisions of state that the server tracks:

  • BlockedIO -- The connection is blocked waiting for an I/O to complete.
  • BlockedContention -- The connection is blocked waiting for access to shared database server data structures.
  • BlockedLock -- The connection is blocked waiting for a locked object.
  • Executing -- The connection is executing a request and not blocked in one of the above ways

The server reports the time spend in various blocked states but not the time spent executing. You can compute it as:

ReqTimeExecuting = ReqTimeActive - (ReqTimeBlockContention+ReqTimeBlockLock+ReqTimeBlockIO)

I think @Breck you were expecting the ReqTimeActive to be this ReqTimeExecuting -- the time the connection spent executing without being blocked.

There are some further caveats to be aware of when looking at ReqTimeExecuting (and this is partly why it is not computed and returned). When a connection issues a WAITFOR statement, this is not counted under any of the blocking categories. Similarly, calls to access remote data (remote procedures, remote tables, web service client procedure, waiting for an external function, waiting for a forbidder) do not fall into the blocking categories. Therefore, the computed ReqTimeExecuting is not purely a measure of time spent executing in the server. A further difficulty is that when a connection executes a parallel plan, each worker involved contributes to the counters. The ReqTimeActive and other counters can therefore exceed the total time the connection was logged in.

Answers (0)