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:
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.
- 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.