none
SQL Server Analysis Services RRS feed

  • Question

  • Backgroung:
    We have allocated separate server with to SQL Analysis service. 
    Having 32 GB static RAM and upto 64 GB dynamic RAM.
    Pus we have almost 65 Database in Analysis service.
    Issue:
    When ever the server or the Analysis service get restarted. Analysis service take a lot of time and also it waits to consume 6 GB of memory before it get responsive.
    This behavior is just for first time running the Analysis service. After that Analysis service work fine.

    Can anyone know what is happening in the start of service that causing this much delay.
    I really appreciate all your time and help in this.
    Monday, November 18, 2019 4:45 PM

Answers

  • Are there any number good number of Database to memory ratio for better performance.

    no, it totally depends on the size and complexity of the individual models

    And what is good better for this level of Analysis service setup is fine with Virtual Server or Physical server would be better.

    A physical server obviously has less overhead, but I have not seen anyone using physicals servers for over 10 years now. The extra flexibility with virtuals is worth the small overhead. However I have not seen anyone using dynamic memory on a database server. Analysis Services in particular will do a lot of allocation/deallocation of memory if it gets into the higher ranges of memory usage (above half way between the high and low memory limits). It does not use a buffer pool like the relational engine does so it's potentially going to periodically incur longer waits while it waits for dynamic memory to become available (depending on the load on your host) 


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Kazimcc01 Friday, November 22, 2019 10:39 AM
    Thursday, November 21, 2019 7:55 PM
    Moderator

All replies

  • What sort of instance is this? Is it a Tabular Instances?

    How are you connecting to the service to see if it is responsive?

    If it's a tabular instance and you are connecting using the SSMS object browser this will trying and enumerate all the databases, so it will force all 65 of your database into memory before it responds. This is an SSMS issue/feature. If you connect with a standard client tool like Excel or Power BI Desktop it should only force the selected database to load into memory when you connect.


    http://darren.gosbell.com - please mark correct answers

    Tuesday, November 19, 2019 12:25 AM
    Moderator
  • Hi Darren,
    This make a lot of sense.
    we are using multi-dimension instance & we for analysis service client are using SSMS 2014 and Visual studio.
    This make a lot of sense. 
    Is this SSMS issues is in selective version?
    What if you update to latest version of SQL server/SSMS currently we are using 2014. Does this help? 
    Plus if you can just clarify the memory management in multi-dimension. What I read its use the disk space so no need to bring all database to cache.. 
    Kindly explain a bit this concept. 

    Regards,
    Kazim Ali
    • Edited by Kazimcc01 Tuesday, November 19, 2019 1:48 PM
    Tuesday, November 19, 2019 1:37 PM
  • No, if you are using multi-dim this makes no sense at all as multi-dim only needs to load metadata.

    The behaviour of SSMS is not really something that needs to be fixed, it's just something that you need to be aware of. When you expand the databases folder you are asking the server to show you the metadata for all the databases on the server. If the server has just been rebooted and does not have the metadata fully loaded it will then go and load the metadata for all the databases. This is not really a big issue as the metadata needs to be loaded at some point anyway. Often after re-booting a server we would connect with SSMS and open the databases folder to force the server to pre-load all the metadata.

    But for multi-dim this typically only takes a second or two per database at most.

    Do you make use of a lot of static named sets in your cubes? Or security roles with a lot of members in the allowed sets. These are about the only things I can think of with multi-dim that might impact on startup time.



    http://darren.gosbell.com - please mark correct answers

    Tuesday, November 19, 2019 9:50 PM
    Moderator
  • Hi Darren,

    Are there any number good number of Database to memory ratio for better performance.
    And what is good better for this level of Analysis service setup is fine with Virtual Server or Physical server would be better.

    And Thanks for your response.
    I really appreciate this.
    Thursday, November 21, 2019 7:25 PM
  • Are there any number good number of Database to memory ratio for better performance.

    no, it totally depends on the size and complexity of the individual models

    And what is good better for this level of Analysis service setup is fine with Virtual Server or Physical server would be better.

    A physical server obviously has less overhead, but I have not seen anyone using physicals servers for over 10 years now. The extra flexibility with virtuals is worth the small overhead. However I have not seen anyone using dynamic memory on a database server. Analysis Services in particular will do a lot of allocation/deallocation of memory if it gets into the higher ranges of memory usage (above half way between the high and low memory limits). It does not use a buffer pool like the relational engine does so it's potentially going to periodically incur longer waits while it waits for dynamic memory to become available (depending on the load on your host) 


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Kazimcc01 Friday, November 22, 2019 10:39 AM
    Thursday, November 21, 2019 7:55 PM
    Moderator