What is using all that memory on my Analysis server instance ?

With the release of SQL Server 2012 tabular models identifying what the memory use of objects on your server instance is has become more important then ever. Since everything is in memory, being able to tune the model to remove columns that take up loads of memory could be very valuable. Or being able to use your development database to extrapolate the memory usage that you will have on you product machine.

I have created a PowerPivot workbook that will allow you to investigate the memory usage on your server instance (this report could also be used on a MOLAP instance).

The report contains two worksheets. Worksheet 1 contains two dashboards, the first dashboard contains the top 10 tables on the server instance by memory usage.

The second worksheet allows a more detailed investigation on all the objects on the server by using a pivottable. I have created a hierarchy of all the objects that allows to start at the top to bottom when you want to investigate the details.

of course since this is a pivottable there you can create your own insights if you want:

 

You can download the workbook and try for you self here BISMServerMemoryReport. Now also available is the Excel 2013 version here.

In order to get this working on your tabular or multidimensional server is change the connection:

After this, refresh the table and refresh the data in the workbooks from Excel.

The data itself is being retrieved into one table by a DMV function:

Then the data that came in was manipulated by several DAX calculated columns using new SQL 2012 DAX functions like parent child functions and created hierarchies for them.

I hope you can use this workbook, let me know if you have any comments or feedback on the report. I am always looking for new insights and ways to improve the workbook.

10 Responses to What is using all that memory on my Analysis server instance ?

  1.  

    I’m using my phone to read this (hence with tiny writing) and at first glance I read “Sales Country – model” as “Sean Connery – model”. Being awake with jetlag at 4 in the morning isn’t helping none either :-)

    Nice post Kasper

  2. haha that would be a pretty cool model ;)

     
  3.  

    Kasper,

    this is really nice!
    I discovered a database I have to analyze log from IIS… should be optimized using a best practice I written in the book that I haven’t applied! :)
    Very nice!

    Marco

  4.  

    Nice post!

    You have given me a bunch of homework assignments. Now I have to figure out why global allocators are eating 57% of my memory. Also I have to figure out why taskmgr reports that my instance is using 48 MB RAM but the DMVs for object memory usage are reporting only 15 MB used.

    Maybe I should give you some homework on how to explain the difference between memory usage reported by the various DMVs and the OS, and also how to free up the global memory used by AS. That should keep you busy for a while, if you have nothing else to do ;)

  5. Hi @Cathy Dumas ,
    The difference between taskmgr and dmv is explained in the intro page:
    1) The memory report does not take the size of the Analysis Services executables into account (hence the memory size in the taskmanager is slightly lager). This is approx 150 MB.

    The global allocators are memory allocated objects that are not assigned directly to a model object. But there might be some objects that are related to the model that are not placed correctly. Unfortunately there are some issues with the object_memory_usage so this report will not give you a 100% accurate report of all your memory. It does give you an idea on the size of your objects and what is eating up most of the space.

    Thanks,
    Kasper

     
  6.  
  7.  

    Great difficulty in trying to download this….. I remember in the late 90s when I said its going to be so nice in the next century when it will be so easier to download things. NOT!!!

  8. Hi @Eric ,

    What is the issue ? You should be able to open the link then do a save as to desktop.

    Hope that helps.
    Kasper

     
  9.  

    When I downloaded this all of the Level calculated columns are erroring out. Excel 2010 version.

  10.  

    As a follow up. The errors in the Levelx calculations were due to the expression used for the RIGHT() function evaluating to a negative number in our environment. I added an IF statement to all of the Levelx calculated columns to catch those instances and the model started working perfectly.

    Thanks for the great tool Kasper!

leave your comment