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.
Enhancement FOM-963 was added beginning with the 18.104.22.168 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
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.