1

These are the results from querying the sys.dm_io_virtual_file_stats DMV. (I add some calculated fields for clarity).

DM IO Virtual File Stats

I'm finding certain basic operations in Database 7 are bottlenecking core operations I have running in Database 6.

For example, I have a table in Database 7 that isn't used anywhere, and has a lot of rows (about 300 million). In Database 6, a bulk insert of hundreds of thousands of records is happening every hour throughout the entire day. If I run a simple SELECT COUNT(1) FROM Database7.dbo.UnusedTableInDatabase it takes about 10 minutes to return, and it appears to eat up a lot of the server resources, IO in particular. My continuously running bulk insert job on Database 6 crawls to almost a halt and a backlog starts to build up.

One thing I know is Database 7 has it's data and log files on the same logical drive. Database 6 is appropriately setup so that it's data and log files are on separate drives.

Where should I start digging next to try to resolve these issues?

UPDATE: My server is hosted on AWS with the Provisioned IOPS SSD (io1) drive. It sounds like I'm supposed to be seeing up to 64,000 IOPS per volume, but I'm seeing about 2,000 IOPS right now, whereas another server I have on AWS with the General Purpose SSD (gp2) drive is giving me about 6,000 IOPS.

Improve this question
2
  • 1
    Which databases are on which disks? Is this a VM? Why wouldn't doing a scan on a 300 million row table cause high IO and affect other queries on the same instance? If this table isn't used anywhere, I don't know how running a scan on it is relevant to your normal workload or performance.
    – LowlyDBA - John M
    Dec 6, 2019 at 20:59
  • Database 6's data file is on drive E, and it's log file is on drive D. Database 7 has both it's data and log files on drive G. I think it's a VM. The table not being used anywhere is a white lie to remove some complication from the conversation. It's actually used for ad-hoc reporting on rare occasions. My scan test is to diagnose issues that arise on Database 6 when other scans happen on Database 7's table. Regardless, are the numbers I'm seeing in the sys.dm_io_virtual_file_stats reasonable?...I read that that over 20ms is bad and over 100ms is really bad for the AVG read / write latencies.
    – J.D.
    Dec 6, 2019 at 21:23

1 Answer 1

Reset to default
2

My problem with this DMV is that it misses the critical context of time, so it's difficult to pinpoint when the spikes in latency occur. If you're running a VM rather than an RDS instance, I'd recommend setting up a Perfmon Trace to collect disk latency metrics so you can narrow down the times. If that's not an option, push the results of your query to a user table every 5 minutes or so and then trend out the deltas so you can see what periods of time experience spikes.

Once you figure out when spikes are occurring, you'll have an easier time identifying the cause of the latency via Extended Events (e.g. capturing long-running queries), Job History, etc. Who knows, maybe you've run into a situation where you've got nothing locally directly affecting the I/O during the spikes at which point you need to escalate to Amazon with this information in hand. Noisy neighbor scenarios happen, so don't rule anything out.

On a side note, if you want to further break down the DMV stats to their database files, check out Paul Randal's post on this DMV explaining how to do that.

Improve this answer
1
  • Good points, I'll have to look into Perfmon Tracing. You reminded me I did setup low-key logging a few days ago but only one at the end of the day just to see the change in the AVG. Day over day AVG remained pretty constant (e.g. my worst case for Database ID 6 is still averaging 179ms every night.)
    – J.D.
    Dec 13, 2019 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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