SQL Server 2014 consuming more memory than max server memory setting
0
votes
0
answers
514
views
For the past month, I have noticed our SQL Production server has been consuming more memory than we allocated for it. This is the current structure:
Total OS Ram: 31Gb
SQL Server currently using 15095Mb
Max memory setting: 11864Mb
On this server we have: Prod Sql Server, UAT Sql Server, SSAS, antivirus software, and other smaller applications. Before last month, memory utilization was 80% and now it is 86%, and every week the SQL server memory usage keeps on increasing.
Using a combination of task manager, DMV's, Perf Mon, and Resource Monitor, the Prod Sql Server is taking up the most memory.
From an article, I read the reason why SQL is consuming more memory than allocated is because of other processes such as CLR, DB Mail, SSAS, SSIS, etc. In the error log we see on random days:
AppDomain 22 (SSISDB.dbo[runtime].4) is marked for unload due to memory pressure.
In addition, every night we get RESOURCE_SEMAPHORE wait types for about an hour or so. I have a job in place that tracks these waits and during that time there are tons of bigger jobs being ran such as
-2 SSIS jobs doing heavy inserts on the biggest DB we have (1.3 TB)
-4 Insert jobs that use temptables and need to query through linked servers
-smaller jobs like log backups
There are no user complaints, or any random termination of the server, but I want to be proactive from a disaster happening. I am unsure where to go from here. If I need to track down the queries that are causing high memory usage, what specifically should I look for? Once I track the queries that cause high memory usage, what are steps I should take to fix it? (I am an accidental DBA so I am not the best at query tuning / optimization). Should I increase the max memory setting, if so then to what value? Will this change take effect without restart of the SQL Service? I have provided a screenshot of Memory Consumption report from SSMS. Please let me know if I need to provide any additional info. Thanks in advance.

Asked by sqllover2020
(73 rep)
Feb 9, 2022, 06:01 PM