Configuring Memory for Analysis Services 1

[Andel]

We are starting to use Analisys Services and need some recommendations from you guys. SQL Server and Analysis services sit in one server. Is there any recommended settings that I need to be aware of? Like, SQL Memory, etc???

Here's the description of the server that I got: Hewlett Packard ProLiant DL580R G2; (4) Intel Xeon Processor MP 2.2GHz/2MB cache; 1GB Memory, 400 MHz FSB; Integrated Lights Out (iLO); Dual Power Supplies Available; 708GB total HD Capacity; 2x86000MB DB Capacity

Any help would be appreciated.

Andel
andel@barroga.net

http://www.iluko.com

 

[MDXer]

you memory allocation for analysis services depends on a number of things such as Number of cubes. number of dimensions, size of dimensions, size of cubes, expected number of users, security, and measure types.

Dimension data and cube meta data is read into memory upon start up of analysis services so the number and size of your dimensions are important. Security is a factor if you implement cell level or dimensional security as this duplicates the dimension into memory when a user fitting that security role access the cube, this dimension remains in memory until either the service or server is restarted.

Meausre types play a role in determining memory requirements. If you use the distinct count measure, the cube is processed in memory and if this cube and measure are not done in the proper way it can have severe impact or even cause processing failure. I have had cubes use in excess of gigs to process a distinct count cube.

Rule of thumb is give Analysis Services as much memory as you can afford up to 3GB.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Andel]

Thanks MDXer... so how would I allocate memory for Analysis Services? Is Analysis services share memory with SQL server?

Andel
andel@barroga.net

http://www.iluko.com

 

[MDXer]

No SQL and AS don't share memory. At this point they are 2 completely seperate apps using and retaining their own memory allocations.

As for how to Allocate the memory given the relativley small amount of memory you have on the machine I would probably leave it at ot's default settings. SQL, AS, and the OS all sharing 1GB of memory leaves little room to optimize memory usage. I would suggest putting as much memory as you can afford on the machine.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Andel]

Thanks MDXer. Is it advisable to separate SQL and AS in two separate server? In what situation would you consider separating them, and when to not separate them?



Andel
andel@barroga.net

http://www.iluko.com

 

[MDXer]

If the money is no issue route I would seperate AS and SQL onto seperate servers in every instance. The issue with doing this is SQL licenses You would be required to license both the AS and the SQL server. Since that is typically not a valid option I would make sure that there were enough available resources on the server to support both systems. Both SQL and AS are resource intense applications and don't like to share. If you have a 2 proc machine I would only allocate 1 proc to SQL, 2 gigs memory I would give 1 gig to SQL and 750mg to AS. Hardrives I would partition out the drives in a way that SQL and AS both had their own physical drive structures. I would further allocate an additional temp drive to AS to utilize during cube processes. But these are just starting points. If your AS instance isn't going to grow to large you might be perfectly fine performance wise.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck