2

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.

  • Two things, did you ran full database stats update after upgrade and have you tested your application to SQL Server 2017 mainly because of new CE also make sure you are on latest SQL Server 2017 CU – Shanky Jan 22 '18 at 15:30
  • Actually no I didn't update all statistics - just for the modified tables. Legacy Cardinality Estimator is set to True. We are on CU1, so not the latest. Thanks – jerik1 Jan 22 '18 at 15:34
  • Okay so seriously stop now, update complete statistics and I believe you would see some improvements. This is MUST with new CE – Shanky Jan 22 '18 at 15:39
  • Also note that you can see an increase in waits caused by a decrease in CPU utilization. EG you had a query that went from 2000ms of CPU to 200ms of CPU, you have less time to harden the log records before the query finishes and enters a WRITELOG wait. – David Browne - Microsoft Jan 22 '18 at 17:41
  • Well, in fact out cpu related waits also has increased not contrary :-( – jerik1 Jan 22 '18 at 17:59
0

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

  • Thank you. I thought the same - that they are not related? I set Legacy Cardinality Estimator to True. Except IO subsystem everything else should be the same. Nothing has changed in a workload. – jerik1 Jan 22 '18 at 15:27

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.