November 7, 2012 at 12:31 pm
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.
November 7, 2012 at 1:25 pm
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
November 8, 2012 at 9:57 am
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.
November 8, 2012 at 11:03 am
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
November 9, 2012 at 9:23 am
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.
November 9, 2012 at 11:45 am
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
November 9, 2012 at 12:06 pm
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.
November 9, 2012 at 4:43 pm
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
October 23, 2016 at 7:15 am
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