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
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
