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 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 spikes in disk IO when the transaction log is being backed up every 20 or so minutes.
Query store has been running for years on the server without any noticable disk IO spikes on log backups. Have anyone had an experience like this and what did they do to fix the issue? We run on SQL Server 2016 EE version 13.0.5622.0