40

I'm using Microsoft SQL Server 2012 and trying to run a simple query against it within Management Studio. I'm getting the following error (in SSMS, running on the server):

An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

The system has 24GB of RAM installed but looking in task manager the sqlservr.exe process is only using 2.9GB.

Is there a setting somewhere that is restricting its RAM usage?

Paul White
  • 94,921
  • 30
  • 437
  • 687

6 Answers6

41

This error indicates that Management Studio is running out of memory, not the SQL Server service. Even if you installed 64 bit SQL Server, the SQL Server Management Studio executable is a 32 bit application.

This is likely caused by the size of the result set that you are returning to Management Studio. Are you executing something like SELECT * FROM really_big_table? See http://support.microsoft.com/kb/2874903 for more.

Mike Stankavich
  • 526
  • 5
  • 5
19

Mike is right that the error message you're receiving is from the Management Studio application itself, and not from SQL Server. It is the memory on your local workstation that has been exhausted, likely due to trying to pull 16 billion rows into the client application (rendering that much data in a grid is quite expensive in terms of memory, so try to limit your queries using TOP etc. - I don't know what practical thing(s) you could possibly do with enough data to use up all of your local memory anyway).

But I do want to address another problem: using Task Manager to assess the amount of memory SQL Server is using. Don't do this; it's a bold-faced liar. Copying from this answer (your question is two-fold, so I can't really close it as a duplicate):

You could NEVER, EVER trust Task Manager to tell you how much memory SQL Server is using. 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, 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';
Community
  • 1
Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
15

It was the same case with me. My SQL Server Management Studio was open for couple of days. I restarted it and it got solved.

Baz Guvenkaya
  • 291
  • 3
  • 5
9

I found turning off IntelliSense helped. I also recommend checking any add-ins you have (things like RedGate tools and ApexSQL also exacerbated the issue for me).

This issue plagued me for days now and to be honest it's quite weak of Microsoft. They should really have 64 bit tool sets, since we are dealing with big data these days, 64 bit servers, and desktop environments.

Paul White
  • 94,921
  • 30
  • 437
  • 687
Trevor
  • 191
  • 1
  • 1
1

For SQL 2008 R2 the memory query commands (from Aaron Bertrand's post) are as follows

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

SELECT object_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_in_bytes FROM sys.dm_os_sys_info;

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

Also note that the command

EXEC sp_configure 'max server memory';

may not work unless you have advanced options enabled. e.g. do this first

EXEC sp_configure 'show advanced options',1
RECONFIGURE
David Bridge
  • 121
  • 4
1

Please note that there is a Microsoft connect issue for this as being an SSMS memory leak. They think they resolved it in v16.5

See the issue on Connect.microsoft.com by clicking here

If you install the latest and still have the problem then please vote it up to get re-opened.

SSMS v16.5 here

Connect issue indicating memory leak fixed in 16.5 here

David Bridge
  • 121
  • 4