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
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.
