September 2, 2015 at 4:03 pm
Hi All,
SQL server service is using excessive memory.
128 GB is the total server memory, 100 GB allocated to SQL. But the available memory on the server is getting low day by day. Now its less than 4 GB left on the server. Why is this happening?
No other applications are installed.
Many thanks!
September 2, 2015 at 9:25 pm
Have u checked perfrmon or system monitor to find how much memory is used by all applications..You may use "Rammap" utility https://technet.microsoft.com/en-us/library/ff700229.aspx to find which processes are using the memory with greater accuracy.
Just curious, are you using filestreams or large SSIS packages on SQL Server. would help if you could provide additional info on the SQL Server Instance - whether it uses any special features fielstream, rbs , linked servers etc..
Regards,
Raj
September 3, 2015 at 5:04 am
SQL Server will take every single gb of memory that you assign it. It won't release that memory unless forced. It's very normal for servers to run with high memory use. You have to worry about other metrics around memory to understand if you're hitting memory issues, not just the amount in use.
"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 3, 2015 at 5:20 am
Do you have any other applications or SQL Server instances running on the same server? What is the value of max server memory?
John
September 3, 2015 at 5:38 am
Thanks John. The max memory value is 100GB.
September 3, 2015 at 5:40 am
Thanks Raj. File stream is not enabled. Also there are no ssis packages or other applications installed.
September 3, 2015 at 5:42 am
Thanks Grant. But the memory usage has exceeded 24 GB over the max memory set for sql which is 100GB.
September 3, 2015 at 5:46 am
Have you checked which processes are using that memory?
John
September 3, 2015 at 6:49 am
'sqlservr.exe' is using 24 GB of memory which is the top on the list.
Also I noticed that the available memory has dropped from 4 GB to 1 GB when 'RamMap' app was started and then increased to 12 GB after 'RamMap' application was closed.
I see the following on event log while RAMMAP was running,
AppDomain 3 (DBXX.dbo[runtime].2) is marked for unload due to memory pressure.
September 3, 2015 at 6:59 am
Can you check using this script
select * FROM sys.dm_os_performance_counters
where counter_name = 'Total Server Memory (KB)'
Checking via task manager is not accurate.
As said earlier, u need to find how much all processes use. use "Rammap" utility for accurate check.
Atleast use tasklist on dos prompt and see if all the memory usage adds up to the total usage. Just checking highest process doesn't help.
Regards,
Raj
September 3, 2015 at 7:00 am
SQL!$@w$0ME (9/3/2015)
Thanks Raj. File stream is not enabled. Also there are no ssis packages or other applications installed.
You're saying that the SQL Server executable is using more memory than you have the max limit set to? Then you have discovered a major bug. It doesn't work like that.
"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 3, 2015 at 7:02 am
First try to find the breakup for all the usage. even large number of smaller processes can cause a memory leak. You just need to note the breakup of usage via rammap
Regards,
Raj
September 3, 2015 at 7:06 am
SQL!$@w$0ME (9/3/2015)
Thanks Grant. But the memory usage has exceeded 24 GB over the max memory set for sql which is 100GB.
Please post the result of the following script.
select * FROM sys.dm_os_performance_counters
where counter_name in ( 'Total Server Memory (KB)' , 'Target Server Memory (KB)') ;
Regards,
Raj
September 3, 2015 at 7:07 am
Hi Raj, total server memory is 100GB. RAMMAP shows 'sqlserver.exe' as the top process (24 GB).
September 3, 2015 at 7:12 am
SQL!$@w$0ME (9/3/2015)
Hi Raj, total server memory is 100GB. RAMMAP shows 'sqlserver.exe' as the top process (24 GB).
Step 1 : Run the script I posted above.
Step 2: Try to find the list of all processes and their usage via Rammap / task list.
Just the highest process simply doesn't help anyone!!!
Regards,
Raj
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply