I’m in a SQL Server 2016 database trying to view the Top Resource Consuming Queries. Since this database has Query Store enabled, I went to Query Store > Top Resource Consuming Queries and the usual screen pops-up, as the data is loading:
After a few seconds waiting, I gave up on Query Store and went to
see random videos on the internet do other very professional task, that took me about ten minutes. When I switched back to check the result, the query responsible to retrieve this information was still running.
At this stage, I was wondering that maybe the Query Store got too fat to be fast. The results shown that it was occupying about 1.5 GB. That’s not very impressive, isn’t it?! But that’s something, I guess.
Initially, I thought using DBCC CLONEDATABASE. This command creates a schema-only copy of a database, to the same server as the source database and allows us to include Query Store data. After that, it would be safe to clear the Query Store without losing those statistics, and it would make a fresh start, collecting enough data to present the recent results, without the weight of historical data. Nevertheless, I would have to go through the same pain to analyze older data, so I decided to leave this path and adapt the query that the Query Store was running instead:
SELECT TOP 25 q.query_id, OBJECT_NAME(q.object_id), rs.avg_duration, rs.avg_cpu_time, rs.avg_logical_io_reads, rs.avg_logical_io_writes, rs.last_execution_time FROM sys.query_store_query AS q JOIN sys.query_store_plan AS p ON q.query_id = p.query_id JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id WHERE q.object_id > 0 AND rs.last_execution_time > DATEADD(HOUR, 1, GETUTCDATE()) ORDER BY rs.avg_duration DESC
By including Live Query Statistics, I was able to see that something was going off with the amount of data on the TVF used by the Query Store, that was posteriorly going through a Filter operator, a Row Count Spool and ended up being joined using Nested Loops.
As quick workaround, I hinted the query with OPTION(HASH JOIN) to change the plan and…
It ran in about 2.5 seconds! As we can see, the plan changed completely – No nasty Filters, no Spools, nor Nested Loops, obviously.
We can use sp_create_plan_guide to apply this principle in the query running behind the Top Resource Consuming Queries in the Query Store GUI, but removing the power of choice from SQL Server is dangerous and often ends up in tears. So, proceed carefully!