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 Performance Tuning video course on Pluralsight on the topic – Using Memory optimized Tables and Native Stored Procedures.
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
Based on the request of many DBAs, In this SQL in Sixty Seconds video, I will show you the Impact of CHECKPOINT on Memory.
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.
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.