Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

 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!

 

Comments

Posted by Stephen R Montgomery Jr on 18 August 2009

This is an excellent article--thank you for writing and posting this .

Posted by Mark Cascella on 19 August 2009

Excellent.  Thanks.

Posted by f3pal on 20 August 2009

Thanks for this info.

Posted by bklein2 on 20 August 2009

Good Article - I used the sql immediately and altered the first one to show how much was reads.

--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,

   CAST(SUM(num_of_bytes_read) / 1048576.

        AS DECIMAL(12, 2)) AS io_read_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,

 io_read_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;

Posted by joe.jordan on 20 August 2009

Very helpful queries.  Well done sir!

Posted by Andrew Coppock on 20 August 2009

Great for use in our development area to help identify under-utilized databases. Thanks!

Posted by ahperez on 20 August 2009

How helpful and useful. Thanks!

Posted by Anonymous on 21 August 2009

Do you consider the I/O whenever a database design project is assigned? It is one of the primary design

Posted by pmcpherson on 24 August 2009

Can someone point me to a resource on how to use WITH as it was used above?  I have a feeling this is just the surface of a powerful scripting tool.  I'd like something pretty in depth with many examples, if possible.

Posted by Rowland Gosling on 26 August 2009

He's using a Common Table Expression (CTE). See this article for further information see the BOL or the link below:

msdn.microsoft.com/.../ms190766.aspx

Posted by Anonymous on 26 August 2009

Pingback from  Twitter Trackbacks for                 SQL Server Central, I/O I/O - It's why my server's slow.....Examing I\O Statistics - Pearl Knows         [sqlservercentral.com]        on Topsy.com

Posted by BuckWoody on 31 August 2009

I'd like to get your permission (if possible) to show this script in a demo (with attribution of course) - can you ping me and I'll send you my regular e-mail address? Thanks!

Posted by isoletl on 2 September 2009

Brilliant!

Leave a Comment

Please register or log in to leave a comment.