The
Cache tab
in the
Advanced Options window enables you
to set the maximum size of the subquery caches. You can also enable
and disable the caching of empty intermediate result sets. By default,
the maximum size of each subquery cache is 5 megabytes, and the caching
of empty intermediate result sets is disabled.
SAS OLAP Servers maintain
a unique subquery cache for each query for the duration of the processing
of that query. When a server is processing concurrent queries, server
memory contains multiple subquery caches.
During query processing,
the subquery caches grow in size. If the subquery caches reach a specified
maximum size, the contents of the caches are paged (swapped) into
temporary disk storage.
Memory is not pre-allocated
for subquery caches. Paging might therefore occur before the subquery
caches reach their maximum size.
Paging for the subquery
caches is indicated by disk writes by the SAS OLAP Server. This is
the only case where the server writes to disk, except for the logging
and debugging operations that take place outside of the production
environment. If you see disk input and output from the server, you
can do one of the following:
-
change the subquery cache settings
-
change the value of the MEMSIZE=
system option for the server
-
add physical memory to the host.
Smaller maximum sizes
of the subquery caches might limit performance improvements due to
memory-intensive paging to and from temporary disk storage, particularly
if the caches include empty intermediate result sets.
Larger maximum sizes
of the subquery caches, when combined with a large number of concurrent
queries, might occupy an inefficient percentage of available server
memory.
In many cases, the default
maximum cache size (5 megabytes) is a good choice. It is recommended
that the maximum subquery cache size not exceed 50 megabytes.
To set the maximum size
of the subquery caches, use the field
Memory size for
subquery cache.
To cache empty subquery
result sets, select the check box to show a check mark in the field
Cache
the empty subquery result sets. Generally, most of the
set processing in OLAP queries involves very sparse matrices. For
this reason, empty subquery result sets are not cached by default.
If you have plenty of available memory, or if your cube or your queries
are relatively dense, then caching empty subquery result sets might
improve performance.