SQL Server Performance

The code I use to analyze IO

Discussion in 'Contribute Your Performance and Clustering Tips' started by bertcord, Jul 13, 2005.

  1. bertcord New Member

    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

    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


  2. FrankKalis Moderator

  3. techbabu303 New Member

    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.

  4. Luis Martin Moderator

    I cant' download.

    Luis Martin

    All in Love is Fair
    Stevie Wonder

    All postings are provided “AS IS” with no warranties for accuracy.

  5. bradmcgehee New Member


    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.

    [email protected]

    Brad M. McGehee, SQL Server MVP
  6. Haywood New Member

    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.


    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]
    ,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
    ,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.

Share This Page