I have an SQL Server 2016 Standard production server with more 59 clients Databases.
Microsoft SQL Server 2016 (SP2-CU17) (KB5001092) - 13.0.5888.11 (X64) Mar 19 2021 19:41:38 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: )
This server is heavily used and Query Store is enabled on all databases. My biggest database on the server is about 800gb in size. It also uses about 15% of the I/O subsystem by itself.
I'm using Brent Ozar's sp_blitzcache proc and I've been searching for the most executed queries on the server since I'm seeing an increase in the Queries executed/second in Perfmon.
Now, my most executed queries are not user queries. The most executed queries are internal Query Store cleaning queries, on the biggest database.
I'm not exactly sure what to do about it (other than maybe trying to enable/disable the Query Store on the database). If I do that, I will loose all the data collected.
Change Tracking is configured to keep 2 days of data with auto cleanup.
Do you think these queries could be posing problems on my server?
Anyone experienced something similar?