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 (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – Installing AdventureWorks for SQL Server
Next Post
SQLAuthority News – SQL Server 2008 R2 System Views Map

Related Posts

10 Comments. Leave new

  • 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.

    Reply
  • run perfmon utility

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

    it shows graphical memory pressure

    Reply
  • fabriciolimadba
    December 10, 2010 5:33 pm

    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

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

    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

    https://blogs.msdn.microsoft.com/

    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

    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

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

    Reply
  • can any body explain
    below query
    (select * from table) a

    what the purpose of “a” here.

    Reply
    • It gives name or alias to the subquery. Check this example:

      select a.foo, a.bar
      from (
      select 1 as foo, 2 as bar
      ) a

      Reply
  • Agree with Pinal. I always thought single pages kb plus multi pages kb is the actual use of memory. This post confirms my belief.

    Reply
  • Hello Pinal,
    Your query is not working.

    Reply
  • When it comes for tuning or pressure on memory we have to find if any query or proc which is taking more memory then required.e.g if a date column can be added on a sub query which is fetch 10 cr of records or using fast forward cursor.many things comes into play theory can’t be implemented as practical different environments means different tuning.

    Reply

Leave a Reply

Menu