SYMPTOM:
Users observe high memory usage on Strategy Intelligence Server machines when the database that stores statistics data for the Intelligence Server experiences slow performance.
On some occasions, users may also see increased logging in StatisticsFailedSQLs.log. StatisticsFailedSQLs.log is a file that captures the Structured Query Language (SQL) statements that are issued against the statistics database when the Statistics Manager component within Strategy Intelligence Server fails to write to the statistics database. For more details about the functionality of StatisticsFailedSQLs.log, refer to the following Strategy Knowledge Base document:
KB8145: What is the 'StatisticsFailedSQLs.log' file and how is it used in Strategy Intelligence Server 8.1.x and 9.x?
CAUSE:
As actions take place in Strategy Intelligence Server that produce statistics, the statistics must be queued for writing, because many jobs can run concurrently and produce statistics at about the same time, but the SQL to write statistics into the statistics database executes one pass at a time. When the statistics database does not respond quickly, statistics data accumulate faster than they can be written. Statistics buffers exists in memory; if they grow larger, memory usage grows accordingly.
Session statistics are a common bottleneck. When a session closes, an UPDATE statement is issued to locate the row representing that session and populate the disconnection time column. As the session tables increase in size, this operation takes longer and can delay the insertion of other statistics.
ACTION:
To resolve this problem, administrators can take the following actions:
