PAGEIOLATCH wait and Page Life Expectancy very low

Question

Training for SQL Server, has following scenario:

SQL Server on Azure VM, DS-series machine, it has 32 GB of RAM
Server has below performance conditions:

- very high PAGELATCH_IO waits
- average Page Life Expectancy is 30
- nothing else is known

What should be done in order to decrease PAGELATCH_IO waits ?
Choose correct answer (need to pick just one):

1. Add more tempdb files
2. Enable large-page support
3. Enable lock pages in memory
4. Configure buffer pool extensions

Training says that the correct answer is 1) add more tempdb files

I think correct answer is 4) Configure buffer pool extensions, based on:

- PLE is a number of seconds a page will stay in the buffer pool without removing
- PLE = 30 is too low, means high turnover in the buffer pool
- DS-series support Premium SSD drives, can place buffer pool extension on Premium SSD

But could be also this 3) Enable lock pages in memory based on this https://sqlperformance.com/2014/06/io-subsystem/knee-jerk-waits-pageiolatch-sh

Who is right - me, Training material, or you (your option) ?

By the way, not sure what they mean under PAGELATCH_IO, that could be PAGEIOLATCH or PAGELATCH, I guess answer depends on exactly which type we have

Answer 1

It seems that you haven't reproduced the question from the training materials here with complete accuracy.

Assuming that the question was about PAGELATCH_** waits, and that the latched pages are in the tempdb database, then those waits can be a common sign of tempdb allocation contention. And the most common solution to that is to increase the number of tempdb files.

See this Microsoft Support article:

Recommendations to reduce allocation contention in SQL Server tempdb database

To improve the concurrency of tempdb, try the following methods:

• Increase the number of data files in tempdb to maximize disk bandwidth and reduce contention in allocation structures. As a general rule, if the number of logical processors is less than or equal to eight (8), use the same number of data files as logical processors. If the number of logical processors is greater than eight (8), use eight data files. If contention continues, increase the number of data files by multiples of four (4) up to the number of logical processors until the contention is reduced to acceptable levels. Alternatively, make changes to the workload or code.

If we already suspect tempdb contention (because of the latch waits), then PLE = 30 could be caused by the heavy tempdb usage (if you think of lots of tempdb pages being loaded into memory and released repeatedly, driving down PLE).

Thus option 1 would most likely be the correct answer, although more information would be needed to confirm (for example, knowing that the latch waits are in fact in tempdb).

Answer 2

The problem with hypothetical questions is that we can't ask for more info. Nor can we ask for clarifications (did you mean PAGELATCH_IO, PAGEIOLATCH or PAGELATCH?).

Seeing just PAGEIOLATCH and low PLE doesn't necessarily mean that it is tempdb. 32 GB RAM is not much nowadays, but we know nothing more about this environment. We are waiting for I/O, that is all we know. Perhaps we just "need" more RAM?

Throwing files on tempdb don't mean we do less IO. OTOH, if it was PAGELATCH (in contrast to PAGEIOLATCH) we were waiting for, in tempdb, then more files might be "it".

In the end, I'd say throw more RAM or tune the queries. None of the suggested alternatives are reasonable.

Low quality questions like these tend to just confuse us, if were are trying to take them seriously. :-)