SQL SEVER – Finding Memory Pressure – External and Internal

The following query will provide details of external and internal memory pressure. It will return the data how much portion in the existing memory is assigned to what kind of memory type.

SELECT TYPE, SUM(single_pages_kb) InternalPressure, SUM(multi_pages_kb) ExtermalPressure
FROM sys.dm_os_memory_clerks
GROUP BY TYPE
ORDER BY
SUM(single_pages_kb) DESC, SUM(multi_pages_kb) DESC
GO

What is your method to find memory pressure?

Reference: Pinal Dave (http://blog.sqlauthority.com)

7 thoughts on “SQL SEVER – Finding Memory Pressure – External and Internal

  1. Hi,

    We use mainly Zenoss for monitoring our servers. It gives warnings when the system is running out of memory or harddrive is filling up etc. I think it could be set to watch Sql Server memory statistics also via performance counters. I think it’s working rather good.

    The real problem comes when Sql Server is under heavy pressure and you need to decide what to do about it. Luckily in my case there’s always been some single query that’s taking a huge amount of resources. I’ve simply issued kill command and the server has continued normally.

    Like

  2. run perfmon utility

    add counter select Performance object SQLINSTANT:Memory
    ex. MSSQL$SQLEXPRESS:Memory and add allcounter

    it shows graphical memory pressure

    Like

  3. I use this querys to analise the memory (Dashboard memory):

    select counter_name ,cntr_value,cast((cntr_value/1024.0)/1024.0 as numeric(8,2)) as Gb
    from sys.dm_os_performance_counters
    where counter_name like ‘%server_memory%';

    –See code depot for full script
    — Page Life Expectancy
    SELECT cntr_value AS ‘Page Life Expectancy’
    FROM sys.dm_os_performance_counters
    WHERE object_name = ‘SQLServer:Buffer Manager’
    AND counter_name = ‘Page life expectancy’

    — Top 10 consumers of memory from buffer pool
    SELECT TOP (10) type, SUM(single_pages_kb)/1024. AS [SPA Mem, MB],SUM(Multi_pages_kb)/1024. AS [MPA Mem,MB]
    FROM sys.dm_os_memory_clerks
    GROUP BY type
    –HAVING SUM(single_pages_kb) + sum(Multi_pages_kb) > 20000
    ORDER BY SUM(single_pages_kb) DESC

    SELECT SUM(single_pages_kb)/1024. AS [SPA Mem, KB],SUM(Multi_pages_kb)/1024. AS [MPA Mem, KB]
    FROM sys.dm_os_memory_clerks

    — Get total buffer usage by database
    SELECT DB_NAME(database_id) AS [Database Name],
    COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
    FROM sys.dm_os_buffer_descriptors
    WHERE database_id > 4 ––- exclude system databases
    AND database_id 32767– –- exclude ResourceDB
    GROUP BY DB_NAME(database_id)
    ORDER BY [Cached Size (MB)] DESC;

    SELECT
    COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
    FROM sys.dm_os_buffer_descriptors
    WHERE database_id > 4 ––- exclude system databases
    AND database_id 32767– –- exclude ResourceDB

    Like

  4. 1. Look for a message in the Resource Manager Ring buffer

    http://blogs.msdn.com/b/grahamk/archive/2009/09/10/troubleshooting-sql-server-performance-problems-associated-with-low-memory-conditions-should-i-use-sys-dm-os-ring-buffers.aspx

    select convert(xml, record) from sys.dm_os_ring_buffers where ring_buffer_type = ‘RING_BUFFER_RESOURCE_MONITOR’

    2. Look for Free Pages below 640

    http://blogs.msdn.com/b/temenosonsql/archive/2010/03/24/creating-a-performance-baseline-part-2.aspx

    SELECT * FROM sys.dm_os_performance_counters WHERE counter_name = ‘Free pages’ AND [object_name] LIKE ‘%BUFFER MANAGER%’

    3. Look for Lazy Writes/sec above 20

    http://blogs.msdn.com/cfs-filesystemfile.ashx/__key/CommunityServer-Components-PostAttachments/00-09-00-06-02/PerfMon_5F00_Counters_5F00_Digest_5F00_w_5F00_Vital_5F00_Signs_5F00_Correlation_5F00_v6.xls

    DECLARE @per_second_counter TABLE (point_in_time datetime primary key clustered not null, read_value bigint not null);
    INSERT @per_second_counter (point_in_time, read_value)
    SELECT GETDATE(), cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = ‘Lazy Writes/Sec’ AND [object_name] LIKE ‘%BUFFER MANAGER%';
    while NOT EXISTS (SELECT 0 FROM @per_second_counter WHERE point_in_time DATEADD(SECOND, -11, later.point_in_time) AND earlier.point_in_time < later.point_in_time

    4. Look for Page Life Expectancy below 300

    http://blogs.msdn.com/b/rafidl/archive/2010/06/07/check-if-you-have-sql-memory-pressure-using-perfmon.aspx

    SELECT * FROM sys.dm_os_performance_counters WHERE counter_name = 'Page life expectancy' AND [object_name] LIKE '%BUFFER MANAGER%'

    Like

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