How to find out how much i/o resources a task, session, request or transaction is currently using?
by task I mean the DMV called DMV sys.dm_os_tasks.
by session I mean the DMV sys.dm_exec_sessions
How can I link these things with I/O usage?
I had a look at the DMV called DMV sys.dm_os_tasks .
The DMV sys.dm_os_tasks has some interesting columns to find out more about processes currently running in sql server.
sys.dm_os_tasks – not showing pending_io_byte_count
However, when I try to identify how much I/O each of my tasks is dealing with the pending I/O in bytes is always zero.
This is an example of the results of my query while I was trying to monitor some CXPACKETS WAITS.
Pending_IO_count seems to be fine, but pending_IO_by_counts are always zero.
what could be an alternative?
I want to measure the I/O usage of each parallel process.
in fact it could be just the I/O usage of each session or request.
even sessions without request sometimes are good to see, because they might be blocking other processes.