Skip to Content
0

Zero Waits with nonzero Waittimes the monProcessWaits table

Dec 02, 2016 at 02:08 PM

15

avatar image

Hi all the good people out there,

I just had an observation while running a query against the monProcessWaits table.

Here's the query:

select distinct top 100

a.SPID, left(suser_name(a.ServerUserID),12) LOGIN_NAME, a.WaitEventID, left(b.Description,70) EVENT_DESCRIPTION, a.Waits, a.WaitTime

from monProcessWaits a, monWaitEventInfo b

where a.WaitEventID=b.WaitEventID and a.WaitTime > a.Waits order by WaitTime desc

I ran two samples 30 seconds apart.

What I noticed is that for some processes, the number of waits did not change, whereas the waittimes had increased. Below are the fragments from two consecutive runs:

SPID LOGIN_NAME WaitEventID EVENT_DESCRIPTION Waits WaitTime

----------- ------------ ----------- ---------------------------------------------------------------------- ----------- -----------

17 NULL 596 Wait until heartbeat or check interval expires 10832 1737066153

5 NULL 19 xact coord: pause during idle loop 100535 1736849975

7 NULL 19 xact coord: pause during idle loop 100535 1736849950

6 NULL 19 xact coord: pause during idle loop 100535 1736847929

4 NULL 19 xact coord: pause during idle loop 100535 1736847074

14 NULL 178 waiting for client connection request 270880 1734986304

15 NULL 178 waiting for client connection request 525869 1732548353

8 NULL 57 checkpoint process idle loop 110609 1712428686

10 NULL 61 hk: pause for some time 1200988 1696356150

11 NULL 61 hk: pause for some time 99616 1677751369

9 NULL 61 hk: pause for some time 1409923 1652520713

1342 messagenet 250 waiting for incoming network data 112 1547510810

1257 apiread 250 waiting for incoming network data 56 246564191

1144 ops 250 waiting for incoming network data 41 75843516

2004 ops 250 waiting for incoming network data 117 75834529

. . . . .

-------------------- wait for 30 seconds -------------------

SPID LOGIN_NAME WaitEventID EVENT_DESCRIPTION Waits WaitTime

----------- ------------ ----------- ---------------------------------------------------------------------- ----------- -----------

17 NULL 596 Wait until heartbeat or check interval expires 10832 1737094400 <----

5 NULL 19 xact coord: pause during idle loop 100536 1736878222

7 NULL 19 xact coord: pause during idle loop 100536 1736878197

6 NULL 19 xact coord: pause during idle loop 100536 1736876176

4 NULL 19 xact coord: pause during idle loop 100536 1736875321

14 NULL 178 waiting for client connection request 270880 1735014551 <--------

15 NULL 178 waiting for client connection request 525870 1732576561

8 NULL 57 checkpoint process idle loop 110610 1712456863

10 NULL 61 hk: pause for some time 1200994 1696384306

11 NULL 61 hk: pause for some time 99616 1677779616 <--------

9 NULL 61 hk: pause for some time 1409925 1652548861

1342 messagenet 250 waiting for incoming network data 112 1547539057 <--------

1257 apiread 250 waiting for incoming network data 56 246592438 <--------

1144 ops 250 waiting for incoming network data 41 75871763 <--------

2004 ops 250 waiting for incoming network data 117 75862776 <--------

. . . .

For example for SPIDs: 17, 14, 11, 1342, 1257, 1144 and 2004.

Was wondering if it's a bug with those wait events, or if it was caused by the ASE still waiting for an event to complete? Like for example SPID 1342 is a connection which is kept open, and is idle. It had 56 waits before I ran the first sample, and while nothing has happened over the last 30 seconds for it it still continues to wait for incoming data from a client. So its waits delta is 0, while the WaitTime delta is 28148 msec. (i.e. 1652548861 - 1652520713)...

Thanks Vato

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers