SQL Server Page Life Expectancy and Memory Status
Shows PLE, Memory Grants Pending and memory settings. Tells you quickly whether there is memory pressure.
SELECT counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Page life expectancy','Memory Grants Pending','Total Server Memory (KB)','Target Server Memory (KB)')
AND (object_name LIKE '%Buffer Manager%' OR object_name LIKE '%Memory Manager%');
SELECT physical_memory_kb / 1024 AS os_memory_mb,
(SELECT CAST(value_in_use AS INT) FROM sys.configurations WHERE name = 'max server memory (MB)') AS max_server_memory_mb
FROM sys.dm_os_sys_info;PLE consistently below 300 seconds per 4GB of buffer pool indicates severe memory pressure and cache thrashing.
- This script is designed for read-only diagnostics; it does not alter schema or modify data directly.
- Prior to executing on production systems under high load, verify the target database version (Min. Version: 2012).
- Exercise caution with mutating actions (e.g., terminating sessions or dropping indexes) referenced in the notes section.
Engineering Triad: Related Runbooks & Incident Response
Which production incidents and emergency drills execute this script as a diagnosis step?
Other SQLSERVER Diagnostic Scripts
View All (100)SQL Server Running Requests and Blocking Chain
Shows active requests, wait types, blocking sessions and the running statement together. The first diagnostic query to run during a slowdown.
Top CPU-Consuming Queries in SQL Server
Lists the top 20 queries by total and average CPU time from the plan cache. Pinpoints the source of CPU pressure at query level.
SQL Server Missing Index Recommendations (Missing Index DMVs)
Ranks the optimizer's missing index suggestions by estimated benefit. Merge them with existing indexes before applying.
SQL Server Index Fragmentation with Rebuild/Reorganize Advice
Measures fragmentation on large indexes and recommends rebuild or reorganize. Lets you plan maintenance windows without wasted work.