System.OutOfMemoryException for Management Studio


I have seen multiple posts on the web trying to decipher the OutOfMemoryException thrown by Management Studio (SSMS) while executing a user query. Contrary to popular beliefs, this is not a SQL Database Engine Out of Memory condition. This is a client side OOM condition thrown by the .NET runtime while trying to receive the output of the query from the Database Engine. More often than not, the culprit is a large number of rows returned by the query submitted by the user.

A lot of times the error can be circumvented by using the TEXT mode output of SSMS. The error is a .NET Out of Memory exception pointing to the Management Studio running out of available physical memory. The GRID output requires a .NET GridView control to be created where as the Text mode output uses a TextBox to store the output returned by the database engine. The amount of memory consumed by the GRID is higher than a TextBox.

It is always advisable to store the output of a query returns a large number of rows into a file (CTRL+SHIFT+F) or use SQLCMD to generate the output into a CSV/TXT file. This would help optimize the memory usage on the box that is executing the query and also prevent re-execution of the query due to client box out-of-memory conditions. IMHO I cannot fathom the need to output a million rows in the GRID view because it is not possible to parse the output unless you put that into a flat file! Hope this sheds some light on this common misconception.

Once such issue is mentioned below:

Reference: OOM error when we access Schema changes report from SSMS – Microsoft

Advertisements

How to open Management Studio with the Object Explorer and a Query Window from command line


SQL Server Management Studio provides you the option of launching from command line. If you want to launch SSMS with a New Query Window and Object Explorer using command line, then you need to do the following changes.

Tools –> Options will open up the Options page for SSMS.

Under that you would see an At Startup property under Environment –> General. Use the drop down list to select the “Open a Object Explorer and New Query”. The default is shown below in the screen shot.

image

Once you have made the change, you can use the following command to launch SSMS connected to the instance you want:

ssms -S <server name> -E –nosplash

SSMS Command Line Options

—————————
Microsoft SQL Server Management Studio
—————————
Usage:
ssms.exe [-S server_name[\instance_name]] [-d database] [-U user] [-P password] [-E] [file_name[, file_name]] [/?]

    [-S    The name of the SQL Server instance to which to connect]
    [-d    The name of the SQL Server database to which to connect]
    [-E]    Use Windows Authentication to login to SQL Server
    [-U    The name of the SQL Server login with which to connect]
    [-P    The password associated with the login]
    [file_name[, file_name]] names of files to load
    [-nosplash] Supress splash screen
    [/?]    Displays this usage information