I am running SQL Server 2008 Enterprise Edition and want to monitor the following performance metrics i.e. via dynamic management views (from within SQL):
Average/Maximum Read/Write I/O Waits in ms per database file for sliding time window.
That is: 4 numbers per database file: avg read wait, max read wait, avg write wait, max write wait. All in ms, and all for some sane (or even better configurable) sliding time window.
How can I do that?
PS: I have the VIEW SERVER STATE permission and can read sys.dm_os_performance_counters, sys.database_files, sys.dm_io_virtual_file_stats etc etc
PS2: At least 1 tool (Quest Spotlight 7 for SQL Server) is able to provide Max I/O Wait in ms per database file. So there has to be some way ..