No execution plan details in SQL PI for a SQL Server instance (4309842)

Unable to generate or see execution plans for a SQL Server instance.

Sometimes messages similar to the following appear in the database agent log

Plan query length is over the limitation of [400000] for handle [0x0500220034210a7445fff23566500000001000000000000000000000000000000000000000000000000000000]

Most plans are not resolved because their length - in characters - is bigger than the 400,000 character limitation.

It is of no use to get truncated Plans (as opposed to getting truncated SQL text, which can still be useful to customers); so once the Plan Query length exceeds 400,000 characters, it will be discarded & not saved by PI.

RESOLUTION 1

When a user clicks on "Analyze Plan", if the plan is not in PI Repository, will PI engine connect to Monitored Instance on Demand and attempt to get the execution plan using the sql_handle & query_plan_hash.

There are several reasons why the execution plans may not be available in the SQL PI performance dashboard due to problems with the SQL Statement itself: 

1. SQL was not resolved
2. SQL is truncated when resolved because it is too long 
3. SQL is encrypted

For issues where the SQL was truncated because it is too long

Any issues where the "SQL text is out of the collected batch configured size" should be addressed first. This is described in KB 4301141.

Users can expect the ratio of cases of Successful retrieval of Execution Plans to improve that the parameter change is made to to increase the batch text size limit in these situations. Once the statement is retrieved successfully, it follows that the Execution Plan for those SQL statements will also be retrieved, provided they do not hit the limit of Plan Query Length exceeding the 400,000 character limit.

Increasing the Plan Query Length would have these implications:

(a) Increase in memory consumption - up to 5 MBs per engine.
(b) Each row inserted by DLP is also limited to 65535 bytes, so plan may be resolved but will fail still on insertion. Plan compression ratio is 1:12. This is how the 400,000 limitation is calculated.

If the row was discarded the following line will be shown in the database agent log file:
"Discard writing a row to table [XXX] due to DLP sizing limitation. Values: []

**** Knowledgebase article 4370453 provides scripts to increase the plan text size. Due to the potential performance effect on the Foglight Agent Manager and monitored hosts using very high settings, contact Support for a copy of this Knowledge base article.
 

RESOLUTION 2

A statement may not be collected after a new execution plan is created because PI will filter out statements with small workloads and will not submit them to the PI repository.

Note: There will always be some SQL statements that will not have actual execution plans in SQL PI.

The SQL PI mechanism of fetching Execution Plans works as follows:

• PI resolves only 5 plans in each cycle of 1 minute (out of 300 top unique combinations).
• The assumption is that over time, most plans would be retrieved but obviously this is not guaranteed.

To collect the specific statement the user can also try to 

1. Increase the workload of the statement to a one minute interval by executing the original test procedure many times during a one minute period.

2. Increase the number of records that are collected by PI each cycle. The default number is 300. This increase may impact performance as a trade-off. The following script can be used to change the number to 1000. Set the agent_name in the script and run this in the script console, then check if the statement can be collected in the statement dimension before the check the plan change tracking.

configService = server.get("ConfigService");
agentService = server.get("AgentService");

agent_name="";//e.g ISRVM702-X64DEV1

def modifyAgentPropertiesPrimary() {
	def primary = configService.getAgentInstancePrimaryAsp(agent.getAgentNamespace(), agent.getTypeId(), agent.getId());
primary.setValueByString("paecProcessUniqueTopsNumber", "1000");//300
	configService.saveConfig(primary);
paecProcessUniqueTopsNumber = "paecProcessUniqueTopsNumber is "+primary.getString("paecProcessUniqueTopsNumber");
}


def getAgentByName() {  
  agents = agentService.findByName(agent_name);

  if ((agents != null) && (agents.size() > 0)) {
    return agents.get(0);
  } else {
    return null;
  }
}


agent = getAgentByName();
if (agent != null){
	modifyAgentPropertiesPrimary();
} else  {
   return "Agent '" + agent_name + "' not found" ;	
}

return paecProcessUniqueTopsNumber;