RE: Troubleshooting Execution Location

From: Jonathan Levine (JonathanLevine_at_discussions.microsoft.com)
Date: 06/17/04 

Date: Wed, 16 Jun 2004 19:24:01 -0700

Hi Chris,

Thanks very much for your detailed email.

"Chris Webb" wrote:
> Hi Jonathan,
>
> One further scenario to try with your queries is running them directly on the server - what happens
> then? That said, I'm feeling more and more that remoting is not going to be the answer you're
> looking for.

It's much faster on the server.
Lately, I've been noticing a significant difference between connecting via HTTP (much slower) and connecting directly (faster, but still noticably slower than the server).

Here's something interesting: The query uses a range into a date dimension. On the remote client,
If I run the query once via HTTP, then I change the date range and run the query again, it's almost instantaneous the second time. On the other hand, if I run the same test via direct connection, the
query time is almost the same the first time or the second time.

>
> A question: when you say that your aggregates are simple, either sum or distinct count, does that mean
> you're using the DistinctCount MDX function in calculated members, or do you have measures who have > the aggregation type Distinct Count. If the former, can you tell me a bit more about what you're doing
> with these calculations? If the latter, have you made sure that each Distinct Count measure is in a cube
> on its own, with no other measures?

It is the latter, with each Distinct Count measure in its own cube, and the various cubes combined in a
Virtual cube. Yes, the Distinct Count cubes don't have any other measures.

> Do you have partitions in your cube?
Yes. In each of the 3 cubes, I have one partition for "historical data" and one for "current data". "Current data" is from the beginning of May, 2004. Historical data is from January 2003 - April 2004.

> Regarding Partition Manager, it's useful for understanding the levels at which each aggregation is built
> at - the Analysis Services Performance Guide, which I think you've read, explains this very well. It would
> be a good idea to look at your old server (the one without the performance problems) and the new one
> and check to see that the same aggregations exist on both.

The aggregations do look substantially the same. I modified them to be exactly the same and didn't see any performance difference.
One thing I noticed is that one of the dimensions doesn't appear at all in the Partition Manager. Specifically, the axis that I think would benefit the most from aggregation (also the largest axis) doesn't show up.

>Additionally, while you run your test queries you should have a look at the Query Number, DSN Requested
>and the DSN Used counters in Perfmon on your server. Query Number will tell you have many sub-
>queries each query is being broken up into - possibly hundreds or thousands for a single query. DSN
>Requested and DSN Used are lists of numbers giving the ordinal of the level on each dimension that each
>subquery is requesting data from. To explain this a bit further, imagine you had a cube with two
>dimensions apart from Measures - Time, with three levels (Year, Quarter, Month) and Product, with four
>levels (All, Category, SubCategory, Product Name), and which appear in that order in the treeview in the
>Cube Editor. You run a query, and it results in one subquery on the server, and DSN Requested returns
>the value 21, meaning that it requested data at level 2 from Time (ie Quarter) and level 1 from Product
>(ie All). DSN Used, however, returns the value 33 - which means that the data itself was read from an
>aggregation built at the Month/SubCategory levels, and the values requested had to be aggregated from
>here. It follows if you had built an aggregation at Quarter/All Product then your query would have been
>directly answered from that, and DSN Used would have returned 21 too; and of course, performance
>would have been better. What do you see happening on your server?

On my server, I'm seeing query number increment by about 15 per query, with DSN Reqested = 511122
and DNS Used = 546223

Is there some way to understand which level maps to which digit in the DSN Requested/Used perfmon?

-- Jonathan

>
> "Jonathan Levine" wrote:
>
> > Hi, Chris. Thanks for your reply.
> >
> > "Chris Webb" wrote:
> > > Hi Jonathan,
> > >
> > > "Jonathan Levine" wrote:
> > >
> > > > Greetings,
> > > >
> > > > I'm connecting via HTTP to an Analysis server. Whether or not I set "Execution Location=3;Default Isolation Mode=1" in my Connection String, I don't notice any change in performance. If I monitor Web Service:Bytes Sent/sec, it seems that my server (which doesn't host any web pages other than msolap.asp) is pumping out tons of data, regardless of the Execution Location setting. So I suspect that my client is still performing most of the analysis.
> > > First thing to check, if you can, is whether you notice any difference when you connect not using HTTP. Some connection string properties get stripped away when you use an HTTP connection, and I'm sure other complicating factors come into play too.
> > >
> >
> > It's difficult to tell for sure. I've been running similar queries using the MDX sample app in 4 configurations:
> > - No HTTP, no Execution Location or Isolation Level
> > - No HTTP, Execution Location=3;Default Isolation Level=1
> > - HTTP, no execution Location or Isolation Level
> > - HTTP, Execution Location=3; Default Isolation Level=1
> >
> > The query times seem similar in all 4 cases (that is, the first time a query is executed, it takes about a minute to return the results and subsequent queries take much less time).
> >
> > The working set of each process seems similar, too: about 13MB when it starts up, then about 75-80 MB after the query is executed. Typical results are about 36 cells -- 60 MB strikes me as a lot of memory for 36 cells if everything is executed on the server.
> >
> > > Once that's out of the way, there are some other scenarios where you can't remote a query to the server apart from when the locales on the two machines are different:
> > > - When the version of the server components is older than the version of the client components. Basically, make sure you have SP3a installed on your server and your client.
> >
> > HKLM\Software\Microsoft\OLAP Server\CurrentVersion\CSD Version is "Service Pack 3" on both the client and server.
> >
> > > - When you have ROLAP dimensions in your cube. Since the occasions when ROLAP dimensions are a valid design choice are few and far between, then if you have them you should consider getting rid of them.
> >
> > I don't have any ROLAP dimensions. The cubes themselves are also MOLAP.
> >
> > > - When you use certain other, obscure connection string properties and functionality such as Default MDX Visual Mode and session cubes. You're unlikely to be using these unless your client is Excel, in which case try using a different client and see if there's a difference.
> >
> > So far, I've been connecting with the MDX sample and with VB.NET using ADO (not ADOMD).
> >
> > > - When you have calculated members that return strings, although this may well have been fixed in a hotfix by now.
> >
> > Calculated Members return Integer or Big Integer
> >
> > > - When you use certain other functions in calculated members, such as Lookupcube or CreatePropertySet, or you use UDFs in your calculated members.
> >
> > My aggregates are pretty simple. Sum and DistinctCount.
> >
> > > That said, even when you can get Execution Location=3; Default Isolation Mode=1 to work, I find it's rarely the answer to performance problems. In fact, in some cases it can make performance worse. If you're having problems in this area (and from your previous posts, I think you are) then you might be better off looking elsewhere, such as double checking the actual aggregations that have been built on your new machine are the same as the ones on your old machine, using the Partition Manager tool; checking to see if the slices on your partitions are correctly set etc.
> >
> > I've been investigating a little with Partition Manager, and you may ultimately be right about this... would love your feedback on things to check. However, given the Performance monitor stats on the amount of data sent out by the web server, I am pretty sure that the time it takes to pump data between the machines is at least one issue.
> >
> > Regards,
> >
> > Jonathan
> >

Relevant Pages

  • Re: Performance Benchmarks?
    ... Are attribute relationships defined properly on the dimensions? ... If you run Profiler against the server, how long is spent in "Query ... I have created aggregations (Partitions tab in cube design). ...
    (microsoft.public.sqlserver.olap)
  • Re: Performance Benchmarks?
    ... adding memory on the server can help you; more data can be cached on the ... Here is my test query. ... )} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ... I have created aggregations (Partitions tab in cube design). ...
    (microsoft.public.sqlserver.olap)
  • Re: AS 2000 Processor busy with no query or processing activity
    ... the other place to find this option is to edit the cube himself. ... a lazy aggregation option will evaluate the aggregations of a partition ... but during this background aggregation the server is slower. ... According to Performance Monitor "Analysis Server:Connection" there ...
    (microsoft.public.sqlserver.olap)
  • Re: CPU and MDX
    ... specially if you have changed the cube structure after an initial aggregation. ... also try to preload the cache by executing the query a first time. ... > be able to use all my CPU. ...
    (microsoft.public.sqlserver.olap)
  • Re: Sudden drop in speed after adding some records. Why?
    ... setup the log query interval to 1 in your servre properies to log every ... play with the cube, and see the results with the usage analysis. ... server process time takes only 5 seconds, ... Try to play with the client connection string to insure you use server side ...
    (microsoft.public.sqlserver.olap)
Loading