March 16, 2015 at 1:16 pm
Hi all,
I have a virtual server dedicated to a single installation of SQL Server Enterprise edition. The box has 224GB of RAM. 190GB was assigned to SQL Server, but the amount of available RAM was 7GB and Task Manager shows SQL Server using 212GB. I understand that some Windows processes will use RAM, so I released 10GB thinking that it will be released to Windows and the amount of available RAM will grow, but it didn't and SQL Server is still showing that it is using 212GB. I released another 10GB and the same story. How can I find out which non-data cache pool of SQL Server is using the freed RAM? I want to make sure that the usage is warranted for and that everything is optimized, but I am not even sure which pool is using up the released RAM. Thanks in advance.
March 16, 2015 at 2:20 pm
shahgols (3/16/2015)
Hi all,I have a virtual server dedicated to a single installation of SQL Server Enterprise edition. The box has 224GB of RAM. 190GB was assigned to SQL Server, but the amount of available RAM was 7GB and Task Manager shows SQL Server using 212GB. I understand that some Windows processes will use RAM, so I released 10GB thinking that it will be released to Windows and the amount of available RAM will grow, but it didn't and SQL Server is still showing that it is using 212GB. I released another 10GB and the same story. How can I find out which non-data cache pool of SQL Server is using the freed RAM? I want to make sure that the usage is warranted for and that everything is optimized, but I am not even sure which pool is using up the released RAM. Thanks in advance.
1) Please define exactly what " released 10GB " means.
2) DBCC memorystatus. Semi-documented online.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 16, 2015 at 3:56 pm
Yeah, sorry, I mean that I reduced the maximum memory size for SQL Server by 10GB.
Are there any DMVs that can help in stead of the cumbersome MEMORYSTATUS?
March 17, 2015 at 6:59 am
DBCC memorystatus may be cumbersome, but it breaks down the memory buckets/usage just like you need to see it for this issue I think.
Check out Glenn Berry's SQL Server Diagnostics Scripts for some good stuff.
It has been my experience that SQL Server doesn't immediately give back RAM when you reduce the max setting.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 17, 2015 at 8:28 am
You can use sys.dm_os_memory_clerks to check memory usage. It should give you the relevant information you are looking for.
select
type, sum(pages_kb) as pages_kb
from
sys.dm_os_memory_clerks
group by type
order by pages_kb desc
Another thing to check is whether your high sql server memory allocation is actually a problem. you can check via the sys.dm_os_ring_buffers to see if sql server or windows is complaining about low memory. The following query (http://sqlskills.com/blogs/jonathan/post/Identifying-External-Memory-Pressure-with-dm_os_ring_buffers-and-RING_BUFFER_RESOURCE_MONITOR.aspx) will show whether you need to reduce the max memory. If there are many rows with a 2 in the indicatorsystem column indicates external memory pressure, if the are 2 in the indicatorprocess column sql server has memory pressure.
SELECT
EventTime,
record.value('(/Record/ResourceMonitor/Notification)[1]', 'varchar(max)') as [Type],
record.value('(/Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') as [IndicatorsProcess],
record.value('(/Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') as [IndicatorsSystem],
record.value('(/Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [Avail Phys Mem, Kb],
record.value('(/Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [Avail VAS, Kb]
FROM (
SELECT
DATEADD (ss, (-1 * ((cpu_ticks / CONVERT (float, ( cpu_ticks / ms_ticks ))) - [timestamp])/1000), GETDATE()) AS EventTime,
CONVERT (xml, record) AS record
FROM sys.dm_os_ring_buffers
CROSS JOIN sys.dm_os_sys_info
WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') AS tab
ORDER BY EventTime DESC;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply