Administering SAS OLAP Servers

Tuning SAS OLAP Servers with Advanced Server Options

To display the advanced server options for a SAS OLAP Server, follow these steps:

Within SAS Management Console, expand the nodes under the Server Manager until you can see your SAS OLAP Server. The server components represent the actual machines that the SAS OLAP Servers are running on.
Right-click the server and select Properties.
In the Properties window, select theOptions tab.
On the Options tab, select Advanced Options.

In the Advanced Options window, tune your servers on the Performance, Cache, and Query Thread Pool tabs. Use the other tabs (Debug Query, Debug Server, and Journals) only under the direction of SAS technical support, outside of the production environment, to generate detailed log information. The logging process severely limits server performance.

Tune the Cube Cache

The cube cache stores an in-memory copy of the cube's metadata. The metadata describes the calculated members and named sets that are used to parse the MDX query syntax. The metadata does not include any disk-resident aggregations.

As the server processes a query, the server first checks the cube cache to determine whether the cube metadata is in memory. If the cube metadata is in memory, the server uses the cached metadata. If the cube metadata is not in memory, the server loads the metadata from the SAS Metadata Server.

Cubes are added to the cache as they are queried. Cubes remain in the cache until the OLAP server stops, or until the cache reaches its maximum number of cubes. After the limit it reached, new queries on new cubes cause the new cube's metadata to replace the metadata of the oldest cube in cache (first in, first out).

The cube cache is implemented as least recently used. As the cache becomes full, cubes are removed based on usage.

The default limit on the number of cubes in the cache is 20. To change this value, use the field Maximum number of cubes in cache in the Cache tab of the Advanced Options window. Restart your OLAP server to activate the new value.

Tune the Subquery Caches

The Cache tab in the Advanced Options window allows 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.

Tune the Query Thread Pool

The query thread pool is used to efficiently assign threads to query requests that are received by a SAS OLAP Server. As threads are assigned to queries, additional threads are allocated, up to the specified maximum number of threads. If the number of query requests and active queries exceeds the maximum number of threads, requests are queued and assigned to threads as threads are reclaimed.

You can set the following parameters of the query thread pool on the Query Thread Pool tab of the Advanced Options window:

minimum number of threads in the pool. The recommended value is 1.
maximum number of threads in the pool. The recommended value is twice the number of available CPUs on the host or lower.
threshold number of query requests that are required before the requests are assigned to threads.
time-out for thread reclamation.
thread stack size.

As with other server option changes, you need to restart your OLAP server to activate your new values.

Set Values for Flattened Row in the Server Tab of the Advanced Options Window

The Server tab of the Advanced Options window for SAS OLAP Servers contains two options for flattened results sets: Maximum number of flattened row and Maximum memory size for flattened rowset. Increase the size of these options if queries fail with a "flattened rowset size limit reached" error.

Most applications get their query results as multidimensional result sets. However, there are two exceptions, drill-through queries and SQL passthru queries:

Drill-through queries	Drill-through queries are not really multidimensional queries; they are two-dimensional queries against a relational table. As such, they can be requested as flattened result sets only.
SQL passthru queries	When using SQL to query a cube, the server always returns the data as flattened result set. This is because SQL understands rows and columns only.

Set Values in the Performance Tab of the Advanced Options Window

The Performance tab has four options that enable you to configure access to MOLAP aggregations in multidimensional databases. Three other options set limits on queries.

Memory available for group by operations

sets a memory allocation for MOLAP group-by operations, which are used to pre-summarize data on-demand, during queries. If a cube is pre-summarizing during queries, increasing this memory allocation might improve performance.

If a group-by operation exceeds its memory allocation, the SAS OLAP Server pages memory to the disk path that is specified in the option Path to temporary working files.

Note that the group-by memory is not pre-allocated, so the actual memory allocation might be limited by existing allocations.

Number of threads to spawn

specifies the number of threads that can be created to access MOLAP aggregations during queries. The default value 0 is preferable on multiprocessor hosts. When the value is 0, the number of threads is programmatically set to a value from 1 to 8, based the number of processors (CPUs) on the host. Note that this value does not limit the number of threads that are used to process queries. Query threads are set on the Query Thread Pool tab.

Path to temporary working files

specifies the disk directory that received memory swaps during MOLAP group-by operations.

Maximum segment ratio

controls the subsetting of indexes when processing MOLAP WHERE expressions. The ratio compares the number of segments that apply to the expression to the number of segments that do not apply to the expression. When the ratio exceeds the specified maximum, a subset index is not created before the expression is applied to the segments.

Maximum number of tuples in a set

sets the maximum size of the result sets that are sent to clients in response to queries. The default value is 1 million tuples.

MDX query timeout

automatically closes stale queries. No default value is provided.

Optimize queries that use the NONEMPTY and CROSSJOIN function

compares performance with or without optimization. Deselect this option to compare performance without optimization.

Top of Page