furmangg


I don't have any hard numbers to share, but in the tests I've done, restarting the SSAS service nightly appears to improve query performance. Here's the reason I say that:

1. If I leave the SSAS service running for a number of days, timing an expensive MDX query will show it being slightly slower (say 4%) than immediately after I restart the service and run the same query. (In both the before and after test, I'm running the query several times in a row so that it can do all the caching it wants.)

2. If I leave SSAS service running for a long time with out restart (say, a month or two), it slowly consumes more and more memory even though we're doing a full process of the cube every night. (I would have figured that doing a full process would have invalidated everything in the cache, but apparently not.)

So if I had to guess, the more stuff that's cached in memory, the slower accessing any one bit of the cache gets. Therefore, restarting SSAS nightly helps this situation by clearing old/invalid stuff out of memory. Thus queries run a little faster.

Has anyone else seen anything similar Can anyone on the product team shed any light on the underlying architecture and whether they think this could be a legitimate concern





Re: restarting SSAS nightly improves query performance

Ashok Ojha


That's true. We do it for last 3 years in AS 2000 and I guess it's more true for AS 2005 because it's all server side. Also helps killing 'Runway queries'

-Ashok






Re: restarting SSAS nightly improves query performance

Dave Balsillie

This may be impacted by which service pack you're running. I'd recommend repeating your tests with SP2. I haven't measured the specific behaviour, but certain run away tasks and other errors have been corrected by restarting the service. At present (for a number of different reasons), we've been restarting MSAS on average every 2 weeks.

I'd suggest running the MSAS memory and cache counters for a couple of weeks if you can. How much memory do you have on the server How big is the cube







Re: restarting SSAS nightly improves query performance

Thomas Ivarsson

Hello! Do you use any dynamic security like an UDF or a security fact & dimension table instead of the standard role based security

Dimensions are not loaded directly into memory anymore, like in AS2000, so that should not be a problem.

I have heard about issues regarding SSAS2005 and dynamic security.

Regards

Thomas Ivarsson





Re: restarting SSAS nightly improves query performance

furmangg

The little testing I've done on this issue has all been against SP1. We're getting SP2 installed this week, so I'll report back if that changes my story at all.

We don't have dynamic security. But we do have dimension security using MDX expressions. (The expressions just don't use the Username function, so that's why I say it's not dynamic security.)

The cube is very calculation heavy, but the footprint on disk is fairly small (under 500MB).

The production box is an Itanium with 16GB memory which is well over twice what SSAS was consuming even when we left it running for several months without restart. So I don't think it's caused by low memory.

Thanks for chiming in. Looking forward to more confirmation (or non-confirmation).






Re: restarting SSAS nightly improves query performance

Mosha Pasumansky

Did you try to run the very same MDX query right after the full processing of the cube Do you see it at its fastest on the first day, and then gradually degrading 4% as time passes by Or is it as fast immediatelly after full process as it was on the first day




Re: restarting SSAS nightly improves query performance

furmangg

Mosha Pasumansky wrote:
Did you try to run the very same MDX query right after the full processing of the cube Do you see it at its fastest on the first day, and then gradually degrading 4% as time passes by Or is it as fast immediatelly after full process as it was on the first day

Mosha, I'm able to fairly consistently reproduce a 4% performance improvement after restarting SSAS with SP2 installed on a 32-bit box. By bouncing around some reports on the cube in question in addition to bouncing around the Project REAL cube, I can fairly easily get SSAS to consume about 1.2 GB of memory (which is about 3x of what it consumes after I restart and run the query I'm performance testing). So I feel pretty comfortable stating that 4% number.

On several 32-bit boxes under SP1 left running for several weeks, I was able to see a 10-17% perf improvement by restarting... but I'm not as comfortable sharing those numbers as they are difficult to reproduce without waiting a week. The total memory usage was at or under 1.2 GB, so I can't exactly explain why it would be more than 4%.

On the production 64-bit box, SSAS did consume 8GB of memory after months without restarting. After restarting SSAS, I may have even seen a 25% performance improvement... but I wasn't really looking for it back then so I don't really feel comfortable sharing that number... and we've begun restarting SSAS service weekly on production since then, so I'm not sure I'll be able to repro that number.

At this point, I can't explain how SSAS got to the point where it was consuming 8GB of memory on production. It could be that querying and a daily full process over several months gradually causes it to consume more and more memory. Or it could be that a stupid user wrote a bad ad-hoc query. I'll post back if I get any more info.

Doing a full process doesn't appear to slow performance as much as when SSAS is consuming a lot of memory due to lots of data being cached.

Mosha, I got a notification that you had posted the following, but somehow it doesn't appear in the thread:

Mosha Pasumansky wrote:
Is the following correct (based on the previous posts):

1. The difference in performance is 4%

2. The memory consumption at peak is about 8 GBs after several months

If this is true, then one explanation could be that since there is plenty of memory, the cache can grow without limits, adding more and more elements. At some point the cost of searching inside the cache (even taking into account that AS uses very sophisticated multidimensional indexing data structures to look in the cache) becomes a factor. 4% to search inside huge cache doesn't sound unreasonable.

Of course, I could be completely off, but this would be my guess.

That was precisely what I was thinking... that the more memory used, the slower it is to determine whether something's cached or not.

In my opinion, a best practice would be to restart SSAS at least weekly. And you should also test carefully whether any cache warming you're doing is helping you more than hurting you. (We're not doing any cache warming, but there have been some recent blog entries on the topic, so I thought I would mention it in this context.)