-
Titre
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.
-
Résolution
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.
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
Please review the following video for details on this functionality. - Navigate to the Global View databases dashboard
-
Informations supplémentaires
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.
How much additional RAM and CPU cores would be needed for the FMS machines if we increase this parameters.?
This cannot be calculated by Dev, it is unique to every environment... the number of agents, the amount of SQL run in the system (activity), how many of those SQL are very long, how many characters those statements are in length
What is the limit for this parameter?
There isn't a known limit. For users that have needed to collect and investigate a specific long SQL in a database they have set the value in the millions.
Will the size of the PI Repository will be impacted if the parameter is increased?
No, it shouldn't be. The actual SQL text of the batch is stored in the SQL Manager cache, The SQL Text is then retrieved using the SQL Handle which is stored in PI.