SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting more info out of dm_io_pending_io_requests


Getting more info out of dm_io_pending_io_requests

Author
Message
agustingarzon
agustingarzon
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 186
Hi there.
From time, the SQL Server I/O activity for a database we host starts increasing, up to the point that we start experiencing slowness, and associated issues. We are troubleshooting this in different ways.

Currently I'm interested in knowing if it's possible to connect the information from this DMV sys.dm_io_pending_io_requests with a specific query, or gather some more specific information out of it.

I mean, during the spikes, we will see many PENDING on DISK results http://imgur.com/a/NjRTj

Is it possible to get more concrete information out of it ? It says you have pending IO on disk, but this info alone is not enough.
E.g. want to know what's going on, what is the query or task triggering this increase.

I can easily reproduce this, e.g. if I rebuild a large index, the PENDING on DISK will go out of scale, but is there a way, by looking at those stats, to connect it back with whatever is causing it ? In my example an index rebuild command.

Cordially, Agustin Smile
Attachments
sHTycUS.png (34 views, 10.00 KB)
lmarkum
lmarkum
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3798 Visits: 901
I found that sys.dm_exec_requests has a Schedule_ID field in it. You can join dm_io_pending_io_requests to it on that field. Join to Exec_Requests and exec_sql_text and you can get what you are after. the first query I got from Pinal Dave: Here

Second query I came up with after looking at BOL.



SELECT vfs.database_id, df.name, df.physical_name
,vfs.FILE_ID, ior.io_pending
FROM sys.dm_io_pending_io_requests ior
INNER JOIN sys.dm_io_virtual_file_stats (DB_ID(), NULL) vfs
ON (vfs.file_handle = ior.io_handle)
INNER JOIN sys.database_files df ON (df.FILE_ID = vfs.FILE_ID);
GO

SELECT ipir.io_type, ipir.io_pending,
ipir.scheduler_address, ipir.io_handle,
os.scheduler_id, os.cpu_id, os.pending_disk_io_count,
ER.session_id, ER.command, ER.cpu_time, ST.text

FROM sys.dm_io_pending_io_requests ipir
INNER JOIN sys.dm_os_schedulers os ON ipir.scheduler_address = os.scheduler_address
INNER JOIN sys.dm_exec_requests AS ER ON os.scheduler_id = ER.Scheduler_id
CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) AS ST



Also, I'm not proficient with Extended Events, but I am wondering if you can create an XE session to get this information for you programmatically when the pending disk events occur.

agustingarzon
agustingarzon
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 186
Thanks a lot for your reply Smile
I've been testing it, and it's working as expected Smile When there is pending IO, this query will get you information about it.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search