SQL Server measure i\o transaction rates and sys.dm_io_virtual_file_stats

 It is difficult to monitor and extrapolate IO statistics for data and log files. From SQL server 2005 there is a view : sys.dm_io_virtual_file_stats. 

 The sys.dm_io_virtual_file_stats returnsI/O Statistics for data\log files per file. Using the view requires VIEW SERVER STATE permission . A simple sql query such as :

SELECT * FROM sys.dm_io_virtual_file_stats(db_id,file_id) 

  Where  : db_id is replaced with the database id (int) and file_id (int) is the file id.If NULL is used all files are returned.

 The results can  indicate that either  that the database is underperforming but that the hard drives are struggling to respond adequately.

By using sys.dm_io_virtual_file_stats  and monitoring for approx 5-10 minutes during a busy period , you can create metrics to pass onto the SAN administrator. The tricky aspect of getting figures , is dealing with the wild variations on IO . Therefore I like to gather statistics over a few minutes and average out – read and write speeds.

 A SAN administrator may ask  to look for indications that the average write or read speeds, are less than  4-5 ms. Anything more and this reflects some problem. The next step is to attempt to figure what problem is indicated and then how to resolve.

Higher average rates of read or write point to certain  problems such as :

1) Are 15000 RPM disk drives being used? Also , are all the drives in the array at the same RPM?

2) Check adequate devices attached to LUN

3) Write Back Cache Controller – Consider implications of  speed versus increased dependability

4) RAID 5 or RAID 10 – general rule of thumb , if more than 10% activity of db is writes than there might be some performance issues. This should be thoroughly analysed prior to implementing

 More on SQL Server – RAID levels overview - SQL Server DBA

Potential ways of improving performance:

1) Move the poorly performing data files to underutilised drives.

2) This requires some planning , but analyse the possibility of splitting one data file into 2 data files over to 2 logical drives. This will introduce more spindles into the mix – doing the same amount of work.

3)   Split data , log and tempdb files . For tempDB tactics - TempDB performance and strategy checklist - SQL Server DBA

Read More

Storage Checklist FAQ - SQL Server DBA

Calculate disk IO throughput and MB per second - SQL Server DBA