Menu
I'm trying to troubleshoot some intermittent slowdowns in our application. I've got a separate question here with more details.
I ran sp_who2 to and I've noticed a few connections that have a status of SUSPENDED and high DiskIO. Can someone explain to me what that indicates?
442
DiskIO is number of reads and writes. CPUTIme is duration of the spid. Sometimes a spid will be sleeping (or orphaned) and this counter will continue to grow.
It means that the request currently is not active because it is waiting on a resource. The resource can be an I/O for reading a page, A WAIT it can be communication on the network, or it is waiting for lock or a latch. It will become active once the task it is waiting for is completed.
A query gets suspended when it is requesting access to a resource that is currently not available. This can be a logical resource like a locked row or a physical resource like a memory data page. The query starts running again, once the resource becomes available.
Check the status of the query using the SP_who2 command. After some time, use the KILL command to KILL SPID using the following command. Execute this command in a new query window. Once we execute the KILL SPID command, SQL Server starts the ROLLBACK process for this query.
This is a very broad question, so I am going to give a broad answer.
That is all that I can tell from the above screenshot. However, if I were to speculate, you probably have an IO subsystem that is too slow to keep up with the demand. This could be caused by missing indexes or an actually too slow disk. Keep in mind, that 15000 reads for a single OLTP query is slightly high but not uncommon.
53
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
