Get I/O Stats directly from the SQLServer

garak

Here two little scripts, to get some I/O Stats from the SQLServer2005.
For the script we use the "sys.dm_io_virtual_file_stats" which provides I/O statistics about database file

The two little script are based on this article --> http://www.sqlmag.com/Articles/Print.cf ... leID=93327

The First script calcs. the Percentage of I/O for every Database on your server

//we build us a temp. reultset
WITH IO_Stats_per_DB
AS
(
  SELECT
    DB_NAME(database_id) AS database_name,
    CAST(SUM(num_of_bytes_read + num_of_bytes_written) / 1048576.
         AS DECIMAL(12, 2)) AS io_in_mb
  FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS DM_IO_Stats
  GROUP BY database_id
)

SELECT
  ROW_NUMBER() OVER(ORDER BY io_in_mb DESC) AS row_num, //we "create" a rowID
  database_name, io_in_mb, CAST(io_in_mb / SUM(io_in_mb) OVER() * 100  AS DECIMAL(5, 2)) AS pct //we create a percent coloumn
FROM IO_Stats_per_DB //we use the temp. reultset
ORDER BY row_num;

The second script is to get the I/O stats by Drive. So you can find out, which disc is producing the most I/O. This could help to know, which database should remove to a other disk.

With IO_Stats_per_Drive as
(
  select db_name(mf.database_id) as database_name, mf.physical_name,
           left(mf.physical_name, 1) as drive_letter,
           vfs.num_of_writes,
           vfs.num_of_bytes_written as BYTESWRITTEN,
           vfs.io_stall_write_ms,
           mf.type_desc, 
           vfs.num_of_reads, 
           vfs.num_of_bytes_read, 
           vfs.io_stall_read_ms,
           vfs.io_stall, 
           vfs.size_on_disk_bytes
  from 
    sys.master_files mf
  join 
    sys.dm_io_virtual_file_stats(NULL, NULL) vfs
  on 
    mf.database_id=vfs.database_id and mf.file_id=vfs.file_id
)

select database_name,drive_letter, BYTESWRITTEN,
         Percentage = RTRIM(CONVERT(DECIMAL(5,2),
         BYTESWRITTEN*100.0/(SELECT SUM(BYTESWRITTEN) FROM IO_Stats_per_Drive))) + '%'
from 
  IO_Stats_per_Drive
order by 
  BYTESWRITTEN desc

Regards