Query store data maintenance background tasks

Question

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?

Answer 1

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.