Problem: the database has high disk usage and is in a deadlock, possibly due to a resource conflict.
The road to here:
Captured a few counters to see where the problem is, looking at an outrageously high page reading (56 billion), page writing (330 million) per second with PLE = 19 s, I thought the server was running out of memory, d & Where the high number of errors which in turn leads to a high IO disk, but now this is more like a side effect of another operation.
56 billion I / O translates to 453 TB with a page size = 8 KB, which is much higher than the actual database size = 3.2 TB, I don't know how that is possible. Can we count on these metrics?
After this point, the server was restarted / reset and the last memory usage was 76% with the following buffer manager counters captured using the last request from this blog
High checkpoint pages / sec can cause high page faults (checkpoints and transaction logs)
My question is:
- Since all of the counters tell us about memory usage, I guess they are interdependent or at least relative to some extent. I could not find the recommended values for these counters in msdn. So, how to interpret the figures without reference?
- If the checkpoint is the problem, how do you know for sure?
- If not, can you provide some input to dig into the root cause?