This was a very interesting situation and at the end of we had found the culprit for the slow query and resolved it. Today we will discuss the experience.
Their server got tremendously slow with many sessions suspending in the status of SUSPENDED status and they were waiting on PAGELATCH_UP and PAGELATCH_SH.
How to Grow All Files in a File Group Equally? – TRACE FLAG 1117 – Interview Question of the Week #201
Question: How to Grow All Files in a File Group Equally?
Answer: If you are a regular reader of this blog, you would have read my earlier blog post writing about this subject over here: SQL Server 2016 – Introducing AutoGrow and Mixed_Page_Allocations Options – TraceFlags. However, let us try to get an answer to our question in this blog.