Re: Analysis Services running out of memory periodically
- From: "Dave Wickert [MSFT]" <dwickert@xxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 22 Jul 2005 18:40:11 -0700
An excellent summary and you touched all of the right things to look at!
--
Dave Wickert [MSFT]
dwickert@xxxxxxxxxxxxxxxxxxxx
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"djl" <UseLinkToEmail@xxxxxxxxxxxx> wrote in message
news:4_842319_f5671f89c66df7966b1d7387bfb06cc5@xxxxxxxxxxxxxxx
> "" wrote:
> > We are running SQL Server 2000 (with SP3) on Windows 2000
> > Advanced
> > Server. The machine is a Dell Optiplex with Intel Xeon at
> > 1.6GHz. The
> > server has 4GB memory.
> >
> > We have 2 main OLAP databases that we refresh at the end of
> > the ETL
> > run. It is an automated process that gets fired through an
> > external
> > scheduler.
> >
> > About a year back, we used to see 'out of memory' errors about
> > once in
> > a week. Of late though, these errors have become much more
> > frequent and
> > it has come to a point where the databases cannot be refreshed
> > twice in
> > a row.
> >
> > Could there be a memory leak in AS 2000? Is there a way to
> > troubleshoot
> > that?
> >
> > Just fyi I have read some of the related posts, and I should
> > mention
> > that in both the databases, there is at least one dimension
> > which is
> > about 100K rows. The fact table itself in each of the
> > databases is
> > about 1 million+ rows.
> >
> > Any help in troubleshooting this problem will be greatly
> > appreciated.
>
> We had similar problems recently and they've now been resolved.
>
> Firstly, AS loads all dimensions from all AS databases into memory
> when the services starts. In our case, we had 10+ databases and there
> were a lot of redundant dimensions, where we'd "archived" and
> "restored" the databases to provide the users with snapshots at a
> given point in time. We've now removed all redundant dimensions.
> Also, we've managed to be able to save these archive databases, so
> that they can be restored later.
>
> Secondly, AS can only access 2Gb of this memory by default. To enable
> AS to use more (only up to 3Gb), you can add a /3Gb switch to the
> boot.ini file (see microsoft tech for full details). This enables AS
> to use more than 2 Gb, but you should also amend the high memory limit
> value (either within AS or the registry - again check microsoft for
> details). The high memory limit is recommended to be no higher than
> 2.7 Gb as some memory is needed for a "cleaner thread".
>
> We've got the /3Gb switch set and the high memory limit set at
> approximately 2.5Gb. You might need to be a bit careful if you change
> your values as you've only got 4 for the whole server and SQL will
> need a fair amount (we have got 6 Gb of memory).
>
> Another point is that we were running with 4Gb for a while until we
> realised that we actually had 6 installed although windows told us we
> only had 4. This was resolved by adding another switch to the boot.ini
> file - /PAE. For some reason windows would only recognise 4 Gb
> without this switch.
>
> The AS process was running at 1.7Gb previously, now we're down to 0.6
> Gb and we've had no more memory problems.
>
> Hope this helps with your problem.
>
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/OLAP-Analysis-Services-running-memory-periodically-ftopict241100.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=842319
.
- References:
- Analysis Services running out of memory periodically
- From: rpmfantasy
- Re: Analysis Services running out of memory periodically
- From: djl
- Analysis Services running out of memory periodically
- Prev by Date: Re: How to normalize data cube?
- Next by Date: RE: rank mdx
- Previous by thread: Re: Analysis Services running out of memory periodically
- Next by thread: Cube data incorrect when using more than 20 aggregations
- Index(es):
Relevant Pages
|
