Memory troubleshooting with normal?

  • Hi,

    I have recevied email alert from server, for Message: Memory Utilization is 86%, threshold value for this monitor is 75%

    I have checked into SQL server side everything is normal but overall physical memory usage is 86%.

    If I restart SQL services then come back normal 20 % then again 86% after 1 weeks.

    Please anyone help, where is root case fixing?

    1. memory usage is with the TotalServerMemory

    SELECT CAST(physical_memory_in_bytes / (1024.0 * 1024.0 * 1024.0) AS DECIMAL(20, 2)) AS PhysicalMemoryGB,

    CAST((bpool_committed * 8) / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS BufferPoolCommittedMemoryGB,

    CAST((bpool_commit_target * 8) / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS BufferPoolTargetMemoryGB,

    (

    SELECT CAST(CAST(value_in_use AS INT) / 1024.0 AS DECIMAL(20, 2))

    FROM sys.configurations

    WHERE name = 'min server memory (MB)'

    ) AS MinServerMemoryGB,

    (

    SELECT CAST(CAST(value_in_use AS INT) / 1024.0 AS DECIMAL(20, 2))

    FROM sys.configurations

    WHERE name = 'max server memory (MB)'

    ) AS MaxServerMemoryGB,

    (

    SELECT cntr_value / 1024.0

    FROM sys.dm_os_performance_counters

    WHERE counter_name = 'Total Server Memory (KB)'

    ) AS TotalServerMemoryMB

    FROM sys.dm_os_sys_info;

    --output

    PhysicalMemoryGBBufferPoolCommittedMemoryGBBufferPoolTargetMemoryGBMinServerMemoryGBMaxServerMemoryGBTotalServerMemoryMB

    85.275.7905.975396.125

    2. track down the database that are taking up most of that memory.

    DECLARE @total_buffer INT;SELECT @total_buffer = cntr_value

    FROM sys.dm_os_performance_counters WHERE RTRIM([object_name]) LIKE '%Buffer Manager'

    AND counter_name = 'Total Pages';

    ;WITH src AS(SELECT database_id, db_buffer_pages = COUNT_BIG(*)

    FROM sys.dm_os_buffer_descriptors

    --WHERE database_id BETWEEN 5

    --AND 32766

    GROUP BY database_id

    )

    SELECT [db_name] = CASE [database_id] WHEN 32767 THEN 'Resource DB'

    ELSE

    DB_NAME([database_id]) END,

    db_buffer_pages, db_buffer_MB = db_buffer_pages / 128,

    db_buffer_percent = CONVERT(DECIMAL(6,3),

    db_buffer_pages * 100.0 / @total_buffer)

    FROM src ORDER BY db_buffer_MB DESC;

    --output

    db_namedb_buffer_pagesdb_buffer_MBdb_buffer_percent

    BRIBS521338407275.388

    tempdb566984428.199

    Resource DB3698280.535

    msdb1709130.247

    master48930.071

    model8000.012

    3. track down the objects that are taking up most of that memory

    use BRIBS

    go

    ;WITH src AS(SELECT [Object] = o.name,[Type] = o.type_desc,

    [Index] = COALESCE(i.name, ''),[Index_Type] = i.type_desc,

    p.[object_id], p.index_id,au.allocation_unit_id

    FROM sys.partitions AS p INNER JOIN

    sys.allocation_units AS au ON p.hobt_id = au.container_id

    INNER JOIN sys.objects AS o ON p.[object_id] = o.[object_id]

    INNER JOIN sys.indexes AS i ON o.[object_id] = i.[object_id]

    AND p.index_id = i.index_id WHERE au.[type] IN (1,2,3)

    AND o.is_ms_shipped = 0)SELECT src.[Object], src.[Type],

    src.[Index], src.Index_Type, buffer_pages = COUNT_BIG(b.page_id),

    buffer_mb = COUNT_BIG(b.page_id) / 128FROM src INNER JOIN

    sys.dm_os_buffer_descriptors AS b ON src.allocation_unit_id = b.allocation_unit_id

    WHERE b.database_id = DB_ID()GROUP BY src.[Object], src.[Type],

    src.[Index], src.Index_Type ORDER BY buffer_pages DESC;

    output attached in xls sheet

  • SQL Server will allocate all the memory you tell it can allocate. It will take it all, and keep it. It manages it's own memory internally. So if you have, for example, a 16gb system and you don't put a limit on the memory, SQL Server will begin allocating as much of it as it can until it maxes out and the OS & SQL SErver begin fighting for memory. If you put a limit of 14gb, then you'll SQL Server go right up to that limit. This is normal, expected behavior. It's not a problem. The only problem is when you don't have a limit. So make sure you always set an upper limit on SQL Server memory.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • +1000

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thank you for your reply...

    Total server memory 8 GB, I have set it max memory 6GB but SQL side using 5.79 GB, so it seems ok in sql side. But server admin people asking why SQL server used 75%?

    This is mail alert is overall physical memory usages including OS side.

    In second output, too much buffer page accoupied in cache memory either single paln etc..

    Do we remove single cache plan by using dbcc freesystemcache('sql plans;), if fire this command does performance isseus?

  • ananda.murugesan (9/20/2012)


    Thank you for your reply...

    Total server memory 8 GB, I have set it max memory 6GB but SQL side using 5.79 GB, so it seems ok in sql side. But server admin people asking why SQL server used 75%?

    This is mail alert is overall physical memory usages including OS side.

    In second output, too much buffer page accoupied in cache memory either single paln etc..

    Do we remove single cache plan by using dbcc freesystemcache('sql plans;), if fire this command does performance isseus?

    You can use DBCC FREEPROCCACHE(plan_handle) to get a specific plan out of the cache if you need to. BTW, if someone runs that query again, it'll be back in cache. To get the plan handle, use sys.dm_exec_query_stats in combination with sys.dm_exec_sql_text to find the one you want.

    Tell the sysadmin that SQL Server is going to take the amount of memory you allocate it. Period. It manages it's own memory internally. Read the chapter in the book, or better still, read it and give it to your sysadmin.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply