Chẩn đoán Memory Pressure

Cách 1: 

Monitoring SQL Server’s buffer pool is a great way to look out for memory pressure, and
Performance Monitor provides numerous counters to help you do this for quick insight, including
the following:
➤ MSSQL$<instance >:Memory Manager\Total Server Memory (KB) — Indicates the current
size of the buffer pool
➤ MSSQL$<instance >:Memory Manager\Target Server Memory (KB) — Indicates the
ideal size for the buffer pool. Total and Target should be almost the same on a server with
no memory pressure that has been running for a while. If Total is signifi cantly less than
Target, then either the workload hasn’t been suffi cient for SQL Server to grow any further
or SQL Server cannot grow the buffer pool due to memory pressure, in which case you can
investigate further.
➤ MSSQL$<instance >:Buffer Manager\Page Life Expectancy — Indicates the amount of
time, in seconds, that SQL Server expects a page that has been loaded into the buffer pool
to remain in cache. Under memory pressure, data pages are fl ushed from cache far more
frequently. Microsoft recommends a minimum of 300 seconds for a good PLE; this threshold
continues to be debated within the SQL Server community, but one thing everyone agrees
on is that less than 300 seconds is bad. In systems with plenty of physical memory, this will
easily reach thousands of seconds.

--Để query Page life expectancy

--Query total and target server memory
SELECT* FROM sys.dm_os_performance_counters WHERE counter_name like '%Server Memory%'



--query page life expectancy( lấy từ ebook SQL internall and troubleshooting)
SELECT cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Buffer Manager%' -- Handles named instances
AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);

--cách 2: tự viết
SELECT* FROM sys.dm_os_performance_counters where counter_name like '%Page Life%'

Theo: sql internal and troubleshooting

Thông số cntr_value chính là giá trị ta muốn xem