question

SQLServerBro avatar image
0 Votes"
SQLServerBro asked ErlandSommarskog commented

pageiolatch_sh waits

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.

136329-image.png


sql-server-general
image.png (619.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SQLServerBro avatar image
0 Votes"
SQLServerBro answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SQLServerBro avatar image
0 Votes"
SQLServerBro answered ErlandSommarskog commented

@ErlandSommarskog 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)

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OK, so with that version Query Store is not available to you.

Then again, it seems that you have already identified the bad query. So I guess next step is to find from where this query is coming, and then have the application to fix the query. Or add better indexes to support it, as far as that is possible.

(Many years ago, I had a client that had a problem resemblent to yours, although their main concern they aw a high spike of physical disk reads. I tracked it down a query that ran a SELECT * on a 8GB table. On an instance with 10 GB of RAM... )

0 Votes 0 ·