Database Reference
In-Depth Information
As we saw in previous sections, we can try to control the memory usage of Analysis
Services and SQL Server on servers that host both services. However, our suggestion
is to make changes only if it is really necessary. SQL Server has a default setting
that allocates memory if no other processes are claiming it. When Analysis Services
is running and requests memory, SQL Server might release some of the memory
it has previously allocated because it can detect when a system starts pagination.
This is not true for Analysis Services. Memory allocated by Analysis Services up to
the
LowMemoryLimit
threshold is never released. However, it could be paginated.
Sometimes, it is better to page a section of the Analysis Services cache than drop
it completely and have to recalculate the values it contains for future queries.
Therefore, even if Analysis Services allocated memory and SQL Server pushed some
of this memory out to a paging file, this is not a bad thing in itself. Only if paging
activity (measured at the operating system level) is continuous, for example, because
both Analysis Services and SQL Server are used intensively at the same time, then
we might want to try to limit the memory used by one or both services. Last of all,
remember that any attempt to optimize memory settings should be tested properly:
we should compare system performance before and after making any changes,
measuring the impact on things such as query response time, and not only looking at
how they affect performance counter values.
Monitoring processing performance
Processing Analysis Services dimensions and partitions is a resource-intensive
operation. It should use all of the resources—CPU, memory, and disk I/O—available
to Analysis Services. For this reason, it is important to be able to understand the impact
of processing operations on the operating system, as we saw earlier on in this chapter.
When a database goes into production, Analysis Services processing usually takes
place on a rigid schedule. We should monitor processing for the following two reasons:
•
Optimize processing times
: Usually we want to reduce the time required to
process partitions and dimensions.
•
Check for any increase in the processing window over time
: The amount of
time processing takes could increase over time, especially if we are running
a full process on our database. We want to ensure that the amount of time
processing takes won't exceed our assigned processing timeslot. To do that,
we need to log information about the start and finish time of each processing
operation, as well as details on the number of rows processed too, if possible.
Analysis Services doesn't provide a ready-to-use logging system for processing
operations. However, there are a number of ways to gather this data and make it
available for analysis.
Search WWH ::
Custom Search