BD's con más consumo de I/O

--Bases de datos que utilizan más I/O
--BD, Tipo de fichero, IO en megas, IO Stall en segundos (tiempo de espera para lectura o escritura), Porcentaje de IO stall, Posición de más a menos IO Stall (Ranking)

WITH Consumo AS
(
SELECT DB_NAME(IV.database_id) AS db, CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type, SUM(IV.num_of_bytes_read + IV.num_of_bytes_written) AS io, SUM(IV.io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IV JOIN sys.master_files AS MF ON IV.database_id = MF.database_id AND IV.file_id = MF.file_id
GROUP BY DB_NAME(IV.database_id), MF.type
)
--si no queremos verlo en segundos podemos modificar el io_stall /1000 para verlo en milisegundos, etc
SELECT db, file_type, CAST(1. * io / (1024 * 1024) AS DECIMAL(12, 2)) AS io_mb, CAST(io_stall / 1000. AS DECIMAL(12, 2)) AS io_stall_s, CAST(100. * io_stall / SUM(io_stall) OVER() AS DECIMAL(10, 2)) AS io_stall_pct, ROW_NUMBER() OVER(ORDER BY io_stall DESC) AS rnk
FROM Consumo
ORDER BY io_stall DESC;

Avisaros que en SQL 2000 no os funcionará ya que utiliza DMV de SQL Server 2005