Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Nov 21 '12 at 15:08

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer

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)

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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