I've got a basic reporting solution working where we have a collection of views that get exported to CSV files on a daily basis. For numerous reasons, we don't run the reports against the transactional db, but against a snapshot that we temporarily create and then drop once the reports are complete.
These reports are pretty simple and run fine on dev machines, even with millions of rows.
On our test environment, the performance is all over the place, sometimes taking a couple of seconds (same as dev) but often taking over a minute. I've run a few scripts to try and identify the cause, and the problem is always a wait type of
The disk IO at these times is low (2-5mb/sec), the CPU is at about 5%, no other queries need to be happening for the problem to occur, and the database is small (2gb) compared to server memory (32gb). The reports are run on the same machine, but write to a different physical disk.
Is the fact that the database is in an Always On Availability Group likely to be causing an issue? Would it help for the snapshot to be on a different disk from the master?
Any pointers on what the problem might be or how to investigate would be much appreciated!