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 PAGEIOLATCH_SH.
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!
Best Answer
PAGEIOLATCH_SH means waits for reading database pages. When you create a database snapshot, none of the database pages are in the cache, and you will have to fetch them from disk.
The query plans may be driving small, random IO, and 2-5mb/sec is all your disk can supply.
A Database Snapshot is a NTFS copy-on-write snapshot of the target database. There is only one copy of the unchanged pages shared between the database and its snapshot. If you use a backup/restore instead of a Database Snapshot, then you could put it on a different disk.