Home Dashboard Directory Help
Search

Delay on inital connection to Analysis Services by Bob Duffy


Status: 

Closed


6
0
Sign in
to vote
Type: Suggestion
ID: 770592
Opened: 11/9/2012 3:52:15 AM
Access Restriction: Public
0
Workaround(s)
view

Description

Following a COLD restart we can see that the inital connection to Analysis Services can be very slow. Note this is just the connection which is before any MDX queries


Using the ProcessMonitor tool from sysinternals pack we can see that Analysis Services is loading the meta data xml files from disk for ALL databases, cubes, attributes, measure groups and partitions on the fist connect and this is for every database on the server regardles of which database is connected to.

In the profiler trace we can see an XMLA command redorded which takes over 20 seconds on first call:
<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
         <ObjectExpansion>ExpandObject</ObjectExpansion>
        </RestrictionList>

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
         <LocaleIdentifier>6153</LocaleIdentifier>
        </PropertyList>
Details
Sign in to post a comment.
Posted by kmarshbank on 9/10/2013 at 10:07 AM
Yes, but wouldn't it be better to have a flag on various objects so the customer, who is best able to determine which objects should load up at service start could do so?

The default could be off for all objects. In my scenario I would like to flag a cube (which should include dimensions, measure groups, partitions, etc.) and then uncheck older partitions that are less likely to be queried. Maybe there's a more efficient way?

Right click on a cube and select "Load at startup" or something like that. We would still need to be able to control it at the partition, dimension, etc. levels.
Posted by Microsoft on 11/14/2012 at 1:36 PM
Thanks for filing this issue. This is something we can consider for a future release, but isn't likely to meet the bar given all the other high priority work we need to tackle in the near future. Note that there is an advantage to the current approach because we don't use server resources for databases that have not yet been accessed.

Thanks,
Analysis Services Product Team
Sign in to post a workaround.