2

We have peaks where latency (as measured by [Physical Disk\Avg Disk Sec read] on the drive where our data file is) goes up to 0.2 sec.

I immediately started suspecting the SAN while the DBA is suspecting that this is related to the fact that we currently have a single MDF file that is 5 terabytes.

Does a very large MDF file negatively impact latency? If so what is the appropriate size?

I have not seen a lot of content on this subject.

Thanks! -Xavier

  • 3
    Did you check for auto-growth events? If you have a 5 TB file and you have auto-growth set to 1 MB or 10% then yeah, data changes could be a real problem. – Aaron Bertrand Sep 21 '15 at 19:40
  • 2
    Also, check that Instant file initialization is enabled so that the autogrowth event can leverage it when an MDF needs to grow. – Kin Shah Sep 21 '15 at 19:45
2

Does a very large MDF file negatively impact latency? If so what is the appropriate size?

Only the size of MDF does not impact IO latency. Latency is related to your disk IO subsystem, its configuration and disk partition alignment.

To find out if disk subsystem is a bottleneck, use sys.dm_io_virtual_file_stats DMV or Capturing IO latencies for a period of time

Having multiple data files on separate LUNs - depends on number of CPU Cores will help to alleviate contention to some extent as well.

Look at the NUMA configuration of your server instance, since there is a single I/O thread and a single lazy writer thread for each NUMA node..

We have peaks where latency (as measured by [Physical Disk\Avg Disk Sec read] on the drive where our data file is) goes up to 0.2 sec.

You should look into the autogrowth events and if there are frequent autogrowth events fired, you will see a performance hit. Make sure, you have configured autogorowth to a reasonable value - definitely away from Percent increase. Also, configure Instant file initialization, so that data files can leverage its awesomeness.

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.