Just want to make sure I am understanding this correct. Say I have set my maximum server memory according to best practice. Now I see in the the PerfMon counter: SQLServer: Memory Manager - Free memory Many Gigabytes (of free memeory). Does this mean that I can increase my "max server memory (MB)" until e.g. only a few MB is left (i.e. the Free memory counter only shows a few MB)?

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.

I'm currently working on implementing an upgrade to SQL Server 2019 in order to make use of the graph features that are available in it. Our database stores records of files and their children, and the graph features allow us to quickly find all of a file's relations in either direction. Our current dev environment is using SQL Server 2019 Standard (15.0.4023.6) on a Linux server. I'm noticing a concerning problem when I run my graph queries. The server's 'internal' resource pool appears to not free up all resources after a graph query. Left unchecked, this fills up the resource pool. Larger queries will fail until the SQL Server process to be restarted. Depending on server load, this could happen in as little as 1-2 hours. This can also fill up the tempdb and threaten to fill the storage drive. The files for the tempdb also cannot be shrunk/truncated significantly until the server is restarted. In configuration, 'memory.memorylimitmb' is not set, so this problem happens when the resource pool starts to have used the better part of the default 80% of system memory (12.8 GB, with 16GB of system memory) To set up the tables within a demo database:

CREATE TABLE FileNode (ID BIGINT NOT NULL CONSTRAINT PK_FileNode PRIMARY KEY) AS NODE

GO

CREATE TABLE FileNodeArchiveEdge AS EDGE

GO

CREATE INDEX [IX_FileNodeArchiveEdge_ChildFile] ON [dbo].[FileNodeArchiveEdge] ($from_id)

GO

CREATE INDEX [IX_FileNodeArchiveEdge_ParentFile] ON [dbo].[FileNodeArchiveEdge] ($to_id)

GO

To populate the demo database tables:

INSERT INTO [FileNode] (ID) VALUES
            (1),(2),(3),(4),(5),
            (6),(7),(8),(9),(10),
            (11),(12),(13),(14),(15)

-- Convenient intermediate table
DECLARE @bridge TABLE (f BIGINT, t BIGINT)
INSERT INTO @bridge (f, t) VALUES
	(1,4),
	(4,9),
	(4,10),
	(1,5),
	(5,11),
	(11,12),
	(2,5),
	(2,6),
	(6,13),
	(6,14),
	(13,15),
	(14,15),
	(15,12),
	(7,14),
	(3,7),
	(3,8)

INSERT INTO FileNodeArchiveEdge
($from_id, $to_id)
SELECT 
	(SELECT $node_id FROM FileNode WHERE ID = f),
	(SELECT $node_id FROM FileNode WHERE ID = t)
FROM @bridge

To get all child IDs of a file (repeating this same query will eat up memory resources and cause 'USERSTORE_SCHEMAMGR' to grow uncontrollably):

DECLARE @parentId BIGINT = 1
SELECT 
	LAST_VALUE(f2.ID) WITHIN GROUP (GRAPH PATH)
FROM
	FileNode f1,
	FileNodeArchiveEdge FOR PATH contains_file,
	FileNode FOR PATH f2
WHERE
	f1.ID = @parentId
	AND MATCH(SHORTEST_PATH(f1(-(contains_file)->f2)+))

Re-running the provided query to retrieve all child nodes of a particular file will eventually see the 'USERSTORE_SCHEMAMGR' memory clerk type eat up the bulk of used resources. The tempdb will also grow uncontrollably. The demonstration database queries are too small to trigger an explicit message reporting that the 'internal' resource pool. However, running a larger query on the same server should trigger the warning, and performance should still be impacted. The following queries are useful for monitoring server performance:

-- Memory clerk usage
SELECT TOP(10) mc.[type] AS [Memory Clerk Type],
   CAST((SUM(mc.pages_kb)/1024.0) AS DECIMAL (15,2)) AS [Memory Usage (MB)]
FROM sys.dm_os_memory_clerks AS mc WITH (NOLOCK)
GROUP BY mc.[type]
ORDER BY SUM(mc.pages_kb) DESC OPTION (RECOMPILE);

/*
Example output of above query:

Memory Clerk Type                                            Memory Usage (MB)
------------------------------------------------------------ -----------------
USERSTORE_SCHEMAMGR                                                    9224.26
MEMORYCLERK_SQLSTORENG                                                 1114.73
MEMORYCLERK_SQLBUFFERPOOL                                               471.50
CACHESTORE_SEHOBTCOLUMNATTRIBUTE                                        376.47
MEMORYCLERK_SOSNODE                                                     292.02
MEMORYCLERK_SQLGENERAL                                                   19.84
MEMORYCLERK_SQLCLR                                                       12.04
MEMORYCLERK_SQLQUERYPLAN                                                  2.99
MEMORYCLERK_SQLLOGPOOL                                                    2.61
MEMORYCLERK_SQLTRACE                                                      2.14

*/

SELECT cache_memory_kb/1024.0 AS [cache_memory_MB],compile_memory_kb/1024 AS compile_memory_MB, used_memory_kb/1024.0 AS [used_memory_MB] FROM sys.dm_resource_governor_resource_pools

To rapidly query the server and eat up resources, I'm using the following BASH loop:

l=1000 # Number of loops
# The loop will probably need to be run 2M times or so to start to see significant usage.

c=0 # Loop tracker

touch marker # Alternate emergency stop: Remove the marker file from another terminal session.
time while [ $c -lt $l ] && [ -f "marker" ]; do
	c="$((${c}+1))"
	echo ${c}/${l}
	# Notes: SQLCMDPASSWORD has been set in environment variable
    #        child-query.sql contains the above child query to loop for the children of file ID 1.
	time sqlcmd -U db_user -S localhost -d DemoDatabase -i child-query.sql > /dev/null || break
done
rm marker

The DROPCLEANBUFFERS/FREEPROCCACHE/FLUSHPROCINDB DBCC commands complete successfully, but don't appear to have an effect. Is there a configuration or procedure that could solve this problem, or is this a fundamental server problem?

I have a production system running SQL Server 2019 Standard edition. It recently had a problem 3 times in 1 day where it became unresponsive until a reboot. Errors seem to point to memory limitations. 32GB is installed and the machine is dedicated to MSSQL. Max Memory set to 26GB. (edited 2020-11-23 to remove some erroneous info re: MEMORYCLERK_SQLGENERAL.) The most damnning info from the logs is a series of FAIL_PAGE_ALLOCATION errors. Note: Since having the problem I've bumped up to a larger instance size (64GB) **Update 2020-11-22**: The problem has reoccurred on the new larger instance. The memory errors began a few minutes after a scheduled backup of several databases. That may or may not be coincidence. It became nonresponsive and I forcibly restarted it. - I will start the MSFT support process as suggested by Josh and sqL_handLe. - I've made an Agent job (sproc here ) to collect the results from dm_os_memory_objects every 5 minutes, based on sqL_handLe's query. - I've shared recent logs here . (I replaced the nl tags with actual newlines to make it easier to read.) - Disabled SSAS as suggested by Jana Sattainathan. I did confirm it was running and had 13GB "Min Working Set" as shown in the linked article, even though its actual working set was small. I think it's possible this subtle issue is the culprit, but if it's as dangerous as it seems, I'm very surprised I haven't run into it before. Jana, consider converting your comment to an answer? The non-SQL load on this server is near-zero. The backup agent is just Ola Hallengren's TSQL scripts. The default SQL services are running but not in use. As mentioned, I've stopped SSAS. We occasionally use SSIS but not since the instance upgrade.

Below what my server's performance counters show: Questions: 1) What "Stolen Server Memory" consists of ? I can assume "Plan Cache", "Lock Memory", "SQL Cache", "Connection", "Optimizer" are included into "Stolen Server Memory", but what else is included ? 2) How can I query rest of things that are included into "Stolen Server Memory" ?

Is there any system catalog views or DMVs that can be used to query (in SSMS for example) below performance counters from SQL Server:Memory Manager ? Free Memory (KB) Target Server Memory Total Server Memory Maximum Workspace Memory (KB) Database Cache Memory (KB) Granted Workspace Memory (KB) Lock Memory (KB) Log Pool Memory (KB) Optimizer Memory (KB) Connection Memory (KB) SQL Cache Memory (KB) Reserved Server Memory (KB) Stolen Server Memory (KB)