on 2013 Dec 28 11:26 AM
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
Request clarification before answering.
You can think of a connection as being in one of three categories of states (see ReqStatus):
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:
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
52 | |
8 | |
5 | |
5 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.