The code I use to analyze IO

A few months ago I really starting digging into researching IO and how optimize file layout.

I found that using performance monitor was difficult for several reasons

1.Doesn#%92t take into account the difference between logical and physical IO
2.Unable to focus on individual database files
3.Hard to query and analyze the data.

At first I starting messing with importing the results into SQL Server and querying the data but then I ran across fn_virtualfilestats. AS BOL states “Returns I/O statistics for database files, including log files.”

I have created several scripts that are built around this function that I have been using to measure IO performance on my databases.

With all of the posts about disk performance I figure I would post my scripts so others can benefit from my work. I am also interested in any feedback…and of course to see if I have any flaws in my logic. You can download the scripts here. www.corderman.com/FNStats.zip

The download contains a stored procedure to track IO at each file level and then another stored procedure that can be used to report on the data.

IF there is enough interest I can provide more detailed documentation but here is an overview of some of the key data points that can be reported on

Group the data based on Database, FileGroup or File
Group based on Drive letter
Logical IOPS
LogicalIOPS – Read
LogivcalIOPS – Write
AvgIOSize
AvgIOSizeRead
AvgIOSizeWrite
PhyiscalIOPSRAID5
PhyiscalIOPSRAID10
PhysicalBytesPerSecond5
PhysicalBytesPerSecondRAID10

To collect the data run the following in QA or as a scheduled task

EXEC usp_FileStatsViaFNStats
@Duration = 5, --minutes to run
@WaitInterval = 10, --collection interval in seconds
@DatabaseID = -1, --database id -1 for all
@FileID=-1 --file id -1 for all

After collecting the data you can run the following to generate a report.
exec usp_FNFileStatsReport
@RunID = 1 --RunID from table TBL_FNFileStatsTracking_tasks

And here is the reporting proc with all of the input parameters described
exec usp_FNFileStatsReport
@RunID = 21,--RunID from table TBL_FNFileStatsTracking_tasks
@DbidFilter = '', -- can be mulitple database id's ie 1,2,3
@DBLevelGrouping = 0,---0=none, 1=Database, 2=FileGroup, 3=File
@GroupByDriveLetter = 0,--Set to 1 to group by drive letter, this grouping is done after the db level grouping
@DisplayRWRatio = 0, -- set to 1 to display RW ratio information
@DisplayRAIDOutput = 'BOTH', --CAN BE RAID5, RAID10, BOTH, NONE
@DisplayOption = 1,
@Debug = 0 -- 1 outputs the SQL String

Bert

Hi Bert,

Iam not able to download the zip file it stated "Page cannot be displayed", it would be great if you can provide alternate link.

Cheers
Sat

Bert,

Why don't you put your IO code in the form of an article so it can be published entirely on the website. If you send me a draft, I will edit and publish it.

Brad
bradmcgehee@hotmail.com

-----------------------------
Brad M. McGehee, SQL Server MVP

FWIW, Gert has some terrific I/O code and information on his site athttp://sqldev.net He has his own wrapper for fn_virtualfilestats on SQL2000, I highly suggest all DBA's get this and learn it, it's invaluable.

http://sqldev.net/misc/fn_filestats.htm


For 2005 this is what I'm starting with (got it at PASS), I haven't really modified it to suit my needs yet though.



-- Calculate avg. I/O stalls:
select DB_NAME(database_id)
, [file_id]
,io_stall_read_ms
,num_of_reads
,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
,io_stall_write_ms
,num_of_writes
,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'
,io_stall_read_ms + io_stall_write_ms as io_stalls
,num_of_reads + num_of_writes as total_io
,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
from sys.dm_io_virtual_file_stats(null,null)
order by avg_io_stall_ms desc



Perfmon defenitly lacks in the area of detailed metrics gathering and reporting ease of use. And you almost always have to go 'internal' to get good information.