In the Strategy Intelligence Server x users may observe that some database jobs result in a large amount of memory being used. Typically such jobs will trigger repeated sequences of MCM contract request succeeded messages for the MDb::Table and MDb::Connection sources as shown below:
Contract Request Succeeded: Source=MDb::MPConnection, Handle=62509, Size=15867904, ContractedTotal=18780160, AvailableTotal=30412573185
Contract Request Succeeded: Source=MDb::Table, Handle=63361, Size=15822848, ContractedTotal=18821120, AvailableTotal=30395737580
These jobs may also be identified using Enterprise manager reports and reporting on database jobs that return a large number of rows. If the number of columns returned by the query or the columns that are returned have large data widths, the total memory used to return the results from the database to the Intelligence Server can be substantial.
Issues with the existing governing settings:
The Strategy Intelligence Server provides some measure of governing against large database jobs through the project level governing settings for 'Maximum number of result rows' or 'Final Result rows', and this should be the primary means for administrators to restrict users from running reports with excessive rows. However, the report level VLDB Setting "Results Set Row Limit" allows users to override this setting at a report level (see KB3822 -- How to set the results set row limit for Strategy SQL Generation Engine 8.x).
In addition to governing the number of report rows, its possible for users to set the MCM 'Single Allocation Limit' to limit single large allocations. However in practice if this setting is set to a value of greater than 100 MB, it has no effect on database results returned by the query engine. This is because the M8MulPrc process that run the SQL against the database and retrieve the results chunk the data back to the Intelligence Server - Query Engine in chunks based on the MaxCrossProcessTableSize registry parameter (see KB17628 -- How to change the maximum database table chunking size used for data transfer between the database multiprocess component (M8MulPrc) and Strategy Intelligence Server 8.1.x and later ).
As a result of this chunking behavior the single largest memory allocation carried out by the Query Engine will be around 100MB even if the actual memory required for all the results is many times more than this. So any Single Allocation Limit of greater than 100MB will not impact the jobs.
Constraining the Intelligence Server maximum memory usage for single database results:
As described above, there is no straightforward and failsafe means of restricting the amount of memory used for database results within the Strategy Intelligence Server . An enhancement request has been logged with our Technology team to introduce a targeted governing setting to accomplish this in a future release.
It is also possible in the Strategy Intelligence Server 8.1.x and 9.x, to modify the MCM Single Allocation Limit and the MaxCrossProcessTableSize parameters in such a way that the Intelligence Server will deny an database results requiring memory over a specific amount. This is accomplished by changing the MaxCrossProcessTableSize to a value greater than the desired memory limit. The Single Allocation Limit is then set to the required limit. This change will force the the M8MulPrc process to hold all the results from the database before transferring them in a single chunk to the Intelligence Server. If the memory required for the results is greater than the Single Allocation Limit, the results will be denied by MCM.
For example, if the user would like to deny any requests larger than 600 MB, the single allocation limit can be set to 600MB, and the MaxCrossProcessTableSize set to about 640MB. The registry key for MaxCrossProcessTableSize can be changed as described in following Technical Document:
KB17628 -- How to change the maximum database table chunking size used for data transfer between the database multiprocess component (M8MulPrc) and Strategy Intelligence Server 8.1.x and later
NOTE:
Users should be aware that this change will apply at an Intelligence Server level across all projects, and equally to report and Intelligent Cube reports, so the threshold at which the jobs are denied must be carefully determined to reduce the impact of this setting.
Registry Modification WARNING
Modifying registry values incorrectly may cause serious, system-wide problems that may require the re-installation of Microsoft Windows NT. Any edit of the registry is done at the user`s own risk. Since these are user-initiated changes, they are not covered by any Strategy warranty. If using Microsoft Windows NT, the user should backup the registry and/or update an Emergency Repair Disk (ERD) prior to alterations.
