SQL Server Buffer Cache details:- 

———————————————————————————————-

select

       [DateTimePerformed] = SYSDATETIMEOFFSET()

,      Server_Physical_Mem_MB = os.[Server Physical Mem (MB)] — SQL2012+ only

,      c.[Min_Server_Mem_MB]

,      c.[Max_Server_Mem_MB]

,      p.PLE_s

,   p.Stolen_Mem_MB                                                                                                        

,      ‘Churn (MB/s)’                    =      cast((p.Total_Server_Mem_GB)/1024./NULLIF(p.PLE_s,0) as decimal(19,2))

,      Server_Available_physical_mem_GB = (SELECT cast(available_physical_memory_kb / 1024. / 1024. as decimal(19,2)) from sys.dm_os_sys_memory)

,      SQL_Physical_memory_in_use_GB = (SELECT cast(physical_memory_in_use_kb / 1024. / 1024. as decimal(19,2)) from sys.dm_os_process_memory)

,      p.Total_Server_Mem_GB –May be more or less than memory_in_use because it

,      p.Target_Server_Mem_GB    

from

( select

       InstanceName = @@SERVERNAME

,      Target_Server_Mem_GB =     max(case counter_name when ‘Target Server Memory (KB)’ then convert(decimal(19,3), cntr_value/1024./1024.) end)

,      Total_Server_Mem_GB  =      max(case counter_name when  ‘Total Server Memory (KB)’ then convert(decimal(19,3), cntr_value/1024./1024.) end)

,      PLE_s  =      max(case counter_name when ‘Page life expectancy’  then cntr_value end)

, Stolen_Mem_MB = max(case counter_name when ‘Stolen Server Memory (KB)’   then convert(decimal(19,3), cntr_value/1024.) end )

from sys.dm_os_performance_counters

) as p

cross apply (SELECT ‘InstanceName’ = @@SERVERNAME

              , cpu_count , hyperthread_ratio AS ‘HyperthreadRatio’

              , cpu_count/hyperthread_ratio AS ‘PhysicalCPUCount’

              , ‘Server Physical Mem (MB)’ = cast(physical_memory_kb/1024. as decimal(19,2))   — SQL2012+ only

              FROM sys.dm_os_sys_info ) as os

cross apply (select 

         Min_Server_Mem_MB = max(case when name = ‘min server memory (MB)’ then convert(bigint, value_in_use) end)

       , Max_Server_Mem_MB = max(case when name = ‘max server memory (MB)’ then convert(bigint, value_in_use) end)

from sys.configurations) as c

———————————————————————————————-

–SQL Server Memory Grant Wait info

———————————————————————————————-

select

              mg.session_id

       ,      offsettext    =      CASE WHEN r.statement_start_offset = 0 and r.statement_end_offset= 0 THEN NULL

                     ELSE

                           SUBSTRING (est.[text], r.statement_start_offset/2 + 1,

                                  CASE WHEN r.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), est.[text]))

                                  ELSE r.statement_end_offset/2 – r.statement_start_offset/2 + 1

                                  END)

                     END

       ,      mg.group_id

       ,      mg.request_time

       ,      mg.grant_time

       ,      requested_memory_gb =      mg.requested_memory_kb/1024./1024.

       ,      granted_memory_gb    =      mg.granted_memory_kb/1024./1024.

       ,      mg.required_memory_kb

       ,      used_memory_gb             =      mg.used_memory_kb/1024./1024.

       ,      mg.max_used_memory_kb

       ,      ideal_memory_gb            =      mg.ideal_memory_kb/1024./1024.

       ,      mg.query_cost

       ,      r.granted_query_memory

       ,      r.status

       ,      [db_name] = db_name(r.database_id)

       ,      r.wait_time

       ,      r.wait_type

       ,      r.cpu_time

       ,      r.total_elapsed_time

       ,      r.reads

       ,      r.writes

       ,      r.logical_reads

       ,      est.objectid

       ,      est.text

from sys.dm_exec_query_memory_grants mg

inner join sys.dm_exec_requests r on mg.session_id = r.session_id

outer apply sys.dm_exec_sql_text (r.sql_handle) est

order by query_cost desc

GO

———————————————————————————————-