Working with large analysis services customer we found some complaints that users have to wait an additional 20-60 seconds to run basic reports on top of the usual time to warm up the calculation script.
The slow down only happens on a cold server restart, or if the service is shut down and disk un-mounted (which is common if you are doing scale out using SAN snapshots).
Using the SQL profiler we can see that following a “connect” command to analysis services the following XMLA command is issued as part of the connect process.
<RestrictionList xmlns=”urn:schemas-microsoft-com:xml-analysis”>
<ObjectExpansion>ExpandObject</ObjectExpansion>
</RestrictionList>
<PropertyList xmlns=”urn:schemas-microsoft-com:xml-analysis”>
<LocaleIdentifier>6153</LocaleIdentifier>
</PropertyList>
Using the fantastic ProcessMonitor tool by Mark Russinovich and Co (Available Here) we can see a lot of small disk activity on the very first connect to the analysis server as all the databases are “mounted” and the xml meta data files for cubes, dimensions, attributes, measure groups and partitions are read from disk into memory in most cases using quite small IO sizes (2k). In our case this takes 25 seconds.
An example of some of the traffic interaction is below. You can see that following the first connection the definition files will be read from disk in 2K IO size, for pretty much every attribute, measure group and partition xml file.
This is a completely different approach that the SQL Server DB Engine which “mounts” databases during the start-up of the service.
What does this mean ?
I guess this means a few things:
1. Be careful of having too many databases in an analysis instance. The mounting is done for all databases at once on first user connect.
2. A warm-up script of some sort after service start-up is probably worthwhile on larger solutions, not just to warm up the calculation script and/or storage engine cache, but also to force the databases to mount.
What could the product do Better ?
some suggestions for the product team:
- Mount the databases at service start-up (as per DB Engine). I would have through it would be more efficient to mount the databases at the service start-up (as an asynchronous process) rather than on the first connect by a user.
I added a connect item, but its pretty obscure improvement, so I’m not sure how much love it will get.
This was SQL2008R2, so I’m not sure if 2012 behaves differently.
