Sql-server – How to find out how much i/o resources a task, session, request or transaction is currently using

dmvmonitoringsql serversql server 2014wait-types

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

Sometimes a session has a request, by request I mean the DMV sys.dm_exec_requests
or a session might have a transaction instead – sys.dm_tran_database_transactions

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.

enter image description here

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.

enter image description here

Best Answer

If you are looking for the actual physical I/O for a query, you should look in the XML showplan for the 'ActualPhysicalReads' element:

schemas.microsoft.com/sqlserver/2004/07/showplan

However, those physical reads are likely only going to occur the first time the plan is cached. Subsequent runs are more likely to pull the pages from memory, not from disk.

HTH