5

I am facing odd performance issues at one of my servers that is using SQL Server 2019 Standard Edition.

Users started complaining that the application is very slow/unresponsive. To check what is going on I logged in to SQL (without any issues) and I run sp_whoisactive without any parameters. It showed that my session is the only active one. So, I run sp_blitzfirst, also without any parameters and.... it took 90s it to be completed. The results were not super interesting. I have 0 warnings with priority between 1 and 199. The top wait pointed out as that script was PAGEIOLATEH_SH which was showing 84s of wait time (which btw is strange, because I have 8 cores, that script should compare snapshots took 5s apart so maximal possible wait time should be 40s, isn't it?).

When sp_blitzfirst was running I tried to check what is blocking that with sp_whoisactive and it was also showing that my sessions are the only one and it was showing in wait_info column (9ms)PAGEIOLATCH_SH:MMLIVE:1(*).

I needed to do some googling/reading to check what that wait type means and when I went back to the server after ~30 minutes the issue was gone (for example sp_blitzfirst was returning results within 6s).

It seems that it was not one-time incident as I was advised that it happened at least twice within last two weeks when I was not around.

So, my understanding is that performance issues are caused by slow SQL server (because at the time when they were reported even diagnostic queries that I run directly on the server were extremely slow). But at the same time, it cannot be caused by any process that is running directly on the database (because I would see that process with sp_whoisactive).... so, it has to be VM or hardware level. My understanding of the wait type that was top 1 during that time is that SQL struggling to pull data from disk to memory. So, with our sysadmin we checked VM statistics but there was absolutely nothing unusual. We have checked IO statistics for all VMs that share the same storage but there was also noting special (during the time when the issue was reported).

Unfortunately, I do not have third party monitoring for that server.

Could you please advise what it could be or/and what I could monitor to catch and confirm root cause during the next occurrence of the issue?

0

2 Answers 2

5

PAGEIOLATCH_SH is a wait type related to your disk, specifically waiting on a data page to be loaded off your disk. Based on the symptoms you've described, with nothing apparently running in the SQL Server instance, I would agree it's likely either a hardware issue or something else that was running on your server (outside of the SQL Server instance).

I had a bad disk issue occur in a VM (bug from VMWare) not too long ago that was hard to detect by looking at any I/O stats within SQL Server. But what ultimately helped me determine the issue was to use CrystalDiskMark to benchmark the disk itself directly on the server, to take my SQL Server instance out of the equation. So this is my first go-to whenever I suspect something wonky happening with my Disk outside of my SQL Server instance.

Outside of that, I would check if there's any disk-heavy operations that can be happening directly on the server too, e.g. anti-virus, server level backups / snapshots, Windows Task Scheduler jobs, etc.

The next time this happens, you can also open the Windows Resource Monitor which will show you what's consuming the most Reads and Writes on your Disk, in realtime.


For reference, this was my adventure where CrystalDiskMark helped me solve my problem. Further information, suggestions, and resolution in the chat.

2
  • Hi J.D. thanks for your suggestions. Yesterday the issue re-occurred and I tried to follow Brent's post. It turned out that the disks are slow indeed... but it is not likely that this is an exact root cause of the issue. Performance issues at my server are not cosistand (sometimes performance is acceptable, sometimes not) while the results from CrystalDiskMark are low... but stable no matter if I run that when issue exists or not. Commented Oct 18, 2022 at 7:59
  • @RadekGąska Right, how slow though?...and what type of disks are they?...even if they're consistently slow, but are far below the benchmark they should be getting, it may signal your problem is a disk issue (as in a bad disk or similar VM issue as I experienced). Did you also check the Windows Resource Monitor during the reoccurrence of the issue to see what is consuming most of the I/O during the slowness?...did those numbers align with CrystalDiskMark?
    – J.D.
    Commented Oct 18, 2022 at 11:50
0

Based on answers to this StackOverflow question, PAGEIOLATCH_SH wait types in a period of minimal load on the SQL Server with efficient queries may indicate problems with the disk subsystem, or other processes on the server (outside SQL Server itself) competing for resources. There are more details on suggested troubleshooting approaches over there.

See also the Microsoft documentation quoted in the accepted answer:

Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.