June 9, 2016 at 2:40 am
I hope everyone is doing great
We are having one sql server database, 2008 R2, 64 bit and its consuming all the memory which is assigned to the server. We modified the max memory setting for the database to 8GB from 12GB but in the task manager it still shows that its using 91% of the memory when we expected 4GB memory to be free.
What else can I modify or change so sql server releases any unused memory?
have a wonderful day everyone
KY
June 9, 2016 at 2:50 am
The SQL Server memory settings "Max Server Memory" / "Min Server memory" are not the total memory used by SQL server, but are the amount of memory allocated to the buffer pool. There are other things that take up memory - query cache, connection pools, the basic SQL Server process itself...
For some more information, see https://technet.microsoft.com/en-us/library/ms180797(v=sql.105).aspx, paying particular attention to the highlighted note:
SQL Server as a process acquires more memory than specified by max server memory option. Both internal and external components can allocate memory outside of the buffer pool, which consumes additional memory
Thomas Rushton
blog: https://thelonedba.wordpress.com
June 9, 2016 at 2:50 am
What does the following return?
SELECT
(physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory;
June 9, 2016 at 5:08 am
Memory_usedby_Sqlserver_MBLocked_pages_used_Sqlserver_MBTotal_VAS_in_MBprocess_physical_memory_lowprocess_virtual_memory_low
6313 0 8388607 0 0
June 9, 2016 at 7:27 am
Firstly Task Manager is a really bad tool for checking server memory.
Second, you say 'its using 91% of the memory'. What is 'it'? Do you see SQLServr.exe with 91% next to it somewhere?
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
June 9, 2016 at 7:42 am
Don't you have to restart the SQL Service for the new max memory setting to take affect?
June 9, 2016 at 8:03 am
ZZartin (6/9/2016)
Don't you have to restart the SQL Service for the new max memory setting to take affect?
You don't.
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
June 9, 2016 at 8:27 am
Using resource monitor we found that from the total memory (12gb) that 91% was being used and it was locked by SQL server even though we set max memory to 8GB.
So shouldn't we be seeing unused memory between 2-4 GB?
KY
June 9, 2016 at 8:45 am
No. There's the OS as well which uses memory. Plus whatever else is running on the server. If you've installed services you don't need like SSAS, those will be running and using memory as well.
If you're set SQL to 8GB, it will (mostly) use 8GB. There may be a little extra for thread stacks and other non-buffer memory, but that's small
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply