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?