November 9, 2011 at 4:37 pm
We have a single server with Sql 2008 installed. We have trimmed its memeory to free up RAM for the OS. Now they want to install sql 2008 Analysis Servicec on the same box.
Can I "trim" the Analysis service memory usage also? If so, how?
Barkingdog
November 9, 2011 at 11:38 pm
Barkingdog (11/9/2011)
We have a single server with Sql 2008 installed. We have trimmed its memeory to free up RAM for the OS. Now they want to install sql 2008 Analysis Servicec on the same box.Can I "trim" the Analysis service memory usage also? If so, how?
Barkingdog
Memory
SSAS has a special memory “cleaner” background thread that constantly determines if it needs to clean up memory. The cleaner looks at the amount of memory used. The following basic processes are used by the cleaner to control amount of physical memory used by Analysis Server:
If the memory used is above the value set in the TotalMemoryLimit property, the cleaner cleans up to this value.
If the memory used is under the value set in the LowMemoryLimit property, the cleaner does nothing.
If the memory used is between the values set in the LowMemoryLimit and the TotalMemoryLimit properties, the cleaner cleans memory on the need-to-use basis.
If the value specified in any of these properties is between 0 and 100, the value is treated by SSAS as a percentage of total physical memory. If the value specified is greater than 100, the value is treated by SSAS as an absolute memory value (in bytes). Note that when Analysis Server is processing, if it requires additional memory above the value specified in TotalMemoryLimit, it will try to reserve that amount, regardless of the TotalMemoryLimit value.
Process Memory
Process memory is an area of memory that is reserved for processing dimensions, cubes, and partitions in SSAS. If you are going to change any of the memory settings under the Process node, it is important to understand how the processing works before doing so.
Note Processing a partition or dimension could result in more that one job created, in which case memory is calculated per job, and not per processing command.
For an SSAS job, Analysis Server calculates the memory required for processing and asks the memory governor (the global component keeping track of memory usage by Analysis Server) for that amount of memory. Using BufferMemoryLimit, you can limit the amount of memory available for a processing job, which allows more processing jobs to run in parallel. Setting this property to a smaller value could cause Analysis Server to start swapping data to disk, which would effectively slow down the server.
If the governor cannot give at least the minimum amount of memory required, an error will be returned (assuming the MemoryLimitErrorEnabled server property value is True).
SQL Server 2008 Analysis Services (SSAS) Memory Properties
http://msdn.microsoft.com/en-us/library/ms174514(v=SQL.100).aspx
November 9, 2011 at 11:47 pm
Dev,
Thanks for the great answer. I have never used Analysis Services and had no idea how to answer this question.
Barkingdog
November 9, 2011 at 11:57 pm
Most Welcome. 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply