sys.dm_io_pending_io_requests – Day 6 – One DMV a Day

Hello Geeks and welcome to the Day 6 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

I will jump back to IO related DMVs after completing the most important DMV, sys.dm_db_index_operational_stats of Index DMVs on Day 5. So the second one in the IO related DMVs will be sys.dm_io_pending_io_requests. Doesn’t sound familiar? Yes, there are many such DMVs which are very informative and precise in detail but are not used much in our day to day job. We may thank Developers for writing good code OR the Infrastructure guys for giving you such good hardware OR the end users who are not noticing or not using your data to the limits. But when they complain you should be ready to face them with the information. After all we work with data and cannot say we do not have information collected about the data.

Coming back to our DMV story, sys.dm_io_pending_io_requests is very briefly described in MSDN as “Returns a row for each pending I/O request in SQL Server”. Very precise and yes, the name of the DMV explains you that. But what am I going to show today will help you start using this DMV to answer questions like How much IO is pending on my disks/network.

sys.dm_io_pending_io_requests is a DMV which does not hold cumulative information. This is a point-in-time data and gives you the information while it is happening in the background. So what details does this DMV give? The most important columns to notice are these

io_type – IO request type
io_pending – Request status which will tell if the IO request is completed by Windows. Once the SQL task which sent the request gets it CPU time it will consume the data and remove the entry from this DMV.
scheduler_address – Scheduler on which this IO request was requested. This will help you narrow down any sessions running on that scheduler which may be waiting on IO requests. Precisely, pending_disk_io_count column in sys.dm_os_schedulers gets info from sys.dm_io_pending_io_requests.
io_handle – Maps to file handle of file used for IO. This maps to file handle in sys.dm_io_virtual_file_stats.

   

The syntax of sys.dm_io_pending_io_requests is the simple select query. I will join this DMV with sys.dm_os_schedulers to get the information of scheduler which sent this request.

SELECT	ipir.io_type, ipir.io_pending,
	ipir.scheduler_address, ipir.io_handle,
	os.scheduler_id, os.cpu_id, os.pending_disk_io_count
FROM sys.dm_io_pending_io_requests ipir
INNER JOIN sys.dm_os_schedulers os
ON ipir.scheduler_address = os.scheduler_address

sys.dm_io_pending_io_requests

From the above output you will have some basic information about the scheduler on which the IO is pending and how many IO requests are pending on each scheduler for the disk or network IO. So you can tell, as I have already color coded for ease of reading, scheduler 9 has 10 pending IO requests and other CPUs are comparatively less loaded.

Tomorrow I will be covering another important IO related DMV. So, stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook

   

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published.