We have a customer who is switching from SAN storage to directly attached storage. Our SQL Server database has several filegroups all of which has been deployed on the same LUN.
In order to determine the best setup for the filegroups on the directly attached storage I want to test the loading on the existing file groups. For directly attached storage I would normally have tested loading by looking at performance counters like disk queues etc. but this assumes that the file groups have been setup on separate arrays. For SANs we would normally have got the IO throughput for each LUN from the SAN vendor.
However, because all the filegroups are on the same LUN, how can I test how heavily each filegroup is loaded? Is there any way to do this with SQL profiler?
Thanks
Chris
Best Answer
Look at
sys.dm_io_virtual_file_stats. It will give you per-file (you can aggregate them manually per-filegroup) for things like number of reads, number of writes, IO read stalls and io write stalls. These server lifetime aggregates, so you cannot analyze spikes and trends, but is none the less a good starting point to differentiate hot files vs. cold files.