locked
Dashboard Server Cache RRS feed

  • Question

  •  

    We have moved from 32 to a 64 bit server that houses our SSAS database and also ProClarity Analytics and Dashboard Servers on the same box. This has given us substantial performance gain in ProClarity Prof/Std with better processors and we are no longer constrained in SSAS by 2GB memory limit of 32 bit server. However, we still get fluctuating performance with users opening our dashboard. I have been investigating options and have the following questions

    1) The current default settings for cache on PAS for SpaceLimitMB (512) and HardSpaceLimit (1024) are no doubt oriented to a 32 bit environment with a 2G limit.  Does anybody have recommendations what these should increase to in a 64 bit environment (e.g. 32GB RAM). I expect increasing these settings will utilize more ProClarity cache before it uses SSAS cache and therefore speed up queries (including dashboard queries based on briefing book pages), or is this simply not necessary as we are not constrained in SSAS cache (75% upper limit of 32GB befoer cache cleaning starts) and ProClarity will happily (and quickly) use that instead

    2) There is a hotfix for Dashboard Server
    http://support.microsoft.com/kb/939753 , that allows configuration of the cache time-out setting (by default, Dashboard Server caches the records that are retrieved from the database for 60 minutes). Can anyone confirm that if we adjusted this to say 8hours (a working day) then when the first person runs the dashboard early morning, this stays in cache for the whole day and therefore the dashboard should be very quick to load by all users throughout the day.  I am making assumptions here that the dashboard cache has no size constraints and the only activity that causes it to be emptied is the cache time-out setting or if a manual clearing of the cache is actioned in PAS.  (Are there any other situations that clear the cache – stop/restart IIS for instance?)

     

    many thanks to those taking the time to ready this entry, any feedback or insight would be appreciated

    regards

    Stuart Lawrence

    Monday, February 25, 2008 11:18 PM

Answers

  • Hi Stuart.

     

    Your listed scenario is exactly right.  The way you described it is exactly how it all works.  However, there are a couple of caveats.

     

    1.  In version 6.3 of PAS the software had to pass Microsoft's security policy.  Meaning we really had to secure/lockdown the PAS cache a lot better than it was.  What this means to you is that cache entries are a bit more unique now.  Users have to be in the EXACT same set of roles in order to share cache entries.  This means that if User A is in roles 1 and 2, and User C is in 1 and 3, they won't use the same cache entries.  The cache looks at all of this to make sure we are secure. Now, if both users were in only roles 1 and 2, then they would share the cache entries.  This is something to look into as well.

     

    2.  On a dashboard, you can have several views.  Lets take for example a simple 4-Up dashboard.  When you load up this dashboard it appears that all 4 views are being executed at the same time.  However, that isn't exactly the case.  PAS handles requests for views 1 at a time.  This means that whichever view request gets to PAS first gets executed, then the second, and so on.  This is why you will see different views pop up in varying orders when you open a dashboard.  What this means is lets say 3 of the 4 views are cached, but one of them isn't.  Lets also say, that the un-cached view is the first one to get to PAS as a request.  This means it is going to have to be run and the web code created and sent back before anything else gets displayed.  This can partly explain why dashboard views can seem to come back slowly even after being cached.  A great way to see this in action is to run a Fiddler trace on the web server, and a SQL profiler trace on AS.  Should tell you a lot.

     

    I think you are doing a great job with your testing and due diligence.  Keep following your instincts and thoughts on what to keep testing.  I think you will eventually get to where you want to be.  Hope all of this helps in some way.

     

    TJ Nelson

    Tuesday, February 26, 2008 10:13 PM

All replies

  • Hi Stuart.

     

    I want to try and explain a few things regarding your specific questions.  But first you should know that the only 'cache' the Dashboard has is in regards to metadata.  When you connect to the Dashboard the entire database is read into memory.  This is just the metadata like the dashboards, parameters, etcs.  This particular cache has nothing to do with performance and how fast each view in a dashboard is displayed.  All of the view cache and performance comes from PAS.  So if a view is warmed up in the PAS Cache, then it will perform more quickly in the Dashboard.  Now let me address your questions.

     

    1.  The two settings you reference (SpaceLimitMB and HardSpaceLimit) have absolutely nothing to do with RAM.  Those are the hard drive limits for how much space cached files can take up before they start getting purged.  PAS Cache is NOT a data cache like SSAS.  The PAS Cache is just a collection of html, xml, javascript, and png files that make up a particular view.  If a particular PAS view has been displayed once by a user, then subsequent users (in the same exact set of security roles) will utilize the PAS Cache for that view.  If a user does a drill down, cross drill, etc then it will utilize the SSAS cache for data retrieval.  The bottom line is that while RAM is important for PAS, it is not it's only performance gainer, and since PAS is a 32 bit application the most it is going to use out of the box is 2GB anyways.  Regardless of whether or not you have it installed on a 64 bit system.

     

    2.  This particular hotfix is basically aimed at users that have a Load Balanced environment with the Dashboard.  The 60 minute time limit is for the Metadata stored in RAM.  This hotfix allows a user to lower this time limit so that if a change is made on the dashboard admin tool (new dashboard, added views to a dashboard, etc) that new information will get propogated out more quickly to the other dashboard servers in the Load Balanced environment.  It has absolutely nothing to do with making the views execute faster, or speed up other performance.  It is strictly to refresh metadata information from the Dashboard database.  By default PAS cache entries stay in the cache for 6 hours, and that time limit will get reset when users keep using that same view over and over.  The only time that that cache entry becomes un-useable is when it reaches the time limit, a change is made on the cube side making the data invalid, or the PAS Cache is cleared.  Not even a restart of IIS will reset the cache since a mechanism is built in to restore the cache to it's current state after an IIS reset.

     

    While your new hardware will definitely make PAS, and subsequently the Dashboard, perform better, both are still 32 bit applications.  Thus, the constraints of a how much RAM is used is still going to be around whether it's a 32 bit or 64 bit OS/Hardware.  However, the faster speed RAM and processors will definitely improve the performance just because of basic speed increases between hardware upgrades.

     

    hth

     

    TJ Nelson 

     

    Tuesday, February 26, 2008 6:45 PM
  • Hi TJ

     

    thank you so much for taking the time to read this item and to reply.  I feel much better informed now as I'd been struggling to find information to explain what the dashboard server cache was doing, hence my assumptions from an SSAS background. 

     

    However, can I follow up on my first question with the following scenario to ensure I'm absolutely clear in my expectations.  The following scenario assumes no changes in dashboard parameters.  From what I understand, if person A (in security role 1) opens the dashboard for the first time in the day, they will get the views from PAS that are not currently in cache as they expired overnight (no usage) with 6hr limit, the queries will execute against underlying SSAS (lets assume thats not currently cached as no warming occured and cache was cleared due to SSAS restart) and bring back the results.  This would be the slowest the user would get the results displayed back on the dashboard.

     

    If person B (in security role 2) then comes along, they dont benefit from the cached PAS views appearing straight away on the dashboard as they are in a different role, the queries execute this time faster against SSAS cache (due to person A running the same queries), so person B sees the results on the dashboard faster than person A

     

    Person A or B then opens the dashboard again (within next 6 hours), they benefit from cached PAS views (although I'm sure the query still executes against SSAS cache, but I'm interpretting from the explanation that it doesnt submit that query to SSAS?) and the dashboard appears very quickly - this is the quickest they see results on the dashboard.  Equally person C can come along and get the same response as long as they are in the same role as either Person A or B

     

    Are these assumptions true?

     

    If so, we are already warming the SSAS cache to try to avoid the first example with Person A, and always start the day with performance that Person B sees.  Subsequently, we can now add that if we then have all the different roles (we only have 4 roles, and realistically could change that to just have 1) executing the dashboard, then afterwards we should expect any user to experience dashboard performance to match that of Person C ...the dashboard should be its quickest.  This is theoretical, but it does eliminate PAS and the Dashboard server from my investigations into variable performance.  We can get anything from 10 seconds to 45 for most users and sometimes 2 minutes for the dashboard to appear.  I've tested first thing in the morning (as Person A) and I'm often OK (15secs) but others do the same and its 45secs and higher, yet all server and caching conditions are the same.  Hence I'm following all avenues to identify the cause of variability, and consequently I'll probably look next into IIS contention next unless you have any recommendations?

     

    Regarding Qu 2) I'd seen a few entries before where people had issues with updating dashboards and not seeing their changes in load balanced environments, so the reponse to the 2nd part makes total sense, for them its a case of reducing the cache timing to update the metadata

     

    let me know what you think

    thanks

    Stuart

     

    Tuesday, February 26, 2008 9:39 PM
  • Hi Stuart.

     

    Your listed scenario is exactly right.  The way you described it is exactly how it all works.  However, there are a couple of caveats.

     

    1.  In version 6.3 of PAS the software had to pass Microsoft's security policy.  Meaning we really had to secure/lockdown the PAS cache a lot better than it was.  What this means to you is that cache entries are a bit more unique now.  Users have to be in the EXACT same set of roles in order to share cache entries.  This means that if User A is in roles 1 and 2, and User C is in 1 and 3, they won't use the same cache entries.  The cache looks at all of this to make sure we are secure. Now, if both users were in only roles 1 and 2, then they would share the cache entries.  This is something to look into as well.

     

    2.  On a dashboard, you can have several views.  Lets take for example a simple 4-Up dashboard.  When you load up this dashboard it appears that all 4 views are being executed at the same time.  However, that isn't exactly the case.  PAS handles requests for views 1 at a time.  This means that whichever view request gets to PAS first gets executed, then the second, and so on.  This is why you will see different views pop up in varying orders when you open a dashboard.  What this means is lets say 3 of the 4 views are cached, but one of them isn't.  Lets also say, that the un-cached view is the first one to get to PAS as a request.  This means it is going to have to be run and the web code created and sent back before anything else gets displayed.  This can partly explain why dashboard views can seem to come back slowly even after being cached.  A great way to see this in action is to run a Fiddler trace on the web server, and a SQL profiler trace on AS.  Should tell you a lot.

     

    I think you are doing a great job with your testing and due diligence.  Keep following your instincts and thoughts on what to keep testing.  I think you will eventually get to where you want to be.  Hope all of this helps in some way.

     

    TJ Nelson

    Tuesday, February 26, 2008 10:13 PM
  • Thanks for the confirmation and extra information TJ, I think going forward I might simplify our role structure further for the first caveat and I think the 2nd one is fine as we are using the dashboard well as a portal for people to commence their high level analysis so we should see all views concurrently cached on the basis that somebody is always going to the dashboard first thing in the morning as opposed to straight into ProClarity Professional to the individual views etc - added to which we measure the dashboard response to the point where all views are visible, and they do appear pretty much all at once

     

    I appreciate your help

    cheers

    Stuart

    Tuesday, February 26, 2008 11:11 PM