0

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!

Improve this question

2 Answers 2

Reset to default
1

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.

disk IO at these times is low (2-5mb/sec),

The query plans may be driving small, random IO, and 2-5mb/sec is all your disk can supply.

Would it help for the snapshot to be on a different disk from the master?

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.

Improve this answer
3
  • Thank you...is there anything I should look for in the query plan that would indicate something that could cause many small IO operations? The plan looks fine, and the only unusual statement is a LAG() statement
    – GoatInTheMachine
    Commented Nov 23, 2017 at 15:52
  • 1
    Nested Loops/Bookmark Lookup operations generate small, random IO, but other operations can too.
    – David Browne - Microsoft
    Commented Nov 23, 2017 at 15:59
  • There is one nested loop, but it's 0% of the query cost, and will only have two contributing rows from the smaller set
    – GoatInTheMachine
    Commented Nov 23, 2017 at 16:55
1

The actual problem was fragmentation - our indexes where horrifically fragmented. Since the indexes have been fixed, the problem has completely disappeared!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.