Essbase® Analytic Services Database Administrator's Guide | | Update Contents | Previous | Next | Print | ? | |
Information Map | |
You can improve Analytic Services performance with these basic techniques:
Use the following list to identify basic design issues that affect optimization outside this volume:
You can periodically reset a database, and then reload it. Even if you reload a database very often, the main database files, .pag
files, can grow unless you reset the database.
To reset a database, use either of the following methods:
Tool |
Topic |
Location |
---|---|---|
You can customize Analytic Services for maximum performance, using database settings at the database level in Administration Services, ESSCMD, or MaxL.
Note: If you are migrating a database, see the Essbase Analytic Services Installation Guide for information about the default settings after migration.
The following sections list performance settings and describe how to adjust them.
The following table describes database cache settings and lists the location of the settings in Administration Services, MaxL, and ESSCMD.
Setting |
More Information |
Location in Administration Services, MaxL, ESSCMD |
---|---|---|
For more information about these settings in Administration Services, see "Setting Database Properties" in Essbase Administration Services Online Help.
The following table describes database disk volume settings and lists the location of the settings in Administration Services, MaxL, and ESSCMD.
Setting |
More Information |
Location in Administration Services, MaxL, ESSCMD |
---|---|---|
For more information about these settings in Administration Services, see "Setting Disk Volumes" in Essbase Administration Services Online Help.
The following table describes database transaction control settings and lists the location of the settings in Administration Services, MaxL, and ESSCMD.
For more information about database transaction control settings in Administration Services, see "Setting Data Integrity Options" in Essbase Administration Services Online Help.
Setting |
More Information |
Location in Administration Services, MaxL, ESSCMD |
---|---|---|
The following table describes miscellaneous database settings and lists the location of the settings in Administration Services, MaxL, and ESSCMD.
Setting |
More Information |
Location in Administration Services, MaxL, ESSCMD |
---|---|---|
create or replace trigger, alter trigger display trigger, and drop trigger |
For more information about these settings in Administration Services, see "Setting Database Properties" in the Essbase Administration Services Online Help.
Fragmentation is unused disk space. Fragmentation is created when Analytic Services writes a data block to a new location on disk and leaves unused space in the former location of the data block. Block size increases because data from a data load or calculation is appended to the blocks; the blocks must therefore be written to the end of a data file.
The Analytic Services Kernel merges adjacent fragments into increasingly larger fragments so that unused space is more likely to be re-used.
In some cases, fragmentation cannot be reduced completely. Fragmentation is likely to occur with the following:
If you experience performance slow-downs, you can check to see if there is too much fragmentation of the database, and if there is, you can take steps to reduce the fragmentation:
You can measure fragmentation using the average clustering ratio or average fragmentation quotient statistic:
In ESSCMD, look at the Average Fragmentation Quotient that is returned when you execute GETDBSTATS command. Use this table to evaluate whether or not the level of fragmentation is likely to be causing performance problems:
Database Size |
Fragmentation Quotient Threshold |
---|---|
Any quotient above the high end of the range indicates that reducing fragmentation may help performance, with the following qualifications:
The average clustering ratio database statistic indicates the fragmentation level of the data (.pag
) files. The maximum value, 1, indicates no fragmentation.
To view the average clustering ratio for a database, use either of the following methods.
Tool |
Topic |
Location |
---|---|---|
You can prevent and remove fragmentation:
Essbase supports Microsoft 4 GB RAM Tuning (4GT). 4GT enables users with extremely large databases to take advantage of a larger address space to improve performance.
The total addressable limit of RAM on servers running Windows 2000 or Windows NT is 4 GB. By default, applications can access 2 GB, with the Windows kernel using the other 2 GB. For selected versions of Windows running on Intel architecture servers, Microsoft provides the 4GT feature. The 4GT feature increases the addressable limit for applications to 3 GB, reducing the potential RAM allocated to the Windows kernel from 2 GB to 1 GB.
Essbase currently supports the 4GT feature on computers that use Intel-based processors with more than 2 GB of physical RAM, and that have either of the following versions of Windows:
Enabling the Windows 4GT feature may benefit users if the Essbase installation has both of the following characteristics:
To configure the computer where Essbase is installed to enable the 4GT feature, modify the boot.ini file by adding /3GB to the startup line for each boot partition that is defined for a Windows version that supports 4GT; for example:
[boot loader] timeout=30 default=multi(0)disk(0)rdisk(0)partition(2)\WIN2KADV [operating systems] multi(0)disk(0)rdisk(0)partition(2)\WIN2KADV="Microsoft Windows 2000 Advanced Server" /3GB multi(0)disk(0)rdisk(0)partition(2)\WINNT="Windows NT Server Version 4.0" /3GB
Note:
This change to the boot.ini file is effective only if you are using Windows NT Server 4.0 Enterprise Edition or Windows 2000 Advanced Server. On standard Windows NT, although the /3GB flag relocates the kernel, applications are unable to access more than 2 GB.
On a dual boot system, to ensure that the boot loader supporting the 4GT feature is installed, be sure that the last operating system you install is a version that supports the 4GT feature.
Because the Windows kernel area is reduced, it is unlikely but conceivable that certain applications and workloads in certain environments may experience degraded performance. Testing your workload in your environment is recommended.
For additional information about the Microsoft Windows 4GT feature, see www.microsoft.com.
Using Database Settings to Customize for Maximum Performance provides general-purpose information and does not account for the wide variety of configuration possibilities. For more information about performance and server, application, or other settings, see these chapters:
![]() |