0

We are using SentryOne, and one of the biggest consumers of IO & CPU resources is the query

delete sys.plan_persist_wait_stats
where Plan_id in (
    select plan_id
    from sys.plan_persist_plan
    where query_id = @query_id
)

I tried to Google it and all what I found is that it relates to the QueryStore. I'm not able to access sys.plan_persist_wait_stats and sys.plan_persist_plan using Select command, and they are not documented.

Any idea what to do?

We are using Microsoft SQL Server 2017 (RTM-CU26).

Dale K
  • 25,246
  • 15
  • 42
  • 71
Geri Reshef
  • 397
  • 1
  • 6
  • 17
  • Do you have processes running `sys.sp_query_store_remove_query` or is this just from default behaviour? – Martin Smith May 31 '23 at 07:09
  • @MartinSmith No one uses it. – Geri Reshef May 31 '23 at 07:15
  • I guess that I'm not supposed to be exposed to the system activity and the system objects, mainly because I cannot have any influence on them: I cannot change the code, nor index the dmvs.. I wonder why this information is presented.. :-\ – Geri Reshef May 31 '23 at 07:23
  • Yeah, seems like something you need to raise with Microsoft to look into - I remember SSIS also used to have inefficient cleanup routines and this was something that was fixed in a CU that added some missing indexes. You're not the only person to complain about this query https://social.msdn.microsoft.com/Forums/en-US/925ebeb8-4f2e-48a9-bf14-8ab6e88a5217/huge-io-amp-cpu-consumption-by-deletes-in-sysplanpersistruntimestats-by-querystore?forum=sqldatabaseengine – Martin Smith May 31 '23 at 07:28
  • The clustered index of `sys.plan_persist_wait_stats` is `plan_id` and it does have a non clustered index on `query_id` so I suppose any issue will be with `plan_persist_wait_stats` (has no index with leading column `plan_id` as CI is on `runtime_stats_interval_id, plan_id, wait_category, execution_type` and NCI is on `wait_stats_id`) – Martin Smith May 31 '23 at 07:35

0 Answers0