SQL Server is having severe performance issue during testing, found out the PLE is under 500, one db session hung for 2 hours. I use the following queries to find out what's going on: Thanks to this link about query the buffer pool https://simplesqlserver.com/2016/01/04/query-the-buffer-pool/ select * from sys.dm_os_waiting_tasks where wait_type like 'PAGE%' --select * from sys.dm_os_waiting_tasks SELECT TOP 50 qs.execution_count, AvgPhysicalReads = isnull( qs.total_physical_reads/ qs.execution_count, 0 ), MinPhysicalReads = qs.min_physical_reads, MaxPhysicalReads = qs.max_physical_reads, AvgPhysicalReads_kbsize = isnull( qs.total_physical_reads/ qs.execution_count, 0 ) *8, MinPhysicalReads_kbsize = qs.min_physical_reads*8, MaxPhysicalReads_kbsize = qs.max_physical_reads*8, CreationDateTime = qs.creation_time, SUBSTRING(qt.[text], qs.statement_start_offset/2, ( CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2 ELSE qs.statement_end