4

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

  • 1
    Those are physical I/O tasks, so I'm not sure they would report logical I/O. In other words, if the page is already in memory, then there is no pending I/O. – SQLRockstar Oct 8 '15 at 13:46
  • good point- but then how can I find the disk i/o on the current processes, either linking to requests, sessions or transactions – marcello miorelli Oct 8 '15 at 13:54
  • 2
    Have you looked at the sys.dm_io_virtual_file_stats DMV? – SQLRockstar Oct 8 '15 at 23:02
  • I think that will be the way after all. what I really wanted is per object rather than per file. I know this could be mapped, but not exclusively of an object in particular, unless the object is the only object in a file. – marcello miorelli Oct 9 '15 at 9:28
  • 1
    The XML showplan has the 'ActualPhysicalReads' element, maybe you could parse this out? schemas.microsoft.com/sqlserver/2004/07/showplan – SQLRockstar Oct 13 '15 at 15:35
0
+50

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

  • Just curious how this answers the question. The plan wouldn't parse out what is currently running. – Solomon Rutzky Oct 14 '15 at 14:47
  • @srutzky - you are correct - it doesnt 100% answer the question. I am not even sure I will be able to achieve what I wanted, but this question gave me a possibility that I want to work on at a later time. – marcello miorelli Oct 14 '15 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.