SQL Server execution plans visible in SSMS (Query Store) may not appear in SQL PI's Analyze Plans view for the same time range. This article explains the most common reasons for missing plans and how to address them.
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]
Execution plans may not appear in SQL PI due to one or more of the following:
Plan size exceeds SQL PI’s 400,000-character limit.
SQL statements were not executed or were filtered out.
SQL is encrypted or too short (low workload).
SQL text or plan is truncated.
Plan collection hasn't occurred yet.
SQL PI collects up to 5 plans per minute from the top 300 SQL statements.
Execution plans are only saved if:
The statement was executed (not just parsed).
The data reaches a 15-minute resolution window.
Plans are automatically purged after 90 days.
Large plans (over 400,000 characters) are discarded and not saved.
Truncated plans are not stored. You may see logs like:
Plan query length is over the limitation of [400000]...
SSMS Query Store may show more plans due to:
Duplicate plans (same query, different icons: success, error, cancelled).
PI filters low-impact queries or internal statements.
Only distinct and executed queries with sufficient workload are collected in SQL PI.
If an execution plan is missing in SQL PI, it may attempt to fetch it on-demand using the sql_handle
and query_plan_hash
. Plan retrieval may still fail if:
SQL is truncated.
SQL is encrypted.
SQL doesn’t generate a plan (e.g., DDL, control-of-flow statements).
For batch size issues, see KB 4301141.
You can increase the number of plans SQL PI evaluates per minute:
Use this script to raise the default from 300 to 1000 (may affect performance):
configService = server.get("ConfigService");
agentService = server.get("AgentService");
agent_name = ""; // e.g., "MY-AGENT-NAME"
def modifyAgentPropertiesPrimary() {
def primary = configService.getAgentInstancePrimaryAsp(agent.getAgentNamespace(), agent.getTypeId(), agent.getId());
primary.setValueByString("paecProcessUniqueTopsNumber", "1000"); // default is 300
configService.saveConfig(primary);
return "Updated to: " + primary.getString("paecProcessUniqueTopsNumber");
}
def getAgentByName() {
agents = agentService.findByName(agent_name);
return (agents != null && agents.size() > 0) ? agents.get(0) : null;
}
agent = getAgentByName();
agent != null ? modifyAgentPropertiesPrimary() : "Agent not found.";
Foglight Cloud users: Contact Support to run this script.
If SQL PI hasn’t captured a plan, use the “Click here to Generate the Estimated Plan” link in the UI.
If the link is not available, see KB 4370453 or contact Support for guidance.
Some SQL types do not generate execution plans, including:
DDL (e.g., CREATE
, DROP
)
SET
, DECLARE
, WAITFOR
, RETURN
Control flow (IF
, WHILE
) without data access
COMMIT
, ROLLBACK
Stored procedures with no data operations
Summary
SQL PI may not show every execution plan due to plan size limits, SQL type, or collection rules. Use the tips above to improve collection or view plans manually as needed.
© ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center