September 20, 2012 at 3:57 am
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
September 20, 2012 at 6:24 am
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
September 20, 2012 at 6:29 am
http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/ Chapter 4 - memory management
September 20, 2012 at 6:36 am
anthony.green (9/20/2012)
http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/ Chapter 4 - memory management
+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
September 20, 2012 at 6:37 am
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?
September 20, 2012 at 6:42 am
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