After a quick research we figured out the issue was with memory requirement for their query and we used to query specific wait statistics.
SQL SERVER – Enable or Disable Resource Governor
Resource governor which was accidentally enabled by one of their database administrators. Let us learn how to disable resource governor in this blog post.
SQL SERVER – Using Memory-optimized Tables and Native Stored Procedures – Video Course
SQL Server Performance Tuning video course on Pluralsight on the topic – Using Memory optimized Tables and Native Stored Procedures.
Impact of DBCC DROPCLEANBUFFERS on Memory – SQL in Sixty Seconds #085
Based on the request of many DBAs, In this SQL in Sixty Seconds video, I will show you the Impact of DBCC DROPCLEANBUFFERS on Memory
Impact of CHECKPOINT On Memory – SQL in Sixty Seconds #084
Based on the request of many DBAs, In this SQL in Sixty Seconds video, I will show you the Impact of CHECKPOINT on Memory.
SQL SERVER – SUSPENDED Sessions Waiting on PAGELATCH_UP and PAGELATCH_SH – Solution
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 – Increasing Speed of CHECKPOINT and Best Practices
I received this question as a follow up of my earlier blog post which I wrote about SQL SERVER – Impact of CHECKPOINT and DBCC DROPCLEANBUFFERS on Memory.