Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

When Analysis Services 2008 R2 Wakes Up

Posted by thomasivarssonmalmo on November 13, 2011

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.

 

Profiler File Load Events

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.

SSAS File Loading Profiler

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.

One Response to “When Analysis Services 2008 R2 Wakes Up”

  1. […] most interesting of which is a new Profiler event called Resource Usage (Thomas Ivarsson recently blogged about some other new events too). I’ve been doing some investigations on it recently, and asking the […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 29 other followers

%d bloggers like this: