Out of memory exception

  • I have a stored procedure that is normally encrypted. I run this with a specific set of parameters(that I am trying to analyze for slow performance), and it runs fine. I un-encrypt the stored procedure and re-run with the exact same set of parameters, this time with "Show actual execution plan" from SQL Server Management Studio. This results in the below error:

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

    I thought that either the box or SQL Server was genuinely running out of memory, so I shut down some other instances & freed up another 4 GB of memory, allocated 2 GB more to this particular SQL instance, and re-ran the same proc. No dice.

    Has anyone else seen this behavior ? The pattern is consistently repeatable.

  • Is the query just insanely complex? Like the plan would be some simply titanic amount of RAM?

    I don't think it's even possible to have a plan that large, but who knows?

    I'd check the amount of memory being used by SQL Server vs the amount allocated to the OS and other processes. It sounds like you may have already done that, but allocating more memory isn't the same as checking those two things, so I wasn't quite sure if you had.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thanks for the reply. Yes, it is a very complex query, and yes I did check for memory utilization. SQL is using dynamic and it acquires the max allowed memory when this query is run. Was just wondering if there is a bug associated with this somewhere. had to put this on the back burner, but will pick it up again soon.

  • That's a .Net error message, it's your client app that's run out of memory, not the server, not SQL.

    Most likely the plan is complex and the client is low on memory and trying to display the plan is making SSMS run out of memory. Try catching the plan via Profiler/server-side trace.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Excellent suggestion Gail ! I was trying exactly that when I saw your post. I'll post back on how that goes.

    By the way, I saw some blogs about this issue here : http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/70acf679-0745-4a10-8f38-2bcab3de9fd0/[/url]

    I tried those options but that didnt do anything.

  • I've seen this issue when attempting to obtain and execution plan from a procedure that had a cursor/loop in it and it have to process a set of statements several thousand times...just posting this to note that the procedure doesn't necessary have to be complex...

    +1 Gail for that server side trace tip...never would've though of that

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • I still think there is a bug in SSMS. Why would SSMS crap out everytime ? I had even tried moving the DB onto a server with 32 GB memory, out of which only 16 GB was allocated to SQL Server. I ran the query from SSMS on the server itself.

    The profiler, on the other hand, captured what I needed. The profiler didnt even break a sweat(in terms of memory usage). Sure smells like an SSMS bug to me.

  • It's not a bug. SSMS is a 32-bit application and as such can use no more than 2GB of memory, no matter what is available. Profiler does not display all the execution plans as SSMS does, hence it uses less memory at any point.

    As an aside, you should not be running SSMS on the server as it will be taking memory and resources away from SQL Server. Run SSMS on a remote workstation and connect to the server.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hello Everyone,

    I'm trying to do Schema compare of two Databases from VisualStudio - SqlschemaComapre, but i got a memory exception error as below.

    Exception of Type 'System.OutOfMemoryException' was thrown.

    Server is 64-bit version

    SQL 2014

    RAM 1 TB

    760 GB RAM allocated to SQL

    Also i used DBDiff tool, got the same error.

    Can anyone help on this case.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply