Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Out of memory exception Expand / Collapse
Author
Message
Posted Wednesday, November 07, 2012 12:31 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 22, 2013 10:13 AM
Points: 79, Visits: 200
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.
Post #1382131
Posted Wednesday, November 07, 2012 1:25 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #1382155
Posted Thursday, November 08, 2012 9:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 22, 2013 10:13 AM
Points: 79, Visits: 200
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.
Post #1382599
Posted Thursday, November 08, 2012 11:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
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 2008, MVP
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

Post #1382640
Posted Friday, November 09, 2012 9:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 22, 2013 10:13 AM
Points: 79, Visits: 200
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.
Post #1383088
Posted Friday, November 09, 2012 11:45 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 3,731, Visits: 7,069
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"
Post #1383177
Posted Friday, November 09, 2012 12:06 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 22, 2013 10:13 AM
Points: 79, Visits: 200
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.
Post #1383191
Posted Friday, November 09, 2012 4:43 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
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 2008, MVP
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

Post #1383299
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse