SQLTeam.com | Weblogs | Forums

SQL Server 20014 is not using configured memory

Hi,

we are using sql 20014. system specifications :4CORE &128GB RAM . We configured max memory 110460 mb & min memory as 1024 kb.
Users complaining that SQL peformance is very slow.
when we checked the task manager sql.exe utilising 62 % memory.
when i tried to check the memory using below query its showing that 72 gb memory is using.
select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

o/p:
Memory_usedby_Sqlserver_MB =72720 mb.

somehow it is confirmed that SQL Server is not using configured memory.
How to make sure the sql server using high memory? please help. I need to ressolve this as soon as possible.

Thanks,
Jo

Specifying max memory does not mean that SQL Server will immediately grab the max amount you specified. As and when it needs more memory, SQL OS will ask Windows OS for more memory up to the maximum you specified. Once it gets the memory, SQL OS does its own memory management. That is to say, will not release the memory back to Windows OS even if it does not immediately need it.

Memory contention is only one of many potential reasons for slow performance. One of the usual causes for slow performance is poor server maintenance - i.e., fragmented indexes, out of date statistics etc. There are lot of resources on the web to guide you through. Brent Ozar's sp_blitz is a useful tool that can help you quickly assess the state of your server (not just performance issues)

Also, Is that all you have on that server namely 2014 SQL? Is it a physical server or a virtual server?

1 Like

Good points, @yosiasz. I have had one instance where a job on a SQL Server on a VM cluster had performance issues that did not correlate to anything that I could think of. Finally it turned out that there was some other machine running huge jobs which resulted in my server being paged out of the VM memory. Our sysadmins pinned my server to the VM cluster memory, and the problem magically disappeared.