Memory use

• 
  It brings me another question.
  
  Probably SPv3 is getting into your memory, the "source" from all my objects, including all my procs. For my databases, I had a lot of procs, certainly the procs scripts are consuming most momory than tables objects.
  
  Maybe it be a good ideia, in the first moment, when SPv3 gets the database schema, get only my procs names and parameters. If I use the command alter procs.. then SPv3 get the proc source.
  
  Thanks

  EsioN December 14, 2006 08:41

  - 0 +
• 
  Hi Ésio,
  
  I've been assured we don't store the creation SQL of stored procedures and such in memory.
  
  The reason why Prompt is using so much memory is the data structure we use to make filtering the candidate list fast, even for databases with lots of objects. Essentially, we went for speed over memory.
  
  Obviously, or maybe not, we use a separate candidate list for each query window.
  
  I'm afraid there is not a lot you can do, other than getting more memory, or using less databases simultaneouly. You can block databases from being cached, but then you don't have Prompt.
  
  Finally,

  Testing SPv3 in my production environment.
  At this moment, 1 QA opened with 9 windows, using 3 different servers, and each windows pointing to a distinct database.
  QA is consuming 110mb RAM, and with another 450 mb im swap.

  Could you tell me what exactly you mean by '450 mb im swap'?
  
  Regards,
  
  Tilman

  Tilman December 18, 2006 08:15

  - 0 +
• 
  Hi Tilman
  This values '450 mb im swap' I catch in Task Manager in column: Virtual Memory Size.
  
  
  And I picked these memory values from Task Manager now, for your information:
  Begin
  1 - I minimized my QA yesterday nigth (with 8 windows), and still doesn't started working with this until now, I have these values: Memory Usage 28MB and Virtual Memory Size : 497MB.
  
  2 - I maximized QA and starting a new window (9 in total now), the values growed: 231MB Memory Usage, 699MG Virtual Memory
  
  3 - I closed 7 windows, the values didn't shrink a lot.
  End
  
  My machine has 1GB RAM, so it's not a bad physical memory value.
  
  I understand the memory SPv3 is using, is of course used to build the lists, but you must agree with me that 699MB RAM is a high value, and we need to consider I'm not opening only QA in my station. As I mentioned, I'm testing in a real production environment, with all task I do every day.
  
  About blocking some databases, I'm alredy done this, I'm using only that I really need. Maybe it be a good ideia, the system database come blocked by default... They are small, but maybe this helps.
  
  I understand SPv3 uses memory to do its job, but maybe it will be a good idea review this memory consumption use. I don't understand why it uses different candidate list for each query window, if that distinct windows use the same database/credentials.
  
  Thanks a lot
  
  Ésio Nunes

  EsioN December 19, 2006 08:03

  - 0 +
• 
  Hi Esio,
  
  
  There are a number of issues that affect the memory usage of SQL Prompt, although I agree that 700MB is a *lot* of memory for anything to be using. Having said that nowadays I'm kind of used to a single instance of Visual Studio 2005 swallowing a good 400MB, which clearly isn't great either, but then it's a very complex application.
  
  Now, as Tilman has said one of the biggest uses of memory by SQL Prompt is the candidate list data structure. We could have gone for a less memory hungry structure, however this would have meant that the performance would have been truly awful for larger databases.
  
  The other big usage of memory is the database meta-data, but I'm assured by the SQL Compare guys that this does not include the definitions of text objects such as views, SPs etc. We store a maximum of 3 databases in memory at once (you can actually change this value if you edit the "SQL Prompt Meta Data Cache.cache" file, but this isn't recommended - certainly DON'T decrease it). The three are for
  
  1. the database you're currently using,
  2. the master database (necessary for SQL Server 2000 system object support), and
  3. the last database referenced for cross-database queries (not currently supported so you could theoretically decrease the in-memory limit to 2).
  
  However, in your case I don't think this is a big memory hog. The least-recently used database is flushed from memory when you pull in the meta-data for a new one, but the candidate lists aren't ditched in this circumstance. This is because they can take a while to repopulate so there'd be a noticeable delay before you got intellisense again whilst the meta-data was pulled back from disk and the data structures rebuilt. Now we simply didn't envisage people using 20-odd query windows at once, where clearly this becomes a problem, so if we get time we might put in a fix for this in the final release.
  
  The other factor is that the population of the candidate lists depends upon editor context, hence whilst there is some sharing of data structures, for different query windows to the same database you have different candidate lists based on the caret context within the script in each window. Again, this is something that we could put in a fix for, although this would be more involved and would not go in for the final release. You'd also need to consider that when switching between query windows to the same database there would be a noticeable delay before intellisense becomes available, although not as great as that when the meta-data has to be pulled back from disk and the completion data structures entirely rebuilt from scratch.
  
  Hopefully that gives you some insight into what's going on. We've done a lot of work on this version to ensure you don't see the kind of runaway memory usage that version 2 suffered with, however I think it's fair to say there's more we can do to get the footprint down when working with a large number of databases and query windows.
  
  
  Many thanks,
  Bart

  Bart Read December 20, 2006 06:26

  - 0 +

Add comment

Please sign in to leave a comment.