pageiolatch_sh waits

Question

hi Folks,
in one our database server occasionally we run into issue where queries take long time to execute and application/sql mgmt studio becomes unresponsive. After digging further i find there of lot of unusually high number of active/suspended sessions on the server for instance average volume of the active sessions is around 150-200, i see during this problematic phase sessions go upto 800-900 and everything slows down.

When checked , there are blocks on the server but they come and go, there is not one particular blocking session holding everything else.

When i check what these sessions are waiting for it is waiting on PAGEIOLATCH_SH , pls see attached screenshot from sp_whoisactive. IMO there is one particular query that runs rogue and consumes memory on the server that results into reading pages from the disk which results into pageiolatch_sh wait. Is my understanding correct? Question is how can i find out during this time which query was causing the issue if the issue happens again.

Answer 1

When i check what these sessions are waiting for it is waiting on PAGEIOLATCH_SH , pls see attached screenshot from sp_whoisactive. IMO there is one particular query that runs rogue and consumes memory on the server that results into reading pages from the disk which results into pageiolatch_sh wait.

You mean that there was a single query that read a really large table into memory, squeezing out everything else, so all queries had to read from disk for a while?

One approach is to check sys.dm_index_usage_stats to see if there is an index that has a high number of physical reads.

If you have Query Store active for this database, you can check what comes on top for the time of the incident in terms of physical reads.

Answer 2

Thanks Erland, i will check it out. I have not enable query store due to its reputation of consuming resources on the server ? is this correct? Does query store consumes significant resources on the server?

Another question pertaining to my problem described above, if im seeing most of the sessions waiting for pageiolatch_sh resource does it mean there could that one particular session or multiple sessions fighting for buffer space ? or my server itself is going through memory contention? How can i conclude that its few sessions going causing momentary memory issue or overall i have issue with memory allocation with respect to sql server.

Answer 3

Thanks Erland, i will check it out. I have not enable query store due to its reputation of consuming resources on the server ? is this correct? Does query store consumes significant resources on the server?

There are workloads that takes a significant toll from Query Store, to the extent that you cannot use it. These are typically loads with many short queries. And obviously, a system which is close to the edge already, can be pushed over that edge by adding Query Store on top.

However, given the wealth of information you get from Query Store, there is all reason to try it. Normally, the overhead is only a 3-4 per cent.

Another question pertaining to my problem described above, if im seeing most of the sessions waiting for pageiolatch_sh resource does it mean there could that one particular session or multiple sessions fighting for buffer space ? or my server itself is going through memory contention? How can i conclude that its few sessions going causing momentary memory issue or overall i have issue with memory allocation with respect to sql server.

My interpretation of your initial post was that you have isolated instances of these heaps of pageiolatch_sh waits, and those could be due to an evil cache flusher. If you have these waits constantly, it could be that you are short of memory for the workload. How mcuh RAM is there in the machine? What is the size of the database? What does "SELECT @@version" report?

But it can also be that there are some queries that need tuning and better indexing. If you enable Query Store and wait a few days, you can see which are the most resource-consuming queries, and you know what you need to tune.

Answer 4

@Erland Sommarskog thanks for the response . Our data size is around 1.2TB Index size is around 400G. It seems like there is only 1 particular query when it runs consumes resources on the server. Last few days have been fine. Whenever i see this query i kill it. Its a SELECT statement which is not critical for the application.

select @@version gives following info.

Microsoft SQL Server 2014 (SP3-CU4-GDR) (KB4583462) - 12.0.6433.1 (X64) Oct 31 2020 02:54:45 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 14393: ) (Hypervisor)