Question: Should SQL and AS be on the same box and how do you limit the memory used by AS?
How you design the cubes, what/when activity occurs, what your expectations are, these all affect the answer.
Short answer for best performance:
For each environment (dev, test, QA, prod, whatever you have):
· Use 64 bit and SQL Enterprise Edition for all AS servers
· Apply SP2 plus the latest CU for AS!!!!! This is essential!
· SQL on one box, AS on at least one other box (might want one for staging and another for production)
· Add as much memory as the budget will bear, make sure server can be expanded to add more
· Get the fastest disk you can (read and write)
Details:
AS and SQL will compete heavily for memory, they both use a lot of it. Think of AS like any other application running with SQL Server. AS will issue some queries against SQL Server, usually just during processing (for those people who use MOLAP or HOLAP some queries against AS turn around and hit SQL Server, but most seem to use ROLAP so all queries just hit AS). Processing can happen on various intervals, anything from on demand (proactive caching) to daily/weekly/monthly. Some processing will run in a few minutes, some will take days. AS takes the data being returned and does some IO and memory intensive operations, sometimes at the same time it is issuing more SQL queries. Some of those queries may be very resource intensive on the SQL side. How intensive depends on your design and configuration. AS can use a lot of CPU at times as well, especially if you have things like "Distinct Count" measures. If I had to make a blanket recommendation I would say to put SQL and AS on
separate boxes as long as they are on a very fast network. It may not always be necessary but I bet it fits the 80/20 rule. Of course, you may have multiple data sources and some of them may not even run on Windows. You may have Oracle on Unix or DB2 on the mainframe as AS data sources for example. And you may need multiple AS instances.
You will want to review the "Tuning Server Resources" section of the Perf Guide http://www.microsoft.com/technet/prodtechnol/sql/2005/ssas2005perfguide.mspx.
You can set TotalMemoryLimit and LowMemoryLimit to somewhat control the amount of memory AS uses, but they are not hard limits. It is very possible for AS to use more memory than specified in TotalMemoryLimit. SQL is designed to "play well with others", AS is much more limited inthat regard.
This deployment guide from BOL may help:
http://technet.microsoft.com/en-us/library/ms174869.aspx
· If one or more relational databases provide data to an Analysis Services database, you can move these databases to a separate computer. Before you move the databases, consider the network speed and bandwidth that exist between the Analysis Services database and its underlying databases. If the network is slow or congested, moving the underlying databases to a separate computer will cause affect processing performance.
· If processing affects query performance, but you can't process during times of reduced query load, consider moving your processing tasks to a staging server and then performing an online synchronization of the production server and the staging server. For more information, see Synchronizing Analysis Services Databases. You can also distribute processing across multiple instances of Analysis Services by using remote partitions. Processing remote partitions uses the processor and memory resources on the remote server, instead of the resources on the local computer. For information on remote partitions management, see Managing Analysis Services Partitions.
· If query performance is poor but you cannot increase the processor and memory resources on the local server, consider deploying an Analysis Services project onto two or more production servers. Then you can use Network Load Balancing (NLB) to combine the servers into a single cluster. In an NLB cluster, queries are automatically distributed across all the servers in the NLB cluster. For more information, see Clustering Services under Technology Centers on the Microsoft Windows Server 2003 Web site.
If you want an ideal memory scenario for AS:
Have twice the memory as the amount of data on disk plus some for the OS. Example: Assume your dimensions + cubes add up to 5GB. You would want 12GB on the box: 5GB to load all data in cache, 5GB for copy during full processing, and 2GB for overhead such as AS metadata and the needs of the OS.