-
Titel
How to increase SQL statement text size in SQL PI -
Beschreibung
Looking at SQL Statements within PI dashboards shows "SQL text is out of the collected batch configured size...." for some statements.SQL text of Statement or batch is cut and is not presented in full. -
Ursache
PI agent collects information about activity run within database instance. One piece of this information is the actual text of the SQL Statements and Batches (SQL Server only) executed. Retrieving the text from the monitored instance require memory and CPU resources at the agent side. The resource consumption is aligned with the amount of texts and their size.By default, PI is configured to collect a text of maximum 16300 characters. That means that any text that is larger than that will be cut off. For SQL Server the text is resolved at the batch level, and so the Batch will be cut. However, some statements within the batch may not be visible at all and shown as: SQL text is out of the collected batch configured size.
Increasing the limit for text size is possible. The trade off will be resource allocation - to retrieve more there may be a need to increase the memory and CPU allocated to the PI Agent Manager.
Note: Increasing the maximum size will only affect new SQL Statements and batches detected. It will have no affect on texts already collected.
-
Lösung
WORKAROUND
Execute the script below in order to increase SQL text size allowed by the SQL Server agent in SQL PI. Refer to KB 232352 for details on running a groovy script using the script console in the Foglight Management Server.
This script is for a single database agent due to the performance impact that increasing the text size variable can have when multiple database agents are changed.
- paecResolverBatchTextLimit is the script parameter controlling the text size
- agentName is the script parameter controlling the Agent name
Change the two parameters in the script for the required text size and Agent name and then execute the script via the Foglight Script Console.
agentService = server.get("AgentService");configService = server.get("ConfigService");agentName = ""; modifyAgentPropertiesPrimary(agentName);def getAgentByName(agentName) {agents = agentService.findByName(agentName);if ((agents != null) && (agents.size() > 0)) { return agents.get(0); } else { return null; }}def modifyAgentPropertiesPrimary(agentName) {agent = getAgentByName(agentName);if (agent == null) return;def namespace = agent.getAgentNamespace();def agentType = agent.getTypeId();def primaryAsp = configService.getAgentInstancePrimaryAsp(namespace, agentType, agent.getId());primaryAsp.setValueByString("paecResolverBatchTextLimit", "20000"); configService.saveConfig(primaryAsp);}return true;Note 1: Please put your agent name in line 3:
agentName = "";
Example:
agentName = "abc123-DefaultInstance";Note 2: For customers including a parenthesis character "(" or ")" in the agent name, please treat the agent name field as a regular expression. For example: agentName="abcd00001234.example.com.*";
DISCLAIMER
The information in the script provided is known to work successfully; however, it has not been officially tested by Quest Quality Control. If any of these instructions are changed and/or incorrectly used, intentionally or unintentionally, this solution becomes unsupported by Quest Support and Development.
Quest Support and Development recommend always making a backup of the current Foglight installation and database prior to execution of any scripts that may modify it.
STATUS
Enhancement FOM-963 was added beginning with the 5.9.7.20 and higher releases of the SQL Server and Oracle cartridges to provide the capability to increase the batch text size from within the Foglight SQL PI UI. Please review the following video for details on this functionality.
-
Weitere Informationen
After the user applying the script. Based on research, the user can give it about 60 minutes for the previous statements to flush out so the user can check it again. The "paecResolverBatchTextLimit" parameter is set based on the character count of the query that showed ""SQL text is out of the collected batch configured size.."