I was working on a root cause analysis for an OOM (Out-of-Memory) issue for SQL Server 2008 R2 and I needed to schedule notifications when the available memory on the server fell below a certain value. SQL Server 2008 and above has a nifty little DMV to do just this. Read about it’s usefulness here:
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:
I published a new post on SQL Server FAQ on how incorrect buffercount values used or the lack of using the buffercount value can lead to Out of Memory errors in SQL Server.
Read about it here: