RAM Defrag

[cbrunny]

TL;DR

- For Excel reasons, I want to try a good RAM Defrag tool.
- Please suggest a RAM Defrag tool.
- Switching to 64-bit Excel should fix the problem, but that isn't an option right now.

____________________

I'm running into an issue where in Excel 2010 I am running out of useable memory for calculations. I have 8 GB of system ram and 64 bit OS, 32-bit Excel (As I write this, I think the issue may actually be the 32-bit Excel...). When it's computing, I'm using roughly 1GB of memory just in Excel - this is under the 2GB threshold that Excel 32-bit can use. As a result, one would think that I would not be running out of system memory.

I did some Googling, and apparently Excel requires continuous blocks of ram for some computations. Given what I'm doing in Excel, I suspect that I need a larger continuous block than what is available, don't have it after using Excel for a few hours, and need to defrag my ram to avoid having to shut down & restart.

So, can you please provide suggestions for a RAM defrag tool?

Thanks!

 

[R0H1T]

TL;DR

- For Excel reasons, I want to try a good RAM Defrag tool.
- Please suggest a RAM Defrag tool.
- Switching to 64-bit Excel should fix the problem, but that isn't an option right now.

____________________

I'm running into an issue where in Excel 2010 I am running out of useable memory for calculations. I have 8 GB of system ram and 64 bit OS, 32-bit Excel (As I write this, I think the issue may actually be the 32-bit Excel...). When it's computing, I'm using roughly 1GB of memory just in Excel - this is under the 2GB threshold that Excel 32-bit can use. As a result, one would think that I would not be running out of system memory.

I did some Googling, and apparently Excel requires continuous blocks of ram for some computations. Given what I'm doing in Excel, I suspect that I need a larger continuous block than what is available, don't have it after using Excel for a few hours, and need to defrag my ram to avoid having to shut down & restart.

So, can you please provide suggestions for a RAM defrag tool?

Thanks!

I could be wrong but how come you got stuck running 32bit office 2010 (or just Excel ?) on 64bit Windows OS since by default 64bit version of the application runs & not the 32bit one 😕

Anyways I use Process Hacker for this kinda thing.

 

[cbrunny]

To my knowledge, 32-bit and 64-bit Office are two different programs that are entirely distinct & require separate purchases. Our organization uses 32-bit probably for compatibility reasons, as it is generally more widely compatible with add-ons and such.

I'll take a look at that link - thanks!

 

[R0H1T]

To my knowledge, 32-bit and 64-bit Office are two different programs that are entirely distinct & require separate purchases. Our organization uses 32-bit probably for compatibility reasons, as it is generally more widely compatible with add-ons and such.

I'll take a look at that link - thanks!

Yes they are but generally speaking MS' own software works that way as in 64bit office for 64bit Windows OS, again I could be wrong wrt office 2010, some of the applications also install their 32bit versions, like IE & WMP, but the default installation almost always runs the 64bit variant unless there's a problem with it.

 

[cbrunny]

Interesting. I'll investigate further.

 

[code65536]

There is no such thing as a RAM defrag tool. Fragmentation of the address space happens within a process and depends entirely on the memory allocator used by that process. You can't "defrag" that externally.

As suggested, you can bypass this problem by using a 64-bit Excel so that it has a much larger 64-bit address space. And no, you do not need to make a separate purchase--much like with Windows, the product keys and licenses are not tied to a particular architecture (i.e., your key will activate 64-bit Office).

 

[oynaz]

To my knowledge, 32-bit and 64-bit Office are two different programs that are entirely distinct & require separate purchases.

No, that is not so. It might have been at one time, but since Win7 and Office2010. at least, both versions are included in the same license.

Our organization uses 32-bit probably for compatibility reasons, as it is generally more widely compatible with add-ons and such.

You are probably right. However, you might still want to give Excel 64 a try. I am even quite sure you can have the 32-bit and the 64-bit version installed at the same time, and since the installation is a 5-minute job, and you have already paid for the 64-bit version, you do not have much to lose.

 

[BrightCandle]

There used to be RAM clearers that allocated big chunks of RAM which forced the OS to free up the RAM from the cache but it also used to make half the operating system be paged to HDD. I am not sure if that would get you a clean 2GB of allocation space however for a 2GB 32 bit process. If it doesn't allocate all the memory on start then its likely not going to get it.

Also I have never managed to get a 32bit process to really allocate 2GB. I know its theoretically possible but with software like Java I am typically limited in and around 1.2-1.5GB. If your algorithm needs 2GB its always going to have problems, as most 32 bit processes can not allocate that much on Windows.

One other possibility to pursue is in your Excel sheet allocate a big chunk of memory before your algorithm starts, as big as you can get (and ideally sufficient for your allocations). Getting the entire block in one go right at the beginning of the algorithm might improve the amount you can get and so long as you clear your use of it wont then be returned to the OS and you will get the working space for your algorithm.

 

[code65536]

There used to be RAM clearers that allocated big chunks of RAM which forced the OS to free up the RAM from the cache but it also used to make half the operating system be paged to HDD.

LOL, people seriously made software like that?! That's... not a very good idea.

I am not sure if that would get you a clean 2GB of allocation space however for a 2GB 32 bit process.

It absolutely would not.

There is a lot of confusion between RAM and address space, which are two different things. Applications deal with address space. Which is private to each one. RAM (and disk-backed swap) is what the OS uses to fulfill an application's use of its address space. Applications are aware only of their address space, which is a virtual space that's private to them and have no knowledge of how that space is backed by RAM or disk.

So application processes like Excel that need blocks of contiguous memory aren't asking for contiguous RAM, they're actually asking for blocks of contiguous address space, which must be carved out of a total address space of either 2GiB or 4GiB for each 32-bit process (the latter if it's on a 64-bit OS and the process is large-address-aware).

And the key point is that management of a process's address space is handled by that process. Windows has no role whatsoever in determining how a process manages its own address space; it's a mechanism that's under the complete private control of the process and not the OS.

How a particular Excel process manages its address space does not necessarily resemble how a Firefox process manages its address space (esp. since Firefox uses a custom heap allocator, jemalloc, instead of the default allocator most other Windows processes use), and what one Excel process does has no bearing on what goes on in another separate Excel process.

One other possibility to pursue is in your Excel sheet allocate a big chunk of memory before your algorithm starts, as big as you can get (and ideally sufficient for your allocations). Getting the entire block in one go right at the beginning of the algorithm might improve the amount you can get

An application that's mindful of the need to allocate large contiguous blocks of address space might tailor its memory management patterns such that it tries to avoid cutting up large contiguous blocks of address space. If an application doesn't optimize for that or otherwise isn't doing a good job of that, you could try to force the issue by doing something that would somehow cause the application to aside a large space (not sure if that's even possible to do in Excel, though).

and so long as you clear your use of it wont then be returned to the OS and you will get the working space for your algorithm.

Reservation of the space is all that's necessary; whether a chunk of space is actually committed or returned to the OS is irrelevant. Again, the OS has absolutely no involvement in what is purely a private, internal matter.


So, again, if you are running into address space issues, you pretty much need to get a 64-bit Excel. There is absolutely nothing you can do outside of Excel that would affect how Excel manages its address space, and there is very little that you can do within Excel to control how Excel manages its address space. The whole purpose of 64-bit Office is specifically to address these kinds of scenarios.

 

[cbrunny]

So, again, if you are running into address space issues, you pretty much need to get a 64-bit Excel. There is absolutely nothing you can do outside of Excel that would affect how Excel manages its address space, and there is very little that you can do within Excel to control how Excel manages its address space. The whole purpose of 64-bit Office is specifically to address these kinds of scenarios.

This is very helpful and informative. Thank you very much!

 

[BrightCandle]

The amount of times I have run into stupid things like this that have resulted in tens to hundreds of hours to fix via programming when the company could have paid $100 for software *shakes head*. Its pretty foolish to have a company refusing to buy an upgrade to run something particular instead of paying tens to hundreds of man hours trying to fix the algorithm, but that is usually how this works. While the advice to get 64 bit Excel is sound and it will work I also know how these conversations usually go.

 

[Zxian]

Your issue is certainly the 32-bit software you're running.

However, if your calculations are starting to get that intensive, I'd really recommend moving away from Excel for your heavy lifting. I recently ran into issues with extremely large data sets stored in an Excel workbook. Just to load the workbook, it was chewing through 12 GB of RAM.

Writing a script using python would be significantly better, and you can still use Excel to produce any graphs that you might need. The setup time may be a bit, but the ability to maintain your work in future (and port calculations to new projects) will pay for itself quite quickly.

 

[cbrunny]

IN this case, migrating isn't going to be an option, but I appreciate the response. I could have used SPSS to make the changes, and in retrospect that may have been a better idea, but still worked out for me in the end. I got the calcs done.

Our org is in very early stages of moving to TM1, which should help as well though that's a different type of calc than I was doing today entirely.