Out of Memory Exception

This seems to a common thread with Red-Gate tools. There are lots of reports of this error when using pretty much any of the Red-Gate tools.

When is this going to be addressed? It is fairly predictable when it's going to happen using SQL Prompt. SSMS grinds to a halt whilst typing and then a massive number of queries can't run.

This impacts on productivity as I now can't use Debug flags in code to get interim results throughout my code as this causes the problem when more than around 5 sets are returned.

Having used a number of Red-Gate tools, they all seem flaky in anything more than a basic environment. My employer use a number of the tools and regularly (I mean on a monthly basis) have to get a "fix" from Red-Gate for something else that doesn't work.

The Out Of Memory issue has been raised many times here, and yet it persists. Does the software get tested before being released, or are you selling it on the hidden understanding that paying customers will test it for you?

The potential is good, unfortunately the implementation is far from it.

Tagged:

Answers

  • RichardLRichardL Posts: 417 Gold 4
    Hi @Not_Happy

    Thanks for your post. 

    Is this post supposed to be in the SQL Prompt forum. Do let know so that I can move it there if you like. 

    Regarding your query there is information in my link below on this. The documentation link refers to Source Control but actually it is still relevant to any of our tools that plug in to SSMS, like Prompt for example. 

    https://documentation.red-gate.com/soc6/troubleshooting/error-messages/system-outofmemoryexception

    I hope this is helpful to you. 

    Kind regards

    Richard Lynch. 
    Customer Support
    Redgate Software
  • Not_HappyNot_Happy Posts: 12 Bronze 1

    Thank you for replying. I've read that post and it seems to say "if our tool doesn't work, change other things so it can".

    Sorry to be blunt, but that's a nonsense answer. If a tool doesn't have the facilities to run, it should exit gracefully, not bring the entire system down or prevent perfectly valid  code from executing.

    If my electricity supply goes down whilst I'm using the bandsaw, it doesn't make the entire house collapse.

    It's your code, if you don't have the resource available, stop your code. Your tools are add-ins supposedly to make my development quicker and easier. It's farcical to suggest that users change their practices because your tool wants to work within specific constraints. Whether those constraints are imposed on you by the OS or SSMS is irrelevant. We all tend to use these tools in professional environments and so more disks, smaller tables, shorter procs are often out of our control.

    There is never a question where the answer is change the question.

  • Not_HappyNot_Happy Posts: 12 Bronze 1

    No response? It doesn't fill me with confidence.

    I'm forced to use some of your products because that's my client's choice of tool, however, SQL Prompt is something designed to make my work easier, yet having it crash, repeatedly, more than once a day, does the exact opposite. The benefits of being able to type AP and get the proc are far outweighed by the crashes and incomplete results. SSMS doesn't crash when I try to execute a big script, so why does your tool?

    I've reached the point where I am uninstalling it since it's making my job harder not easier.

  • Alex BAlex B Posts: 1,131 Diamond 4
    Hi @Not_Happy,

    As @RichardL indicated - our tools work within SQL Server Management Studio and share it's memory space along with all of the other addins; you cannot run SQL Prompt on it's own.  In your last example t's not just the running of the script that is being done by our tool, there is caching of objects for connected servers and their databases so that suggestions can be provided, code analysis done and the various other tasks that SQL Prompt does.  If you were using any other of our tools those will add their bit to usage of the memory pool as well.

    In your analogy the bandsaw & electricity have no impact on the house staying standing; a more appropriate analogy would be SSMS is the person using the bandsaw that represents SQL Prompt and the persons' fatigue is the memory allocation.  By using the tool the person is expending some energy along with all the other normal demands on the person from just existing.  If his fatigue grows too much too quickly he can't continue and so the person and tool both stop doing work.  Once he has rested (restart SSMS) he is able to continue working with the tool.  I think a more appropriate tool metaphor for SQL Prompt would be a hammer drill, handheld sander, or other power tool that, while it makes the job a lot easier, still takes it's toll on the person using it.

    This page goes over connections an memory usage that may help, in addition to the link from Richard above on making SSMS Large Address Aware.

    I hope that helps clarify things.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • Not_HappyNot_Happy Posts: 12 Bronze 1
    Unfortunately, Alex, it does not.

    Splitting hairs over the analogy isn't helpful.

    The bottom line is you have a product that you sell, for a not insignificant amount of money, that isn't capable of fulfilling the task it purports to do.

    Relying on the limitations imposed by SSMS as a justification for the failures of your product is a lazy excuse for poor coding. With better coding you could make smarter use of available memory and manage your product's use of that memory better.

    Would you eat a stale sandwich if the retailer said it's all we've got? Of course not, but you still expect your customers to pay for software that you know doesn't do the job properly.
  • Alex BAlex B Posts: 1,131 Diamond 4
    Hi @Not_Happy,

    The product does in fact fulfill it's purpose for a large number of our customers every day, otherwise we wouldn't have a product to sell, but I understand that in your case it's not meeting your needs which is why we're trying to provide options to get things working for you.

    We're not relying on those limitations, but functioning within the restrictions; when customers hit issues like these, we provide ways to try and get around the limitations and find out what in our product may be causing the issues.

    How many servers are you connected to and how many databases and objects are contained therein?  How many queries do you have open and how large are they?  If you disable code analysis, make SSMS Large Address Aware or utilize some of the option in the last link I sent on managing memory do any of those help?

    If there is an issue with our code we'd like to narrow down where the issue is occurring that is causing the issue in your case.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • duliduli Posts: 2 New member
    I'd like to piggy back on this thread. My SSMS crashes after a while because it ends up using about 3GB of ram and becomes unstable. The culprit is SQL Source control. To prove this I opened 2 sessions of SSMS (sql source control does not start automatically). One of them I opened source control, the second I did not. The second I have about 18 query tabs opened with results and consumes roughly 500MB. The second has always had only 2 tabs opened. One is sql source control and the other is sp_whoisactive (very similar to sp_who2 active). The one with source control is now at 1500MB and growing (it started at around 200MB I think). So something in your application is causing a memory leak or caching a ton of results. 

    Redgate, in your post above you ask how many servers you connect to. Why does it matter if only 1DB in 1 server is connected to source control? are you reading the contents of every single DB that is in a server regardless of what's in source control?? Just an FYI, I only connect to 1 server but it has 5 databases - only 1 of which is connected to SQL source control. Very annoying. I have to go with this 2 SSMS approach now, and work only on 1, and use the other for source control since the one with source control is guaranteed to crash eventually.
  • Alex BAlex B Posts: 1,131 Diamond 4
    Hi @duli,

    The reason I was asking for the number of servers they connect to is because the issue mentioned above named SQL Prompt specifically.

    As I say above, all of our tools work within SQL Server Management Studio and share it's memory space along with all of the other addins that might be present.

    In all cases, making SSMS Large Address Aware (LAA) will help to some extent and in your specific case with SQL Source Control this page has some further steps that may help (the LAA is the first entry on that page).  In the case of SQL Source Control, it is the number of objects in the database you are attached to that is most relevant for the memory issue.

    I hope one of the options in the linked article help!

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • Not_HappyNot_Happy Posts: 12 Bronze 1
    It is answer like this that left me in despair when I originally raised it. Server configurations may not be in the gift of a lot of developers working in corporate environments. But that aside, looking for something to say "ah ha, it's x that you're doing" doesn't help either. The admission that Source Control (another product I am forced to use) will struggle with a large number of objects in a database, is a staggering admission. You're website makes lots of claims about "large" databases but it seems that what you consider large is not all that large in the real world.

    This all still comes down to poor memory management and error trapping. Whatever causes your app to run out of available memory should be a scenario you're handling. Judging by the admission above, and my experience with SQL Prompt, it's not.

    For the above, surely the simplest thing is to check for a change on an object, and either write that change or move on. In both cases, you can make the memory available again so you're only using a small amount. This is a simple scenario, I know, but the principle holds true.
  • TraeTrae Posts: 2 New member
    Wow, I have been on both sides of this issue in the past, with SQL Prompt back on the end user side, but I have never just used the attitude with end users that you guys have here. While I mostly agree with Not_Happy I do feel for the RedGate folks in a way. 

    Lets be honest SSMS is kind of like Microsoft's old beat up farm truck it is utilitarian and you have to have it to do the job but you really don't want to put more effort into it than you absolutely have to. Because of this it is probably hard as heck to keep things working inside of it.

    However, it is far too easy to get SSMS to run out of memory and crash when RedGate tools are installed. There has to be a way for RedGate to manage the memory better. Come on, did Adobe accept memory limits back when Windows didn't do virtual memory or allowed the end user to disable it entirely? No they found a different way to do it. Yes there are technical limitations, but, I always thought if "necessity is the mother if invention" then adversity is it's father.

    Perhaps someone already in the segment and with incredible understanding and skills in the space should create a better SSMS than SSMS ... hint ... hint.
  • Not_HappyNot_Happy Posts: 12 Bronze 1
    Alex B said:
    Hi @Not_Happy,

    The product does in fact fulfill it's purpose for a large number of our customers every day, otherwise we wouldn't have a product to sell, but I understand that in your case it's not meeting your needs which is why we're trying to provide options to get things working for you.

    We're not relying on those limitations, but functioning within the restrictions; when customers hit issues like these, we provide ways to try and get around the limitations and find out what in our product may be causing the issues.

    How many servers are you connected to and how many databases and objects are contained therein?  How many queries do you have open and how large are they?  If you disable code analysis, make SSMS Large Address Aware or utilize some of the option in the last link I sent on managing memory do any of those help?

    If there is an issue with our code we'd like to narrow down where the issue is occurring that is causing the issue in your case.

    Kind regards,
    Alex
    This error is back again now. After almost a year of not having the problem, during which time my laptop was upgraded, today I updated to the latest version of SQL Prompt and within minutes of working on a not very large script (3000 lines) SQL Prompt crashed and brought down SSMS with it. Prepared to give you the benefit of the doubt, I restarted. Inside two minutes it crashed again. It's now crashed a further 3 times within the last 10 minutes, bringing SSMS down with it each time.

    Let's not get into the discussions about whether you want to accept responsibility for your tool bringing SSMS down or not. It's obvious that you don't, but the fact is on a 3000 line script, with only this script open and only SQL Prompt running, it crashes with such frequency as to make it completely useless.

    I'm sure that you will say that the latest version was fully tested and that it couldn't possibly be SQL Prompt causing the problem. It's not my money being wasted so I don't really care whether you draw in the wagons. The fact remains, it's unusable for serious developers.
Sign In or Register to comment.