Constantly running out of memory

Provides intelligent code completion for SQL Server editors.

Moderators: JonathanWatts, David Atkinson, Anu Deshpande, Paul Stephenson, Michelle Taylor, justin.caldicott, Mike Upton, Aaron Law

Constantly running out of memory

Postby wayneph » Mon Aug 06, 2012 4:58 pm

I've got SQL Prompt installed in Management Studio primarially for the functionality that used to be Refactor. I am working with PeopleSoft databases that each have over 29,000 Tables and 21,000 Views so whenever I switch between DEV and TST environments, I get a message from SQL Prompt that there has been a memory exception error and I have to unload all of the databases.

Any recommendations on how to fix this? (Assuming that re-releasing SQL Refactor as a separate product isn't going to be an option...)
--wayne
wayneph
 
Posts: 17
Joined: Thu Jul 19, 2007 2:57 pm

Postby Chris Auckland » Tue Aug 07, 2012 8:24 pm

Thanks for your post.

Which exact version of SQL Prompt are you using? I believe a recent update had some code added that would stop registering objects if it was going to cause it to run out of memory.

What happens after you unload the databases? Is this a performance issue, or does it mean you can't use any of the refactoring functionality?
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby wayneph » Wed Aug 08, 2012 3:35 pm

Thanks for the help Chris.

I currently have SQL Prompt 5.3.4.1 installed. (Officially I'm still in the Trial period, but we have a PO that is being worked on for 10 people on our team. I'd like to have this worked out before we get it on everyone's machine.)

When I unload the databases, things continue to work until the next time I switch databases at which point I get the error message again. For the most part, I am able to use the Refactor product in between loads, however with SQL Prompt installed (and Display Suggestions turned off) I have to manually trigger the SQL Server Intellisense which is also cumbersome.

After playing with it a little more, I seem to have the biggest problems with using multiple databases at the same time. As long as I only use objects in the database that I am primarially connected to, it works pretty well. As soon as I try to compare data between databases for test validation, etc... I run into the Outof MemoryException.

With the Databases Unloaded I was able to successfully refactor, but I lost the ability to use snippits and auto insert object/column names through SQL Prompt or Intellisense. Opening a new query window allowed me to start getting suggestions again until the next time it unloaded databases.
--wayne
wayneph
 
Posts: 17
Joined: Thu Jul 19, 2007 2:57 pm

Postby Chris Auckland » Wed Aug 08, 2012 3:56 pm

It's normally the number of columns in a database that cause this sort of behaviour. Can you run the following and let me know the result:

SELECT COUNT(*) FROM sys.syscolumns AS S

Can you also run:

SELECT COUNT(*) FROM sys.sysobjects AS S
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby wayneph » Tue Aug 21, 2012 8:34 pm

Chris,
Sorry for the delayed response. It's been a busy couple of weeks and there isn't a slowdown in sight... That being said, here is the information you requested from our Production Database:

Columns: 658508
Objects: 51084

One of our servers has multiple Training and Test databases that each have the numbers above. We are getting ready to start a PeopleSoft Financials implementation (these databases are PeopleSoft HCM) and from what I hear there are even more objects and columns in that one...
--wayne
wayneph
 
Posts: 17
Joined: Thu Jul 19, 2007 2:57 pm


Return to SQL Prompt 5

Who is online

Users browsing this forum: No registered users and 1 guest