SQL memory pressure scripts

 

-- Breaks down buffers by object (table, index) in the buffer cache

SELECT  OBJECT_NAME(p.object_id)  AS   'ObjectName' , p.object_id,

p.index_id, COUNT (*)/128  AS   'buffer size(MB)' ,   COUNT (*)  AS   'buffer_count'

FROM  sys.allocation_units  AS  a

INNER   JOIN  sys.dm_os_buffer_descriptors  AS  b

ON  a.allocation_unit_id = b.allocation_unit_id

INNER   JOIN  sys.partitions  AS  p

ON  a.container_id = p.hobt_id

WHERE  b.database_id = db_id()

GROUP   BY  p.object_id, p.index_id

ORDER   BY  buffer_count  DESC       -- Get Top 25 executed SP's ordered by logical reads (memory pressure)       SELECT   TOP  25 qt.text  AS  'SP  Name ', total_logical_reads,        qs.execution_count AS  'Execution  Count ', total_logical_reads/qs.execution_count  AS  'AvgLogicalReads ',     qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS ' Calls/ Second ',        qs.total_worker_time/qs.execution_count AS  'AvgWorkerTime ',     qs.total_worker_time AS ' TotalWorkerTime ',     qs.total_elapsed_time/qs.execution_count AS ' AvgElapsedTime ',     qs.total_logical_writes,     qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,      DATEDIFF(Minute, qs.creation_time, GetDate()) AS ' Age  in  Cache'       FROM  sys.dm_exec_query_stats  AS  qs       CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle)  AS  qt       WHERE  qt.dbid = db_id()  -- Filter by current database       ORDER   BY  total_logical_reads  DESC