When Analysis Services 2008 R2 Wakes Up

Reposted from Thomas Ivarsson blog with the author's permission.

This is the start of a small series about SSAS performance scenarios that might help in everyday performance discussions with users and customers.

The first post is about why SSAS initially can be perceived as slow after a restart of the SSAS service and when the first user connects after the restart. It does not matter if the first users is a SSAS administrator or a user connection with Excel. I have used the Adventure Works 2008 R2 demo cube project but I think that you will see the same behaviour on versions from SSAS 2005.

This post only use the SQL Server Profiler performance tool and Management Studio or Excel 2010 to connect to the Adventure Works cube after a restart of the SSAS service.

Go to SQL Server Profiler under Performance Tools and start a new trace with a connection to SSAS. Use the standard template and save it to a file to get a first view about what you can do with Profiler. On the second tab I have selected the following events in Events Selection.

In a future post I will continue with more events in Profiler after a restart of the service but this can be enough for this scenario. Actually you will only have to select the File Load Begin and the File Load End events.

Now you can stop the SQL Server Profiler, if it is running, and push the clear trace window button in Profiler. Restart the SSAS service in SQL Server Configuration Manager and when the service is restarted you can start the Profiler with the trace that you have created. When both the SSAS Service and Profiler is started you can connect to SSAS with a client like Management Studio or Excel.

In Profiler you will see the following file load events, in the picture below, that will only show up after a restart of the SSAS service. This are the events that can make you perceive SSAS as slow during the first connection.

If you play with clear cache XMLA commands or clearing the file system cache the file load events will never show up if you do this on the same cube database. When you connect with XMLA in Management Studio all database files on the server will be loaded from the SSAS files. With and MDX query it is only the target database files that will be loaded.

What can you do to improve performance of the SSAS file loading? Buy a quick I/O system(disks). With unlimited budget I will recommend solid state disks that will give you quite amazing performnce after a restart.

In a coming blog post we will have a look at more events after a restart of the service and compare that to what happens when you clear the SSAS cache and the file system cache.