After a quick research we figured out the issue was with memory requirement for their query and we used to query specific wait statistics.
Only official documentation which I could find was about wait statistics related to Parallel Backup Queue is very little available online.
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.
SQL Server performance tuning is often considered as a complex subject and many DBAs and Developers often sees SQL Server Engines as a black box. However, the reality is very different as SQL Server Performance tuning is often a simple art of balancing three of the important resources for any system – 1) CPU, 2) Memory and 3) IO. In this blog post, we will discuss how we can reduce critical waits for queries and optimize server performance.
In the year 2017 just for an experiment, I decided to start a SQL Server Performance Tuning Practical Workshop. I had no idea, that I will be the biggest and most popular service delivered by SQLAuthority.com. Since the year 2017, I have been delivering one or two workshops every week.
While playing with my SQL Server in Azure VM, I faced an interesting issue. I did some change in networking to avoid internet access by creating an outbound rule and then I started facing this issue. In this blog, I would explain one of the possible causes of PREEMPTIVE_HTTP_EVENT_WAIT.