I'm attempting to configure my Analysis Server instance to use all available
memory in my system. I have 6GB available and I would like the service to
use as much as possible. There doesn't seem to be much information on
configuring memory available in BOL so any advice appreciated.

Query performance for a relatively small database < 10GB seems very bad. I
have queries that frequently time out after 15 or more seconds. When I
examine the memory consumed by the service it is typically around 400mb.
What gives? I thought analysis services was supposed to be fast.

My box is a 4-way 700mhz zeon with 6gb of ram and the disk can handle
sustained 8k random reads at a rate of about 4000 I/O's per second according
to IOMeter.

AS uses the memory it uses. It is not like SQL that just goes out and grabs
everything and then slowly gives it back. AS starts out with just dimension
memory and then builds aggregate cache as it runs (executes more and more
queries). However, what it caches is aggregates; not complicated
sub-results. Thus if your cube uses some aggs; but then has large non-empty
crossjoins for it calculations, your queries will take a long time; but aggs
being used memory could be small.

It is aggregation which make AS, as you say, "fast". Without the proper
aggregation level, then slow queries might be reasonable and understandable.

Also, AS' computing model is different from SQL RDBMS (for SQL2K). The
client-side component PTS is thick and has maybe 70% of the server code.
Thus (depending on circumstances) query execution might be happening on the
client and the server is only supplying agg data. If this is true, then
again, having long query times without a lot of memory usage on the server
might be consistent.

Second, AS is not an AWE-aware application. Thus unless you turn on /3GB or
have a 64-bit server, then it is limited to 2GB of virtual address space.
Thus if this application is dedicated to AS you will never use all of those
6GB regardless of what you do.

Lots of technical material on how AS uses memory and how the interaction
between aggs and cache can be found in these two resources:
AS Operations Guide
http://www.microsoft.com/technet/pro.../anservog.mspx
AS Performance Guide
http://www.microsoft.com/technet/pro.../ansvcspg.mspx

--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Chris Stransky" <ChrisStransky (AT) discussions (DOT) microsoft.com> wrote

Thanks for responding to this post. I have reviewed the documents and they
were somewhat helpful for troubleshooting SQL 2005 AS. I have redesigned a
few dimensions and added additional hierarchies and I now have 6 partitions.
However, I still get frequenty query timeout errors. I've been searching for
anything that more or less gives me some idea as to what is a big cube and
what isn't a big cube ... thinking that could help me. I have not been
successful on that front ... so maybe someone can comment on my current cube.
Source structure is as follows:

80 million fact rows: tracking about 10 measures. no calculated measures
About 10 dimensions. Most dimensions probably have less than 200 members.
There are 3 dimensions that are SCD Type 2 and they have around 4,000
members. My cube is built using MOLAP storage with the aggregation
optimization level set to 35% for each partition.

So, the basic questions are ... is this a big cube? is it too big for AS?
I expect to be able to drag a 1,000 member dimension onto each axis of he
browser and get answers instead of timeouts ... is that reasonable? (e.g) I
want to be able to cross customer accounts with investment type ... and my
fact table basically contains all of the trade details. Is there a way to
set the browser timeout ... because I can't seem to find it.

Any feedback appreciated.


"Dave Wickert [MSFT]" wrote:

Just to orient you, the 80 million rows 'disapear' into the aggregation.
Most of the time taken in queries is a function of the (number of dimensions
X the levels of each dimension revealed at any given point) X the number of
measures.
ALSO As Dave pointed out also depending on how you have set thinks up
the PTS MAY be doing much of the work on the client machine rather than on
the server. You have not given us much info on how you have set up your
solution. You should compare queries run on the AS box against on your
client and see what the roll off is.
Doing: "I expect to be able to drag a 1,000 member dimension onto each
axis" will always be a costly operation and a 1000 X 1000 grid is going to
be of little practical use to anyone looking at the data. A general way you
can improve this type of query is to have more hierarical levels in your
dimensions. For Example a dimension of all the counties in the USA COULD
list all counties as a flat surface OR be broken up by Region and then State
and then county within State. You still get all you levels but the box will
give you more prompt results.

Hope this helps.


dlr

"Chris Stransky" <ChrisStransky (AT) discussions (DOT) microsoft.com> wrote

As a potential AS2K5 user, i'm confused by an apparent conflict of
information between the above statements of client-side execution and
the following statement from the ms article:

http://www.microsoft.com/technet/pro...e/dwsqlsy.mspx
---------------------------------------------------------------------------
Calculations are centralized on the server. Analysis Services 2005, by
contrast with Analysis Services 2000, performs all calculations on the
server. The advantages are significant:

· Clients have a zero footprint; the client-side cache is eliminated.

· Query performance for complex calculations is greatly improved.


The cost of these improvements is minor query performance degradation
for the very simplest queries, which in Analysis Services 2000 were
resolved from the client cache.
---------------------------------------------------------------------------

Can someone clarify?

My, my -- conflicting statements from Microsoft -- how unusual :-)

In this case, there are reasons for it. What you are seeing is an evolution
in processing power over time. Relative to CPU, memory and other
bottlenecks, 100MB Ethernet was a very fast medium. Back when OLAP was first
introduced with SQL 7 and SQL 2000, we had a very different distribution of
processing performance. A large server was a 4-way. Desktops were almost as
powerful as servers. Most OLAP applications were much smaller (remember that
pivot tables were only able to do 65,000 rows -- and that with AS it was a
big thing that we could do more than that). A 10GB system was huge; no one
had envisioned a 100GB or TB system. Smaller data sizes; with relatively
more powerful client machines said that the client was an excellent resource
and one that we should heavily use. Thus we did.

Now turn on your time travel machine and move 5-6 years into the future. In
today's world, data set sizes are HUGE. Networking is clearly a major
bottleneck of most system. On the current Project REAL system I am working
on right now
http://www.microsoft.com/sql/bi/Proj...L/default.mspx
we are looking at TB cubes; hundreds even thousands of users -- in that case
the best overall throughtput is to have server-side execution of queries
with sharing between users, with full use of server-side capabilities --
without networking becoming the bottleneck.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"RuiDC" <rui.dacosta (AT) ubs (DOT) com> wrote

As a potential AS2K5 user, i'm confused by an apparent conflict of
information between the above statements of client-side execution and
the following statement from the ms article:

http://www.microsoft.com/technet/pro...e/dwsqlsy.mspx
---------------------------------------------------------------------------
Calculations are centralized on the server. Analysis Services 2005, by
contrast with Analysis Services 2000, performs all calculations on the
server. The advantages are significant:

· Clients have a zero footprint; the client-side cache is eliminated.

· Query performance for complex calculations is greatly improved.


The cost of these improvements is minor query performance degradation
for the very simplest queries, which in Analysis Services 2000 were
resolved from the client cache.
---------------------------------------------------------------------------

Can someone clarify?

ok, so that attempts to justify the strategy for server-side only
execution - we've also been bitten by problems of PTS pulling down a
lot of data over the network (i haven't made the Cache Policy
connection property = 7 changes, because i still cannot find any MS
documentation or even answers to posts regarding what it does!) so
server-side execution can make some sense.

But even after your response above, i can't seem to see an answer to
the apparent conflict...
the MS statement about AS2K5 says:
Calculations are centralized on the server
which i (rightly or wrongly - please feel free to correct me) read as:
Server-side execution only

your statement about AS2K5 says:
PTS MAY be doing much of the work on the client machine rather
than on the server
which again i read as: sometimes execution is client-side

Have i misinterpreted the statements or where does AS2K5 query
execution take place?

R

Dennis made that statement. :-) What I said was:

"Also, AS' computing model is different from SQL RDBMS (for SQL2K). The
client-side component PTS is thick and has maybe 70% of the server code.
Thus (depending on circumstances) query execution might be happening on the
client and the server is only supplying agg data. If this is true, then
again, having long query times without a lot of memory usage on the server
might be consistent."

Notice that I said SQL2K. Your analysis was comparing SQL2K verses SQL2K5
with performance and memory utilization. I was just making that point that
depending on where query execution happens the amount of traffic moved
across the wire, memory consumption o(on both the server and the client),
and execution location may be very different between SQL2K and SQL2K5.. This
makes it difficult to directly compare SQL2K to SQL2K5 as a pure
apples-to-apples.

But you are right, from SQL2K5 itself, this does not apply because all
execution is done on the server.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"RuiDC" <rui.dacosta (AT) ubs (DOT) com> wrote

ok, thx for clearing that up