Systems Engineering and RDBMS

Looking at the data and log files IO stats

Posted by decipherinfosys on March 10, 2009

SQL Server 2005 has a dynamic management view called:  sys.dm_io_virtual_file_stats which is a replacement for the function: fn_virtualfilestats() which was used in prior releases for getting the IO stats for the data and log files.  It returns database_id and file_id values as well which we can use to join it to other DMVs and get the information on the files and their properties.  This DMV has a column called io_stall (combination of the io stall values of reads and writes) which gives the total wait time (in milli-seconds) that the application/end users waited for IO to be completed on that particular file.   This can be used to quickly narrow down the files that are most in use on an instance and also to see whether there are certain files/drives that are facing I/O bottlenecks.  Microsoft MVP Itzik Ben-Gan had written an excellent article in sql mag for this DMV – you can read more on it here.

And here is a simple SQL script to show it’s usage:

select
DMV_File.num_of_bytes_read
,    DMV_File.num_of_bytes_written
,    DMV_File.io_stall_write_ms
,    DMV_File.io_stall_read_ms
,    DMV_File.io_stall
,    DMV_File.size_on_disk_bytes
,    DB_File.name
,    DB_File.physical_name
,    DB_File.size
,    DB_File.max_size
,    DB_File.growth
from sys.dm_io_virtual_file_stats (Null, Null) as DMV_File
inner join sys.master_files as DB_File
on DMV_File.database_id = DB_File.database_id
and DMV_File.[file_id] = DB_File.[file_id]
inner join sys.databases as DB
on DMV_File.database_id = DB.database_id
where DB.state = 0
order by DMV_File.io_stall desc

Do remember that just like with other DMV’s, this data gets re-set with the re-start of the SQL Server service.  So, if you want to maintain a history, you can run this on a regular basis and collect the data so that you have a performance baseline against which you can run your analysis.

Resources:

  • Itzik’s article – here.
  • BOL – here.
  • Microsoft MVP Louis Davidson’s post – here.
  • SQL CAT article – here.  This is an excellent article on diagnosing and troubleshooting transaction log performance issues and has a script which also makes use of the DMV sys.dm_io_pending_io_requests to look into the IO related performance issues.
  • SSQA.net post on the troubleshooting of the WRITELOG wait type – here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: