There are a number of blog posts about why a single query with multiple plans is bad. But they mostly address when you find them, not how to find them.
Query Store does not offer a report, that focuses on finding them, nor is it an obvious metric you can choose.
You can use the configure option to filter for queries with multiple plans on specific metrics.
How do you find all the queries with multiple plans?
You can search the query store plan. The query below returns all the query_ids with multiple plans. You can then use the Tracked Queries report to look at it with the GUI. You will probably want to use the configure option to extend the time interval.
Select query_id
, COUNT (plan_id) as 'Plan_Count'
from sys.query_store_plan
Group by query_id
Having COUNT (plan_id) >= 2
If you have exported the Query store data to another database as described here removing the leading sys. and the query runs on your exported data.
Select query_id
, COUNT (plan_id) as 'Plan_Count'
from query_store_plan
Group by query_id
Having COUNT (plan_id) >= 2
Exporting the data and adding indexes, can have a huge impact on query cost. In the example below my exported data is in seperate database that also has Query Store enabled. The top query with from sys.query_store_plan returns results from the databases Query Store, while the second query with from query_store_plan returns results from the table that was created when I exported the data to it.
