March 9, 2017 at 9:26 pm
Hi
I have a production DB Instance consuming 95% memory, JUST one database consuming all memory
All articles say - set max server memory on SQL as the only setting and enable AWE to allocate memory.
Out of 32 GB- 28 GB is allocated to SQL Server and its at 95% and I'm getting alerted.
These are top waits:
BROKER_RECEIVE_WAITFOR 14728997213
BROKER_TRANSMITTER 491847414
BROKER_EVENTHANDLER 286013973
How to fix this before the box crashes. This is production,so we cannot reboot whenever we want!
Thank you
March 9, 2017 at 10:24 pm
This was removed by the editor as SPAM
March 10, 2017 at 1:35 am
sqlguy80 - Thursday, March 9, 2017 9:26 PMHiI have a production DB Instance consuming 95% memory, JUST one database consuming all memory
All articles say - set max server memory on SQL as the only setting and enable AWE to allocate memory.
Normal, expected, documented behaviour. The articles are correct, set max server memory (AWE is not needed)
These are top waits:
BROKER_RECEIVE_WAITFOR 14728997213
BROKER_TRANSMITTER 491847414
BROKER_EVENTHANDLER 286013973
Those are benign waits, they're normal to see on any server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 10, 2017 at 7:40 am
it is normal for SQL Server to use as much memory as it can, since it wants to cache things like your data, indexes, query execution plans, etc in memory so it's not so dependent on disk I/O. You can get a simple overview like:
SELECT sm.total_physical_memory_kb / 1024 AS physical_mem,
pm.physical_memory_in_use_kb / 1024 AS sql_physical_used,
pm.locked_page_allocations_kb / 1024 as locked_in_mem,
c.value_in_use AS config_max,
sm.available_physical_memory_kb / 1024 AS avail_physical,
sm.system_cache_kb / 1024 AS system_cache,
pm.page_fault_count AS sql_page_faults, pm.memory_utilization_percentage AS mem_utilized_pct,
sm.system_memory_state_desc
FROM sys.dm_os_sys_memory sm
CROSS JOIN sys.dm_os_process_memory pm
CROSS JOIN sys.configurations c
WHERE c.name = 'max server memory (MB)'
There are some good scripts available to look at your wait stats and ignore the ones that don't really matter such as at:
http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
https://www.brentozar.com/archive/2014/03/t-sql-tuesday-stop-tuning-wait-stats-percentages/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply