locked
PowerPivot 32 bit crashing once it reaches 1024 MB memory RRS feed

  • Question

  • Hi PowerPivot Team,

    When the memory for powerpivot reaches about 1 GB it crashes. I thought 32 but can refer up to 4 GB of memory. Am I missing something.

    Monday, January 5, 2015 10:13 PM

Answers

  • Hi Bellicose,

    According to your description, you PowerPivot crash when the memory for PowerPivot reaches about 1 GB, right?

    PowerPivot for Excel is an Add-In which uses a local version of Analysis Services (SSAS) to process data and make calculation to respond to user queries made using Excel 2010. The SSAS engine is loaded in-memory in the process of Excel. The memory available for PowerPivot in a 32 bit version of Excel is just above 1Gb. To avoid this issue, you can use a 64 bit PowerPivot for Excel instead of 32 bit.
    http://sqlblog.com/blogs/marco_russo/archive/2010/01/26/memory-considerations-about-powerpivot-for-excel.aspx

    Regards,


    Charlie Liao
    TechNet Community Support

    Tuesday, January 6, 2015 7:50 AM

All replies

  • While Power Pivot 32-bit can only address 2GB of memory, it seems strange that you are hitting this error at 1GB.

    There was a recent upgrade for Office 2013 which added a safety net to kill the process when memory was about to hit the system limit, rather than allowing Excel to hang. I don't think you should see this at 76% utilization though.

    Monday, January 5, 2015 10:25 PM
  • The task manager view you have does not necessarily show all the memory in use by Excel. Go to the Details tab in Task Manager and add the Working Set field. I believe this better reflects all of the memory used by the process (Private and Shared memory).

    Brad Syputa, Microsoft Power BI This posting is provided "AS IS" with no warranties.

    Tuesday, January 6, 2015 12:14 AM
  • Hi Bellicose,

    According to your description, you PowerPivot crash when the memory for PowerPivot reaches about 1 GB, right?

    PowerPivot for Excel is an Add-In which uses a local version of Analysis Services (SSAS) to process data and make calculation to respond to user queries made using Excel 2010. The SSAS engine is loaded in-memory in the process of Excel. The memory available for PowerPivot in a 32 bit version of Excel is just above 1Gb. To avoid this issue, you can use a 64 bit PowerPivot for Excel instead of 32 bit.
    http://sqlblog.com/blogs/marco_russo/archive/2010/01/26/memory-considerations-about-powerpivot-for-excel.aspx

    Regards,


    Charlie Liao
    TechNet Community Support

    Tuesday, January 6, 2015 7:50 AM
  • I see, thanks for the information, I have couple of books with larger data sets that crash at about 1 GB, and I kept thinking I was doing something wrong in the book iteself.

    Unfortunately I can't switch to 64 bit as much as I like since the database that I'm connecting to has only 32 bit odbc drivers at this point. 

    Tuesday, January 6, 2015 6:47 PM
  • You may find these two links useful in optimizing the size of your workbook to stay under the memory limit.

    Additionally, if you have already optimized the workbook for size and you are still running into memory issues, it may be worthwhile to add a step to your refresh process and dump the DB to a set of flat files and then consume those in Excel 64-bit (either Power Query + Power Pivot or straight Power Pivot).

    Tuesday, January 6, 2015 7:58 PM
  • Greg mentioned how a recent Office 2013 upgrade can help you. Read more here.

    Out-of-Memory Improvements

    Running out of memory in Excel 2013, in scenarios when the Data Model was involved, caused a computer freeze or Excel crash. In this release we targeted these scenarios and introduced a better and friendlier customer experience.

    Specifically, in the scenario when 64-bit version of Excel 2013 is running low on physical memory, or if the user is running a 32-bit version of Excel 2013 and getting close to the 2GB virtual memory limit, Excel will detect this, will stop all allocations and will show the “Out of Memory” error message to the user.

    • Proposed as answer by Gil RavivMVP Wednesday, January 7, 2015 12:22 PM
    Wednesday, January 7, 2015 12:22 PM
  • Hi Gil,

    I'm getting those out of memory errors (or that's what I think they are) since what I see is a small dialog black box popping in the middle of the screen (I'm assuming since it's I don't see anything in the box just a black area in the middle of the screen). So I usually go to task manager to end it.

    Wednesday, January 7, 2015 7:52 PM
  • Could you share a screenshot of the black box errors?

    When did you start having these errors?

    Thursday, January 8, 2015 7:43 PM
  • here it is, I began receiving these after last update...


    Thursday, January 8, 2015 8:03 PM