-
Title
How to increase the max batch text size in SQL PI -
Description
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. -
Cause
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.
-
Resolution
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.
To change the batch text size for one or more SQL Server or Oracle agents- Navigate to the Global View databases dashboard
- Select the checkboxes for one or more SQL Server or Oracle database agents (changing SQL Server or Oracle agents must be done separately)
- Select Settings then Administration
- Select SQL Performance Investigator
- Select the checkbox for one or more of the monitored SQL PI agents
- Click the Settings button
- In the popup, change the Batch Text Size value (the default is 16,300 characters)
- Click the Set button
- Navigate to the Global View databases dashboard
-
Additional Information
Changing the batch text size for database agent adds more performance strain on the Foglight FMS. Quest does not recommend increasing the batch text size for numerous database agents, and to only change the setting for those agents encountering the issue.
After making the change, 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.."
Please refer to KB 4309484 to remove the SQL Text is out of Batch Size messages from the historically collected PI data.