Monday, February 18, 2013 11:26 PM
I know there are a few posts talking about the issue already. However, I have not got one working for my case.
I ran a MDX query from SSMS. After nearly a long time, I got the error:
XML for Analysis parser: The XML for Analysis request timed out before it was completed.
SQL server 2008R2 (SP2)
The cube works alright for some other queries. All setting are as default.
Any advice please?
Thanks a lot.
Thursday, February 21, 2013 9:27 AMModerator
In this case, I would recommend you use SQL Server Profiler to capture some events when you run the MDX query. For more information, please see:
Based on the error message, it might be a Timeout issue. Please try to increase the Server Timeout property to see if this help. Following the steps below:
- Connect to your SSAS instance.
- Right-click on the instance and select "Properties".
- Select "Show Advanced(All) Properties" checkbox.
TechNet Community Support
- Edited by Elvis LongMicrosoft Contingent Staff, Moderator Thursday, February 21, 2013 9:28 AM
Friday, February 22, 2013 4:34 AM
Thank you for the response.
I increase all the timeout to 7200. The query finished OK once and failed the other with timeout.
The same query runs OK on the SQL 2008 but causes problem on the server which was upgrade in-place from 2008 to 2008R2. I am not sure if the issue is the upgrade.
By the way, the query is on a measure that uses By account and custom roll up. If I query the other normal measure, it is fine.
Any hints please?
Monday, February 25, 2013 5:14 AM
I now simplify the comparison.
Use the same cube, same MDX query on 2 server.
Server A takes about 5 minutes. Server B takes hours even it has more memory/cpu!
Profiler shows that on the server B, there are a lot more events with Cache Data subclass on the same ObjectPath. The issue is on server B only. I have set all SSAS settings to default on server B. B is upgraded from sql 2008 to 2008R2 in-place.
- Edited by Koalass Monday, February 25, 2013 5:31 AM
Tuesday, February 26, 2013 10:23 PM
The query timed out at 1hour for the first time, but finished in 2 minutes for the 2nd time.
Friday, March 01, 2013 1:55 AMModerator
Wednesday, April 03, 2013 4:19 AM
I solved the issue. I changed the calculationcoverpolicy from 9 to 0 in msmdsrv.ini. It is related to the cache policy.
- Marked As Answer by Koalass Wednesday, April 03, 2013 4:20 AM