1

I'm preparing to bump up our max server memory value after a RAM upgrade on our server. I'm testing the commands out on a test SQL Server and I'm not sure why I'm seeing different results with T-SQL versus the GUI. I changed the memory on the test server to 24GB as follows:

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'max server memory', 24576;  
GO  
RECONFIGURE;  
GO

In the GUI I see 24576.

But using the query below, I see 22421. What did I do wrong?

SELECT (physical_memory_in_use_kb/1024) AS Used_Memory_By_SqlServer_MB
FROM sys.dm_os_process_memory
Humberto Castellon
  • 849
  • 1
  • 7
  • 17
cspell
  • 195
  • 2
  • 6

1 Answers1

2

Your query is returning memory used by SQL, not the max server memory it's allowed to consume.

How do you get the minimum and maximum memory allocation of a SQL instance using T-SQL?

This will show you how to get that value via query. 

SELECT name, value, value_in_use, [description] 
FROM sys.configurations
WHERE name like '%server memory%'
ORDER BY name OPTION (RECOMPILE);
SteamerJ
  • 403
  • 2
  • 7