SQL Server Explain or Execution Plans are missing from SQL PI Analyze Plans page (4373224)

A SQL Server query has more Explain or Execution plans displayed in the SSMS (SQL Server Management Studio) Query Store list of Top Resource Consuming Queries than for the same time range in SQL PI's Analyze Plan functionality. Why is there a difference?

Top Resource Consuming Queries from the Query Store list in SSMS for the previous three months

SQL PI Analyze Plans list for the previous three months 

A generated plan is either missing or cannot be generated for SQL Server in SQL PI 

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 which are not resolved because their length exceeds the 400,000-character limitation. Truncated plans are not useful (unlike truncated SQL text, which can still be helpful to users). Therefore, once the plan query exceeds 400,000 characters, it will be discarded and not saved by PI.

Other reasons, including PI filtering of queries with small workloads, encrypted SQL, lack of an execution plan due to the nature of the SQL statement run are described in the Resolutions below.

Resolution 1

In the example above, several of the plans listed in the Query Store's Top Resource Consuming Queries are duplicates.

A circular icon indicates that the query has completed. A square icon represents a canceled query that still had a plan generated. A triangular icon signals an execution error.

This leaves nine distinct plans remaining: 1, 42, 134, 170, 241, 266, 267, 278, and 284.

Additionally, there are certain limitations in SQL PI related to explain plans:

• The number of resolved plans per minute is capped.
• SQL PI only collects plans for executed statements.
• Plans are saved to the long-term repository only if they reach the 15-minute granularity of the retention policy.
• Plans older than 90 days are automatically deleted by the system.

Resolution 2

When a user clicks on "Analyze Plan" and the execution plan is not found in the PI Repository, the PI engine will connect to the monitored instance on-demand to retrieve the execution plan using the sql_handle and query_plan_hash.

Several issues can prevent execution plans from appearing in the SQL PI performance dashboard. These issues are usually related to problems with the SQL statement, such as:

• The SQL not being resolved.
• The SQL being truncated due to length.
• The SQL being encrypted.

For cases where SQL is truncated due to length:

If the SQL is truncated because it exceeds the configured batch size limit, this should be addressed first. For further information, see KB 4301141.

Increasing the batch text size limit can improve the rate of successful execution plan retrieval. Once the SQL statement is successfully retrieved, the execution plan can also be fetched, as long as the plan does not exceed the 400,000-character limit.

Increasing the plan query length can help with plan retrieval but may have the following implications:

• Higher memory usage: Up to 5 MB per engine.
• Row size limit: Each row inserted by DLP is limited to 65,535 bytes. A plan may be resolved but fail to be inserted if it exceeds this size. The compression ratio for plans is 1:12, which is how the 400,000-character limit is determined.

If a row is discarded due to size limitations, the following log entry will appear in the database agent log:

"Discard writing a row to table [XXX] due to DLP sizing limitation. Values: []"

For instructions on increasing the plan text size, refer to KB 4370453. Contact support for a copy of this internal article, as setting very high values may negatively impact performance.

Resolution 3

Sometimes a new execution plan may not be collected because PI filters out statements with small workloads. As a result, statements with insufficient workload may not be submitted to the PI repository, meaning no execution plan will be available.

Important Note: There will always be some SQL statements that don't have execution plans in SQL PI. These might include;

• DDL statements (CREATE, ALTER, DROP, TRUNCATE, RENAME) which modify the structure of database objects like tables, indexes, or schemas, but they don't involve querying or manipulating data in the usual way, so they do not require an execution plan. 
• SET statement is used to assign values to variables, and DECLARE is used to define variables. These do not need an execution plan because they don't query or modify data.
• If the block contains only procedural logic like variable assignments, control flow statements (IF, WHILE, etc.), and no data retrieval or manipulation, SQL Server doesn’t need to generate an execution plan.
• The WAITFOR statement causes SQL Server to pause execution for a specified period, but it doesn't perform any data operations that require a query plan.
• ROLLBACK and COMMIT statements control the transaction's state but do not involve querying or modifying data, and therefore, no execution plan is needed. 
• RETURN statements is used to exit from a stored procedure or function and does not require a query plan. 
• If a stored procedure (EXEC) is called with logic that doesn’t perform any query operations (like only variable assignments or control-of-flow logic), it may not generate an execution plan. 
• When using SELECT INTO to create a new table without querying data from an existing table (e.g., with a constant value), an execution plan might not be generated since it's a simple creation operation.

How SQL PI Fetches Execution Plans:

PI resolves up to 5 plans per minute from the top 300 unique combinations. While most plans are retrieved over time, this is not guaranteed.

To increase the likelihood of collecting the execution plan for a specific statement, the user can:

1. Increase the workload: Execute the original test procedure multiple times within a one-minute interval.
2. Increase the number of records collected: By default, PI collects 300 records per cycle. This limit can be increased (e.g., to 1000), though doing so may impact performance.

Here’s a script to change the number of records. Ensure that you set the agent name to the correct value before running the script.

NOTE: Foglight Cloud customers should contact Support for assistance in running this script.

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;

Resolution 4

If an execution plan has not been retrieved yet but the user still needs to see it, they can generate the estimated execution plan manually by clicking the "Click here to Generate the Estimated Plan" link.

Consult Knowledgebase article 4370453 if the link to generate an execution plan is unavailable for the user to click.