0

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

  • What kind of storage are you using. Premium SSD ? Adding tempdb files will not help. You need to first find out root cause. What are the top waits ? – Shanky Oct 13 at 10:28
  • @Shanky unfortunately, nothing else is known in the scenario – Aleksey Vitsko Oct 13 at 10:29
  • I know there should be a process for performance tuning, but this time we got to pick an answer (one out of four) based on that short scenario and prove it. If you have any ideas, let me know if you agree with me (4) or with training's pick (1), or the answer should be something else (2 or 3) – Aleksey Vitsko Oct 13 at 10:36
2

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).

| improve this answer | |
  • hey Josh, how to tie Page Life Expectancy = 30 to TempDB ? btw what if they assumed PAGEIOLATCH under PAGELATCH_IO ? – Aleksey Vitsko Oct 13 at 13:49
  • "how to tie Page Life Expectancy = 30 to TempDB ?" - 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). – Josh Darnell Oct 13 at 13:56
  • 1
    @AlekseyVitsko "btw what if they assumed PAGEIOLATCH under PAGELATCH_IO ?" - there is no such wait type as PAGELATCH_IO. Please be more clear about what you're asking. – Josh Darnell Oct 13 at 13:57
  • yes I aware PAGELATCH_IO does not exist, but that's how they posted it in the question, and it is from respected source :(( thanks for helping in any case, info I get from you guys helps to develop better insight on things – Aleksey Vitsko Oct 13 at 16:53
  • 1
    @AlekseyVitsko You're welcome, and sorry it sounds like you got kind of sloppy training material :( If you're training for a Microsoft exam, I've had good experiences with these Cyber Vista practice tests. – Josh Darnell Oct 14 at 15:05
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. :-)

| improve this answer | |
  • Low quality questions like these tend to confuse us, if were are trying to take them seriously -- exactly, the worst problem that very similar questions show up when you do not expect them. Text changed, but meaning is kept – Aleksey Vitsko Oct 13 at 17:04
  • Indeed. I hope you didn't think I was aiming at you, Aleksey! I was referring to those who write such questions. If you are in any type of certification business, and write questions for tests, books etc, you have a responsibility to do a good job, IMO. Else you confuse the h*ll out of a lot of people! – Tibor Karaszi Oct 13 at 17:13
  • no worries my friend! I totally agree. Both answers are good, probably mark Josh as answer since he was first, thanks for the feedback anyway! – Aleksey Vitsko Oct 14 at 11:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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