1

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.

Query usage

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.

Change tracking configuration

Do you think these queries could be posing problems on my server?

Anyone experienced something similar?

Improve this question
9
  • 1
    Does SQL-server allow you to see what is executing those queries? Perhaps you have a number of runaway processes. If not, perhaps there are so many different queries being executed, that the cleanup has a hard time to follow. Do the applications using that database, use bind variables (prepared statements) in their queries?
    – Gerard H. Pille
    Commented Nov 27, 2021 at 16:11
  • 1
    BTW, Query Store is something you switch on when you are looking for the cause of a (performance) problem. When you have found the cause - as in this case, excessive queries - you switch it off. Don't leave it running if you're not interested in the statistics it produces. red-gate.com/simple-talk/databases/sql-server/… nr. 8.
    – Gerard H. Pille
    Commented Nov 27, 2021 at 21:56
  • 1
    Of course, I understand that. But once you've found some trouble to shoot, you can switch it off until that trouble has been shot. Then, when you have time to do some more, you switch it on again, until you found the next ache.
    – Gerard H. Pille
    Commented Nov 29, 2021 at 16:13
  • 1
    "they do add parameters to the queries." as in "concatenate those parameters to the query string"? I've seen that plenty of times. It can drown a DB and make it spend more time planning and compiling queries than actually executing them. The application should be modified, you won't believe your eyes how much performance you gain.
    – Gerard H. Pille
    Commented Nov 29, 2021 at 16:16
  • 1
    Is the .NET application facing the internet?
    – Gerard H. Pille
    Commented Nov 29, 2021 at 16:19

1 Answer 1

Reset to default
1

I'm no expert on using the Query Store, but from my brief research on the maintenance queries its running (per your sp_blitzcache results) that does seem quite excessive. For example, this person claims it only runs once a day on their particular database.

You may want to look into Monitoring performance by using the Query Store - Query Store space usage and seeing if your Query Store is configured for an unreasonably low storage amount. Specifically you can run the following query from that Books Online article to see what it's currently configured for:

SELECT current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

If your problematic database has a high frequency of use, perhaps you're continually running out of storage space for your current Query Store configuration. But again, ~22,000 executions per minute also seems very suspect.

Improve this answer
4
  • I'm suspecting that I'm continually running out of storage space for the Query Store configuration. I tried to configure a bigger size for the Query Store data, I'll see if it helps.
    – Danielle Paquette-Harvey
    Commented Nov 29, 2021 at 16:08
  • It'll help until that size is filled, then it will have to delete as many rows as before.
    – Gerard H. Pille
    Commented Nov 29, 2021 at 16:21
  • 1
    That was it, once I set the file big enough, it stopped spamming the queries to the server. Now with the file big enough to be able to hold the number of days configured in the auto-cleanup, it's not spamming queries anymore. I've had the query store enabled on my other databases for months without problems. This one only needed a bigger file size.
    – Danielle Paquette-Harvey
    Commented Nov 30, 2021 at 13:30
  • 1
    @DaniellePaquette-Harvey Awesome! Glad it worked out. I learned something new now too. 🙂
    – J.D.
    Commented Nov 30, 2021 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.