I/O I/O - It's why my server's slow.....

Often I've been curious about ways to measure the performance of SQL Server, and be able to present it in a readable and useful format.  One of those measures is examing I/O Statistics. In addition to calculating the total I/O, by using CTEs [Common Table Expressions], you can output the percentage of I/O usage, which will tell you where most of the I/O is occurring.

While this is useful information, the % doesn't really mean much and does nothing for determining if there is a problem or not. That is where the other columns will come into play. For instance the IO Stall ms column will tell you how long you wait for the reads or writes to occur. The more you wait the more performance is potentially affected. So if a given file is using 90% of the I/O but there is no waiting you should be OK. But if you have the oppurtunity to move files to other physical arrays you can use this to decide which ones use the most I/O and might want to separate them from each other.

SQL Server 2005 introduces dynamic management objects (views and functions) that give you server-state information in a convenient, relational format. You can use this information to tune performance, diagnose problems, and monitor the health of your system.

Here, I will reference some excellent links with respect to analyzing I/O statistics, and some scripts to represent I/O usage by database, and by disk.

First, here is a great article 'Querying DMF's to Analyze Performance',http://www.sqlmag.com/Articles/Print.cfm?ArticleID=93327, which focuses on using the dynamic management function named sys.dm_io_virtual_file_stats, which provides I/O statistics about database files.  Below are some such useful scripts:

Calculating the Percentage of I/O for Each Database
---------------------------------------------------
WITH Agg_IO_Stats
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,
  database_name,
  io_in_mb,
  CAST(io_in_mb / SUM(io_in_mb) OVER() * 100
       AS DECIMAL(5, 2)) AS pct
FROM Agg_IO_Stats
ORDER BY row_num;
----------------------------------------------------

Calculating the percentage of I/O by Drive
----------------------------------------------------
With g 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
--order by vfs.num_of_bytes_written desc)
)
select database_name,drive_letter, BYTESWRITTEN,
Percentage = RTRIM(CONVERT(DECIMAL(5,2),
BYTESWRITTEN*100.0/(SELECT SUM(BYTESWRITTEN) FROM g))) --where drive_letter='R')))
+ '%'
from g --where drive_letter='R'
order by BYTESWRITTEN desc
-------------------------------------------------------

The first script, will return the I/O usage for the database, across all drives (regardless of the disk layout of the particular database).  The second script, I/O by Drive, will display the I/O usage by drive, and so will indicate on which disk is producing the most I\O, and which database its related too.  This can be helpful in determining which database file should be moved to another disk.

Another dated, but useful article in examining I\O statistics, is this one by Novick software, http://www.novicksoftware.com/Articles/sql-server-io-statistics.htm, which is based on SQL Server 2000.  The scripts still work on 2005 and predates the advent of DMV's, but is a good primer on learning about I/O stats.

I hope the above information is helpful, and that many of you out there can add it to your arsenal of perfomance monitoring tools.

http://www.pearlknows.com
Try SQLCentric, our award-winning SQL Monitoring and Alert System!