SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Out of memory exception


Out of memory exception

Author
Message
sqlGDBA
sqlGDBA
Mr or Mrs. 500
Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)

Group: General Forum Members
Points: 535 Visits: 315
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.
GSquared
GSquared
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60163 Visits: 9730
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
sqlGDBA
sqlGDBA
Mr or Mrs. 500
Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)

Group: General Forum Members
Points: 535 Visits: 315
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)

Group: General Forum Members
Points: 234049 Visits: 46366
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


sqlGDBA
sqlGDBA
Mr or Mrs. 500
Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)

Group: General Forum Members
Points: 535 Visits: 315
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/

I tried those options but that didnt do anything.
MyDoggieJessie
MyDoggieJessie
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12752 Visits: 7444
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; They'll drag you down to their level and beat you with experience" ;-)
sqlGDBA
sqlGDBA
Mr or Mrs. 500
Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)

Group: General Forum Members
Points: 535 Visits: 315
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)

Group: General Forum Members
Points: 234049 Visits: 46366
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


satyaplay13
satyaplay13
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 2
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search