MDA tables - queries for wait events
From SybaseWiki
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.
Contents
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)
