OPENQUERY MDX query is very slow during cube processing

Question

I have multiple cubes on SSAS server and few cubes which are 10GB+. One of .NET apps is accessing relatively small cube <1GB cube via OPENQUERY.

When one of big cubes is processing (36GB+) .NET application timeouts as there is default timeout in place (30s), I could increase timeout but it would mean users will have to wait anything from 1 to 5 mintues for query to complete. If I run the same MDX queries directly (SSMS or ADOMD) I got results <1s in the same circumstances.

MSOLAP Provider has Allow InProcess=true. Changing app to use ADOMD is not viable option, as it would take far too much resources.

I have tried to use Resource Governor and separate processing from app querying and other requests - it alleviated issue partially and now not every query timeouts - just most of them.

Is there anything else I could do to help SQL Server and SSAS to get along?

PS. While looking at what happens on server I have noticed a lot of ODBC (and related) waits on SQL Server. To me it looks like while cube is processing and accessing data from SQL Server it "blocks" OPENQUERY requests towards cube via OLEDB/ODBC provider. I came to this conclusion because even if I make sure with Resource Governor that there is enough free CPU and Memory to handle .NET App requests it still happens, server just seems less busy (decreased CPU/Memory usage)

edit:

Each .NET application call runs few MDX queries run towards SSAS - every query does not return more than 100 rows. All of this is combined in temp tables into final result. Now, I know it is logical place to start and optimize code, however code is the very last thing we want to change. It is well tested and performs well otherwise.

I am looking for configuration and infrastructure ideas.

Answer 1

We experienced very similar blocking and timeout issues when using OPENQUERY, for mdx, in our stored procedures.

Approximately 2 years ago we switched to an open source CLR solution - we now use these stored procedures in a similar way to OPENQUERY but without the issues!!

Currently it is available here:

https://olapextensions.codeplex.com/