Relation of PAGELATCH waits with PAGEIOLATCH and WRITELOG

Question

I am seeing increase in PAGELATCH, PAGEIOLATCH and WRITELOG waits after migration to SQL Server 2017 (from SQL Server 2008). While I can explain increase in PAGEIOLATCH and WRITELOG (we've other disks - HDD instead of SSD), but don't know why PAGELATCH goes up too.

Are they anyhow related?

What I can see around they are not, cause PAGELATCH belongs to memory, while the others to IO.

Answer 1

While I can explain increase in PAGEIOLATCH and WRITELOG (we've other disks - HDD instead of SSD)

I would not come to that conclusion without doing more analysis. Some of the possible cause Paul Randal mentioned in this article.

Here are some possible root causes (not an exhaustive list):

• External Windows memory pressure on SQL Server causing the memory manager to reduce the buffer pool size
• Plan cache bloat causing extra memory to be borrowed from the buffer pool

• A query plan doing a table/clustered index scan (instead of an index seek) because of:

  a workload volume increase
  a parameter sniffing problem 
  a required nonclustered index that was dropped or changed 
  an implicit conversion
  

Are they anyhow related?

No. Read this article for possible causes and solution.

Knee-Jerk Wait Statistics : PAGELATCH by Paul Randal.

Also read the answer of this question.

High PAGELATCH_* and WRITELOG waits. Are they related?

I suggest you look at your workload and identify what changed between 2008 and 2017. Try to corollate those changes to the change in your wait-type. The New and Improved Cardinality Estimator was released in SQL Server 2014, which might not produce optimal plan for all your code written for 2008. Keeping in mind hardware change can also play a role. Once you have all the information you will be able to identify the root cause of changes in wait-type.

The New and Improved Cardinality Estimator in SQL Server 2014