No link available to generate an execution plan in SQL PI for SQL Server (4370453)

No link available to generate an execution plan in SQL PI for SQL Server

For some SQL statements in SQL PI for SQL Server there are no execution plans found.

For most statements there is a link to “generate the execution plan”, but there are some SQL statements for which no link appears so the user is unable to generate the execution plan.

The full SQL text is missing for the statements missing links. 

Workaround

Increase the following two hidden Agent Status Property values using the two attached scripts by running them in the Script Console. Users must be logged into Supportlink to download the scripts (script_modify_BatchTextLimit_v4.groovy and script_modify_PlanTextLimit_v4.groovy). Details on using the Script Console can be found in Knowledgebase article 4309796 .


Property Values
engine.olap.resolverBatchTextLimit - Size limit for retrieved batch text (characters)
engine.olap.resolverPlanTextLimit - Size limit for retrieved plan text (characters)

Knowledgebase article 4301141 provides an in-product User Interface means to increase the batch text size as an alternative to running the script_modify_BatchTextLimit_v4.groovy script in the script console.
 

Script Usage: input one or more agentNames to update. Alternatively use “all” to apply to all sqlserver agents

Configurable options:

• BatchTextLimit from 16399 → 25000
• PlanTextLimit from 400000 → 500000

- - -DISCLAIMER - - -

This script can negatively effect the Foglight Agent Manager and monitored instance(s) performance. It is advised that customers monitor the performance (CPU, Memory, etc...). The settings in this script may have to be increased beyond the suggested amount if there are still having issues with the updated settings. Adjusted these settings upward performance impact this MAY have in a Foglight environment.

 

Status

Enhancement Request FGSS-I-48 has been logged to make the hidden resolverBatchTextLimit and resolverPlanTextLimit properties visible in Agents Status Properties of the SQL Server agent. This will be considered by Product Management for a future release of the SQL Server cartridge.

 

FGSS-I-148

script_modify_PlanTextLimit_v4
script_modify_BatchTextLimit_v4

This script is an Internal Knowledgebase article that has been referenced in Knowledgebase article 4309842 and is available to customers on request due to the potential performance effects on the FglAM and monitored hosts when increasing the batch and plan text sizes to large values among many database agents.