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              

asked 28 Dec '13, 11:26

Breck%20Carter's gravatar image

Breck Carter
25.8k428592853
accept rate: 20%

edited 28 Dec '13, 11:28


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.

permanent link

answered 22 Mar '14, 10:23

Ivan%20T.%20Bowman's gravatar image

Ivan T. Bowman
2.8k22732
accept rate: 39%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×102

question asked: 28 Dec '13, 11:26

question was seen: 587 times

last updated: 22 Mar '14, 10:23