SQL Server how to test filegroup IO

Question

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

Answer 1

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.

Answer 2

I would use script and approach suggested by Paul Randal here

Another tool is built into windows is called Resource Monitor, you can see IO caused by each disk and latencies (and yes, you can easily filter by selecting SQL Server process)

Answer 3

You could see the hot files with a quick glance in the Activity Monitor in Management Studio. The I/O section will provide the list of files and their load in sheer numbers (MB, ms). Not very detailed info, but is a start.