Return
-
Title
Explain Plans missing from SQL PI Analyze Plans page -
Description
A SQL Server query has more 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
-
Resolution
Several of the plans listed in the Query Store list of Top Resource Consuming Queries are duplicates- If the query is circle that means it completed.
- When the icon is a square that represents query that is cancelled, but had still a plan generated for it.
- A triangle indicates an error in the execution
This leaves nine plans remaining (1, 42, 134, 170, 241, 266, 267, 278, and 284).
Among these, there are also other limits in SQL PI regarding explain plans including:- The number of resolved plans each minute
- SQL PI will only collect plans for statements that were executed
- Plans are saved to the long term repository only the plan made it to the 15min granularity of the retention policy
- Plans older than 90 days are deleted by the system automatically