0

Query store has been running for years on the server without any noticeable disk IO spikes on log backups. We run on SQL Server 2016 EE version 13.0.5622.0

Recently we experienced a sudden surge in disk I/O every twenty minutes or so. We backup logs every five minutes, and every third or fourth backup would increase dramatically (from ~100 MB to >3 GB in size).

It happened at 2 AM without any changes being done to the server.

I dumped the contents of some of the log files and noticed a huge surge in swappages on the Query store system data tables when these log backup IO spikes occured. Disabling query store entirely caused the disk IO to resume at normal levels when backing up the log.

I presume the reason for the large transaction log backups would be online index rebuilds on the query store tables. We had configured query store to capture all transactions, auto cleanup, retain data for 180 days and max the data cache to 15 GB.

So far so good. I re-enabled query store after changing the configuration to auto, auto, 120 days and 8 GB, but now we are starting to see the same type of spikes in disk IO every 20 minutes or so when the transaction log is being backed up.

Have anyone had an experience like this and what did they do to fix the issue?

0

For the query store to be consuming a lot of IO I would say, by my experience, that there is a lot of change in the data calling methods.

  • Are you aware of any reporting with new filters?
  • Any major change is Store Procedures parameters that might cause parameter sniffing
  • Any other change to an Application that consumes data in the sense of querying with parameter changes?

I'm asking since these situations create new execution plans and those create data in the Query Store, causing the IO.

1
  • Hi Pimenta, thanks for the answer. No there are no huge surges of new queries that match this behaviour of happening ever 20 minutes 24/7. The querying structure is absolutely normal. – Rasmus Remmer Bielidt May 21 at 13:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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