Memory Usage with SQL Prompt 5.2

cport

Hello, I previously sent in a support request that wasn't able to provide much help for my issue so thought I would try the forums to see if someone may have some tips or tricks to help fix or at least work around this problem I am having.

I have a database with a large amount of objects (over 180,000), and anytime that SQL Prompt tries to access that database, it eventually give me an insufficient memory error. I typically try to close out the "Processing Columns..." window which seems to help, but ideally I'd just like to exclude that database from being used at all by SQL Prompt. It appears that this was possible in previous versions, is it worth trying to downgrade and using an older version instead?

Thanks in advance.

Brian Donahue

Hi Chris, I wouldn't expect you to get an answer for this from anyone in the communiuty, as we do not have the option to control cache usage any more. But I am currently in the process of wrapping up all of the complaints we currently have running about the lack of scalability in SQL Prompt and will be sending them to the product manager.

You may also want to make some noise over at UserVoice - this is the website mamagement looks at and is publicly visible so whatever goes on this site obviously carries more weight than posting to the internal support forum. Here is a link to the uservoice site:

http://redgate.uservoice.com/forums/944 ... uggestions

cport

Hi Brian,

Thanks for the reply, I'll post something over at UserVoice and hopefully a future version will support better cache management.

Thanks!

Brian Donahue

Hi Chris,
I just talked to one of the developers who formerly worked on SQL Prompt and he said that since v5 it has been caching partial bits of schema as needed rather than storing a schema snapshot of the entire database.

It may be worth debugging - perhaps it isn't a design issue as much as a bug where it is trying to allocate too large an array or something like that.

Can you please download ftp://support.red-gate.com/utilities/re ... bugger.zip , extract and run RedFlag-x86.exe and use actions->attach process and attach to SSMS. Then go to SSMS and connect to the problem database. When you get the outofmemory exception, you can use file->save and send that file to our support address at Red Gate.

Hopefully this will reveal any bug that may be an easy fix.

Brian Donahue

In the end, we did work out that regardless of the cache management, SQL Prompt gathers a list of all columns on every table in the database. When this list gets to around 35,000, SQL Prompt typically runs out of memory.

I can't find any way to work around this issue at this time.

Brian Donahue

I take that back - in SQL Prompt 5.3, there is an option to limit the number of columns, which you can find in the engineOptions.xml file. Settings MaxSupportedColumns to a lower number will probably help.