Out of Memory Exception
Not_Happy
Posts: 12 Bronze 1
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.
Answers
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.
Redgate Software
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.
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.
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
Have you visited our Help Center?
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.
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
Have you visited our Help Center?
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.
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
Have you visited our Help Center?
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.
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.
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.