Delay on inital connection to Analysis Services

Delay on inital connection to Analysis Services by Bob Duffy

Closed

Type:	Suggestion
ID:	770592
Opened:	11/9/2012 3:52:15 AM
Access Restriction:	Public

Workaround(s)

view

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 (expand)

English

Analysis Services

The proposed solution is to load the metadata from the xml files at service startup and not to delay this until the first user connection.

If a customer has many large databases the first user connection can be delayed by up to 20 seconds per database on the server, which leads to poor experience and a need for customers to create "connection warmup" scripts.

Improved Performance

0 attachments

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