View source for MDA tables - queries for wait events

There are two tables that you can use to monitor the various wait events that occur within ASE:

* monSysWaits - Provides a server-wide view of wait events.
* monProcessWaits - Provides a view of wait events per process.

Both tables contain a foreign key to the table monWaitEventInfo where a textual description is stored.

Here are some queries that you can use to query these tables.

==Show wait events on server level==
 select WaitEventID,
        convert(numeric(16,0),Waits) as "Waits",
        convert(numeric(16,0),WaitTime) as "WaitTime"
        into    #waits
        from    monSysWaits
 go
 select Description,
        convert(int,sum(w.Waits)) as "Count",
        convert(int,sum(w.WaitTime)/1000) as "Seconds"
        from    #waits w,
                monWaitEventInfo ei
        where   w.WaitEventID   = ei.WaitEventID
        group   by Description
        order   by 3 desc
 go

sample output:

 Description                                        Count       Seconds     
 -------------------------------------------------- ----------- ----------- 
 waiting while no network read or write is required   515617677       73298 
 waiting for incoming network data                    281686681       45041 
 xact coord: pause during idle loop                      374132       22447 
 hk: pause for some time                                1622879       16518 
 waiting while allocating new client socket              609824       11223 
 checkpoint process idle loop                            115475       11219 
 wait until an engine has been offlined                  187067        5611 
 wait for message                                       1172649        5605 
 waiting on run queue after yield                      41160336         278 
 wait for flusher to queue full DFLPIECE                  85809          85 
 wait for mass read to finish when getting page        22297268          48 
 waiting for message in worker thread mailbox              7436          34 
 waiting for network send to complete                 236416549          33 
 waiting for page reads in parallel dbcc               13314840          28 
 waiting for regular buffer read to complete           22686969          26 
 waiting on run queue after sleep                    1106499327          15 
 waiting for last i/o on MASS to complete               2517210          14 
 waiting for buf write to complete before writing       3547261          10 
 wait for i/o to finish after writing last log page     3639345           4 
 waiting for a lock                                       48193           1 
 wait to acquire latch                                    10686           1 
 waiting for lock on ULC                                    188           0 
 wait for data from client                                51673           0 
 wait for DES state is changing                              26           0 
 site handler waiting to be created                         628           0 
 waiting for native thread to finish                       1256           0 
 wait for object to be returned to pool                       1           0 
 pause to synchronise with site manager                    4349           0 
 waiting for mass destruction to complete                    11           0 
 waiting to re-read page in parallel dbcc                 10705           0 
 waiting for buffer validation to complete                   55           0 
 waiting for site handler to complete setup                 628           0 
 waiting for read to complete in parallel dbcc           142074           0 
 waiting sending fault msg to parent in PLL dbcc           1079           0 
 waiting for MASS to finish changing to start i/o            20           0 
 wait for MASS to finish changing before changing            27           0 
 waiting for i/o on MASS initated by another task         79141           0 
 waiting for write of the last log page to complete        3913           0 
 waiting for MASS to finish writing before changing      245415           0 
 
 (39 rows affected)

==Show wait events per process, aggregated==
===ASE 15.0.2===

 select WaitEventID,
        case ServerUserID
                when 0 then "Y"
                else "N"
        end as "Server",
        convert(numeric(16,0),Waits) as "Waits",
        convert(numeric(16,0),WaitTime) as "WaitTime"
        into    #waits
        from    monProcessWaits
 go
 select Server,
        Description,
        convert(int,sum(w.Waits)) as "Count",
        convert(int,sum(w.WaitTime)/1000) as "Seconds"
        from    #waits w,
                monWaitEventInfo ei
        where   w.WaitEventID   = ei.WaitEventID
        group   by Server,Description
        order   by 4 desc
 go

sample output:

 Server Description                                        Count       Seconds     
 ------ -------------------------------------------------- ----------- ----------- 
 Y      xact coord: pause during idle loop                      374348     5280984 
 Y      hk: pause for some time                                1623691     3643216 
 Y      waiting while allocating new client socket              609947     2639972 
 Y      checkpoint process idle loop                            115538     2636285 
 Y      wait for message                                       1172883     1314160 
 N      waiting for incoming network data                       675825     1212664 
 Y      waiting for message in worker thread mailbox                18      390372 
 Y      waiting on run queue after yield                      39889465      277677 
 Y      waiting for buf write to complete before writing       2915791        9305 
 Y      waiting for last i/o on MASS to complete               1605428        8713 
 Y      wait for i/o to finish after writing last log page     2766664        3992 
 Y      waiting for i/o on MASS initated by another task         14714         200 
 N      waiting for network send to complete                   2377635         122 
 Y      waiting for MASS to finish writing before changing        3065          31 
 N      wait for i/o to finish after writing last log page        1439           8 
 Y      waiting for MASS to finish changing to start i/o            19           7 
 Y      waiting for regular buffer read to complete              10610           6 
 Y      waiting for page reads in parallel dbcc                    800           3 
 Y      waiting for write of the last log page to complete        2869           2 
 Y      waiting for a lock                                          80           0 
 Y      wait to acquire latch                                       69           0 
 Y      waiting for lock on ULC                                     59           0 
 N      waiting on run queue after yield                          1442           0 
 N      waiting for last i/o on MASS to complete                   264           0 
 Y      waiting for mass destruction to complete                     9           0 
 Y      waiting to re-read page in parallel dbcc                    43           0 
 N      waiting for regular buffer read to complete                 37           0 
 Y      waiting for read to complete in parallel dbcc             1018           0 
 N      wait for mass read to finish when getting page               7           0 
 Y      wait for mass read to finish when getting page               1           0 
 N      waiting for buf write to complete before writing             8           0 
 Y      wait for MASS to finish changing before changing             4           0 
 N      waiting for MASS to finish writing before changing           2           0 
 
 (33 rows affected)

===Older versions===

 select WaitEventID,
        convert(numeric(16,0),Waits) as "Waits",
        convert(numeric(16,0),WaitTime) as "WaitTime"
        into    #waits
        from    monProcessWaits
 go
 select Description,
        convert(int,sum(w.Waits)) as "Count",
        convert(int,sum(w.WaitTime)/1000) as "Seconds"
        from    #waits w,
                monWaitEventInfo ei
        where   w.WaitEventID   = ei.WaitEventID
        group   by Description
        order   by 3 desc
 go

sample output:

 Description                                        Count       Seconds     
 -------------------------------------------------- ----------- ----------- 
 waiting for incoming network data                       383833     1875091 
 hk: pause for some time                                  39822      196370 
 xact coord: pause during idle loop                        2632      157911 
 waiting while allocating new client socket              133764      152920 
 checkpoint process idle loop                              1317       75351 
 wait for message                                        219335       68454 
 waiting on run queue after yield                       1887409       38657 
 waiting for disk write to complete                     1696820       15600 
 waiting for network send to complete                    275806        5105 
 wait for buffer write to complete                       657478        2169 
 waiting for semaphore                                    74468        2098 
 wait for mass to stop changing                           13421         298 
 wait for buffer read to complete                        112102          63 
 wait for object to be returned to pool                    3656           8 
 wait to acquire latch                                     3458           6 
 waiting for lock on PLC                                    525           3 
 waiting for CTLIB event to complete                         74           2 
 wait for checkpoint to complete                              2           0 
 wait for mass to finish changing                            50           0 
 waiting for mass to finish changing                        277           0 
 wait for access to a memory manager semaphore                4           0 
 wait for someone else to finish reading in mass             26           0 
 
 (22 rows affected)

[[Category:ASE]]
[[Category:MDA tables]]