Matthew McGiffen takes a look at what’s slowing down that SQL Server instance:
Some time ago I wrote a query store version of the “Top 20 queries” query that will produce a ranked list of your most expensive queries – and I’ve ended up using this a lot.
The only downside of using the DMVs for Query Store is that they are per database whereas dm_exec_query_stats is a view across the whole instance. So I had to use a cursor and a temp table, populating the temp table for each database in turn.
Click through for the script. This is where having a centralized Query Store, in which you regularly dump Query Store stats from various user databases into your own managed database, could be quite useful.