I've recently been getting long delays when connecting to analysis services 2008 via SSMS, some times up to 5 minutes.  SSMS  hangs while waiting to connect, and some users are getting delays when connecting via Excel or ProClarity.   I've done a bit of investigation and found a tip to clear the Windows event logs which I've tried.  This temporarily fixed the issue on my development server, but not on my live server. 

When connecting to the DB engine instance on the same servers it connects instantly and CPU and RAM usage look fairly low.  Grateful if anyone can please give me some tips on how to troubleshoot further! Some other environment information:

• Development server:  Win Server 2003 SP2, 3GB RAM, 2 x virtual CPU  (due for replacement soon!)
• Live server:  Win Server 2008R2  16GB Ram, 4 x virtual CPU Sql Server 2008 R2
• 11 AS databases on both servers

• Edited by MrAndyS 22 hours 2 minutes ago

Hi are there any Load Job running while u try to connect?--> At the end of each Load AS has to commit its changes and requires an INSTANCE WIDE LOCK which could take up to several minutes depending on the metadata.

KR

Hi, thanks for the reply.  Our cube processing runs overnight and usually finishes by around 3:30am so this shouldn't be an issue.   I've looked at our trace file and I can just see a few user queries running at the last time I connected and experienced a delay.

I've noticed this mainly seems to happen when I connect to the instance for the first time after a day or so.  Is there a scenario where SSAS needs a bit of time to "wake up", like IIS where it takes a while to spool up when you access a web site for the first time in quite a while?

Usually after a restart  of the service or the server it takes also longer on our side.

look at http://www.ssas-info.com/analysis-services-articles/66-mgmt/3172-when-analysis-services-wakes-up-part-two

If you a large MDX script or have a very expensive default measure it may cause issues like this. If you have some expensive named sets in the MDX script they could maybe cause issues like this. You should be able to get some more information by running a profiler trace while trying to connect. The article yger linked to shows an example of this.

Hi MrAndyS,

In Analysis Services, for the first time you connect the SSAS server, it will mount all the databases. Since you have 11 databases in your sever, it may need more time to pre-load. You can use the Process Monitor to see if there's any issue when connecting to SSAS. One suggestion is mounting the database at service start-up. Please refer to a blog below:

http://blogs.prodata.ie/post/Anatomy-of-Analysis-Service-Startup-e28093-Slow-connections.aspx

Regards,