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