17

With SQL Server 2005, you could look at the Task Manager and, at least, get a cursory look at how much memory is allocated to SQL Server.

With SQL Server 2008, the Working Set or Commit Size never really goes above 500 MB, even though the SQLServer:Memory Manager/Total Server Memory (KB) perf counter states 16,732,760.

Is there a setting where it will actually show the server memory in the Task Manager? Or is it a result of them changing how memory is used in SQL Server

AngryHacker
  • 1,931
  • 5
  • 20
  • 33

2 Answers2

31

You could NEVER, EVER trust Task Manager to tell you how much memory SQL Server is using (maybe you are remembering a 32-bit system with a very small amount of memory). Stop using Task Manager for this, period. Use the performance counter - you can also query the performance counter using DMVs:

SELECT object_name, cntr_value 
  FROM sys.dm_os_performance_counters
  WHERE counter_name = 'Total Server Memory (KB)';

You could save that as a query shortcut in Tools > Options > Environment > Keyboard > Query Shortcuts, and get accurate results in a query window much faster than getting inaccurate results from Task Manager.

You can also check for memory pressure (and whether you can do anything about it) using these queries:

SELECT object_name, counter_name, cntr_value
  FROM sys.dm_os_performance_counters
  WHERE counter_name IN 
    ('Total Server Memory (KB)', 'Target Server Memory (KB)');

-- SQL Server 2012:
SELECT physical_memory_kb FROM sys.dm_os_sys_info;


-- Prior versions:
SELECT physical_memory_in_bytes FROM sys.dm_os_sys_info;


EXEC sp_configure 'max server memory';
Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
  • EXEC sp_configure 'max server memory'; also does not exist in SQL 2008. – AngryHacker Jul 16 '12 at 22:09
  • @AngryHacker Sure it does (it's called max server memory (MB) but you don't need to type the whole thing). In order to see it, you need to read into the error message that tells you about it being an advanced option, and then sp_configure 'show adv', 1; reconfigure with override;. Note that you don't have to type all of 'show advanced options' here, either. – Aaron Bertrand Jul 16 '12 at 22:14
  • I think you want SELECT counter_name, cntr_value ... instead of object_name. At least for me on SQL2019 object_name returns "MSSQL$PROD:Memory Manager" for both those counters. – Rory Oct 21 '21 at 11:17
8

@AaronBertrand's answer is great. Here's a variation with a bit more info from here

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;

Also a good blog post on why TaskManager isn't up to the task here.

And for more performance and DMV magic you can't go past Glenn Berry's SQL Performance blog

Rory
  • 1,300
  • 9
  • 11