Databases Reference
In-Depth Information
One classic way of measuring whether SQL Server is under internal memory pressure is to look at its
Page Life Expectancy (PLE) (See Chapter 3, “Understanding Memory”), which you can do using the
query shown in Listing 15-27.
LISTING 15-27:
Page Life Expectancy information
-- Page Life Expectancy (PLE) value for default instance
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);
-- PLE is one way to measure memory pressure.
-- Higher PLE is better. Watch the trend, not the absolute value.
This query returns the current Page Life Expectancy (PLE) value, in seconds, for the default instance
of SQL Server. PLE is a measurement of how long SQL Server expects to keep in the SQL Server
buffer pool before it is l ushed or evicted. Higher PLE values are better than lower PLE values. You
should develop an awareness of the normal range of PLE values for your more important SQL Server
instances. That will help you identify a current PLE that is abnormally high or low.
Microsoft has a long-standing recommendation of 300 as a threshold for acceptable PLE, which is
often debated in the SQL Server community. One thing that everyone does agree on though is that
a PLE value of less than 300 is quite bad. Modern database servers with high amounts of physical
memory typically have much higher PLE values than 300. Instead of focusing on the current PLE
value, watch the trend over time.
After looking at Page Life Expectancy, you are going to want to look at Memory Grants
Outstanding, using the query shown in Listing 15-28.
LISTING 15-28:
Memory Grants Outstanding information
-- Memory Grants Outstanding value for default instance
SELECT cntr_value AS [Memory Grants Outstanding]
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Memory Manager%' -- Handles named instances
AND counter_name = N'Memory Grants Outstanding' OPTION (RECOMPILE);
-- Memory Grants Outstanding above zero
-- for a sustained period is a secondary indicator of memory pressure
This query returns the current value for Memory Grants Outstanding for the default instance of
SQL Server. Memory Grants Outstanding is the total number of processes within SQL Server that
have successfully acquired a workspace memory grant (refer to Chapter 3). You want this value to
be zero if at all possible. Any sustained value above zero is a secondary indicator of memory pres-
sure due to queries that are using memory for sorting and hashing. After looking at Memory Grants
Outstanding, you should also look at Memory Grants Pending (which is a much more important
indicator of memory pressure), by using the query shown in Listing 15-29.
Search WWH ::
Custom Search