Is SQL Server under high Memory Pressure?

A few weeks ago, i started a blog post series that covers the finding of performance bottlenecks in SQL Server. In the first blog post in the series https://cms4j.wordpress.com/2014/09/30/is-sql-server-under-high-cpu-pressure/
i focused on CPU Pressure.

In this blog post i will explain how to decide from just a few queries to SQL Server DMVs if SQL Server is under high Memory pressure.

We all know there are far more reasons why SQL Server could be slow, but let’s stick with Memory for now.

Page Life Expectancy

A first and very good indicator is the so called Page Life Expectancy (PLE) that can be queried by the DMV sys.dm_os_performance_counters. The Page Life Expectancy tells us how long a data page that was read from disk subsystem will stay in SQL Server’s Buffer Pool.
In former times when SQL Server just had about 4 GB of RAM a value of 300 (seconds) was recommended by Microsoft. But times are changing, so this is no more longer the amount of RAM that you will find in nowadays SQL Servers.

Jonathan Kehayias has a very good blog post about this:
http://www.sqlskills.com/blogs/jonathan/finding-what-queries-in-the-plan-cache-use-a-specific-index/ -> What’s Wrong about Page Life Expectancy >= 300?

So depending on your Servers RAM you have to define a different value than 300.
Jonathan calculates
Recommended PageLifeExpectancy = Physical RAM / 4 * 300

Top Wait Type

SQL Server always provides you with statistics what he is waiting on for the most of the time. This can be queried from the DMV sys.dm_os_wait_stats.
With high memory pressure i often experienced the Wait Type PAGEIOLATCH_XX as the top wait type.
This is because not all data pages that SQL Server wants to hold in the Buffer Pool fit into it – so SQL Server has to drop some pages from the Buffer Pool in order to read some new fresh pages from the disk subsystem.
Please note that this Wait Type must not always mean that you have Memory Pressure. It could also be a slow disk subsystem or a poor indexing strategy, because there are too many table scans instead of index seeks.
Please take a look at Paul Randals blog post http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/.
I added two more wait types to the “benign” waits: “BACKUPBUFFER” and “BACKUPBIO” as the backup process does not make use of the Buffer Pool.

Buffer Pool Rate

Joining the discussion of the right value for Page Life Expectancy Thomas LaRock brought up another calculation for his so called Buffer Pool Rate. In his blog post
http://thomaslarock.com/2012/05/are-you-using-the-right-sql-server-performance-metrics/ -> 5. BUFFER POOL I/O RATE
he develops this value as
MAX MEMORY / PLE = xx.x MB/sec
He likes to have it below 20 MB/sec.

Building the T-SQL Script

So with these three criteria i built a script.

/*
	CMS, 05.12.2014:
	Is SQL Server under Memory Pressure?
    
    This check consists of 3 Steps:
    1) Check Actual Page Life Expectancy
    2) Check Top Wait Stat                  - with the help of Paul Randal's Wait Stats CHECK
                                              http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
    3) Check Buffer Pool Rate               - With the help of SQL Rockstars Buffer Pool Rate Calculation
                                              http://thomaslarock.com/2012/05/are-you-using-the-right-sql-server-performance-metrics/ -> Buffer Pool I/O Rate
    
*/

SET NOCOUNT ON

DECLARE @MaxServerMemory FLOAT
DECLARE @ActualPageLifeExpectancy FLOAT
DECLARE @RecommendedPageLifeExpectancy FLOAT
DECLARE @RecommendedMemory FLOAT
DECLARE @TopWaitType sysname
DECLARE @BufferPoolRate FLOAT

-- ####################################################################################################
SELECT  @MaxServerMemory = (1.0 * cntr_value / 1024 / 1024)
		, @RecommendedPageLifeExpectancy = Convert(INT ,(1.0 * cntr_value) / 1024 / 1024 / 4.0 * 300)
FROM    sys.dm_os_performance_counters
WHERE   counter_name = 'Target Server Memory (KB)'

SELECT  @ActualPageLifeExpectancy = 1.0 * cntr_value
FROM    sys.dm_os_performance_counters
WHERE   object_name LIKE '%Buffer Manager%'
AND		LOWER(counter_name) = 'page life expectancy'

-- ####################################################################################################
/*
Check TOP Wait Type.
If the TOP 1 Wait Type is PAGEIOLATCH_SH it indicates that SQL Server is waiting on datapages to be read 
into the Buffer Pool.
Memory Pressure might not be the root cause, it can be a poor indexing strategy
because way too many pages have to be read into the buffer pool and they are deleted from the buffer pool too early.
*/
;WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER',             N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP',                N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER',              N'CHECKPOINT_QUEUE',
        N'CHKPT',                           N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT',                N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT',              N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE',           N'DBMIRRORING_CMD',
        N'DIRTY_PAGE_POLL',                 N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC',                        N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT',     N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL',               N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT',            N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK',                 N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP',                  N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE',                    N'ONDEMAND_TASK_QUEUE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'REQUEST_FOR_DEADLOCK_SEARCH',     N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK',               N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP',                 N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY',             N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED',            N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK',                N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP',             N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP',     N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES',           N'WAIT_FOR_RESULTS',
        N'WAITFOR',                         N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_HOST_WAIT',              N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE',             N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT',              N'XE_TIMER_EVENT')
    -- @cms4j: do not take BACKUP into Account, as it does not use the Buffer Cache
	AND  [wait_type] NOT IN (
        N'BACKUPBUFFER', N'BACKUPIO')
    AND [waiting_tasks_count] > 0
 )
SELECT @TopWaitType = (SELECT top 1
    MAX ([W1].[wait_type])
    FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95); -- percentage threshold


-- ####################################################################################################
-- SQL Rockstar comes up with BUFFER POOL RATE. But that depends on SUPER DUPER STORAGE. If that is low, the BufferPoolRate will also be slow.
SELECT  @BufferPoolRate = ( 1.0 * cntr_value / 128.0 )
        / ( SELECT  1.0 * cntr_value
            FROM    sys.dm_os_performance_counters
            WHERE   object_name LIKE '%Buffer Manager%'
                    AND LOWER(counter_name) = 'page life expectancy' )
		FROM    sys.dm_os_performance_counters
WHERE   object_name LIKE '%Buffer Manager%'
        AND counter_name = 'Database pages'

-- ####################################################################################################
-- Calculate Recommended Max Memory
SELECT @RecommendedMemory = Convert(INT, @RecommendedPageLifeExpectancy / @ActualPageLifeExpectancy * @MaxServerMemory)

-- ####################################################################################################
-- Put all the things together...
SELECT	CASE
			WHEN @RecommendedMemory > @MaxServerMemory AND @TopWaitType LIKE 'PAGEIOLATCH_%' AND @BufferPoolRate > 20.0 THEN '3 x YES'
            WHEN
				(@RecommendedMemory > @MaxServerMemory AND @TopWaitType LIKE 'PAGEIOLATCH_%')
				OR
				(@RecommendedMemory > @MaxServerMemory AND @BufferPoolRate > 20.0) THEN '2 x YES'
            WHEN @RecommendedMemory > @MaxServerMemory AND @TopWaitType NOT LIKE 'PAGEIOLATCH_%' THEN '1 x YES, TOP Wait: ' + @TopWaitType
            WHEN @RecommendedMemory < @MaxServerMemory  THEN 'no'
        END AS [Is SQL Server under Memory Pressure?]
        , @MaxServerMemory AS [Max Server Memory (GB)]
        , @ActualPageLifeExpectancy AS [Actual PLE]
        , @RecommendedPageLifeExpectancy AS [Recommended PLE]
        , @RecommendedMemory AS [Recommended Memory (GB)]
        , @TopWaitType AS [Top Wait Type]
        , @BufferPoolRate AS [BufferPool Rate, you want < 20]

So if all of the three criteria equal to “true” the script will show ‘3 x YES’.
If it’s Page Life Expectancy and PAGEIOLATCH_XX or Page Life Expectancy and Buffer Pool Rate, you will get ‘2 x YES’.
Otherwise you will get other results as shown in the script.

Please keep in mind that this is just the starting point where you have to investigate further and proove your first results with more detailed reasons why SQL Server is under high Memory Pressure.

Thank you for reading,
Christoph Müller-Spengler

Other blog posts in that series:

Advertisements
This entry was posted in SQL Server and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s