#Keeweb takes forever to load database code#
#Keeweb takes forever to load database windows#

The new code returns the same results but finishes in about 600 milliseconds: INNER JOIN sys.query_store_query_text qt ON q1.query_text_id = qt.query_text_id SELECT *, qt.query_sql_text query_sql_text Note that the following rewrite only works against the database that I developed it against due to the hardcoded filter values for runtime_stats_interval_id: INT = datetimeoffset = ' datetimeoffset = ' 03:00:00' As is, the original code took 6 seconds to execute against one of my production databases: I'll back up my criticism by doing a minimal effort rewrite of the query text that you captured. For example, join elimination may not work as expected with some query store DMVs. There are unusual performance gotchas in many places. The problem is further compounded by the query store data model and the code that makes up the DMVs. Unfortunately, Microsoft has a long history of including inefficient T-SQL in parts of SSMS. The core problem is that the T-SQL code that drives the SSMS GUI reports is not efficient. If it's a SQL Server bug, is there somewhere where I can find a list of versions that has this behaviour? I didn't configure the Query Store via script, just via the properties.īut below my setup: ALTER DATABASE SET QUERY_STORE (ĬLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 15),

Third Execution Plan (stopped after 16 mins).

Second Execution Plan (stopped after 15 mins).

First Execution Plan (completed in 26 mins).

The following execution plans are from different servers: The execution plan shows a 96% cost of a Clustered Index Scan on the object. WHERE NOT (rs.first_execution_time > OR rs.last_execution_time = 1 JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id JOIN sys.query_store_query q ON q.query_id = p.query_id JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id SUM(rs.count_executions) count_executions,

ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration, ISNULL(OBJECT_NAME(q.object_id),'') object_name, This query text was retrieved from showplan XML, and may be truncated.

#Keeweb takes forever to load database windows#

10 (X64) 00:47:52 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor) When i try to load the Top durations of last day it took me 26 min to load the screen. I'm running Query Store to make a DB perform better.