How to determine the average disk i/o

  • I would like to determine the average and peak disk i/o for my Sql 2005 database. Are there any Sql Server 2005 tools to accomplish this ? Will the Sql Profiler or Windows PerfMon to the trick or is there something else ?

    Thanks,

    bob

  • If you are running on a SAN, you will need the performance tools provided by the SAN vendor.

    If you are using direct storage, then Performance Monitor will provide you with this data. Profiler cannot provide this information.

    See http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

    bob mazzo (6/20/2008)


    I would like to determine the average and peak disk i/o for my Sql 2005 database. Are there any Sql Server 2005 tools to accomplish this ? Will the Sql Profiler or Windows PerfMon to the trick or is there something else ?

    Thanks,

    bob

    Brad M. McGehee
    DBA

  • There is a system function named fn_virtualfilestats that will provided the statistics since SQL Server started.

    I run this function every hour versus about 200 SQL Servers.

    Table InstanceFileIORaw stages the output from fn_virtualfilestats.

    An insert trigger on InstanceFileIORaw determines the change since the last capture and records into table InstanceFileIO.

    Run this SQL on a regular basis:

    insert into dbo.InstanceFileIORaw

    (SQLName, DatabaseName, FileId, AsOfTs, ReadCnt, WriteCnt, ReadBytes, WritteBytes, IoStallMS )

    SELECT @@ServerNameas SQLName

    ,db_name(DbId) as DatabaseName

    ,FileId

    , Current_timestamp as AsOfTs

    ,NumberReadsas ReadCnt

    ,NumberWrites as WriteCnt

    ,BytesRead as ReadBytes

    ,BytesWritten as WriteBytes

    ,IoStallMS

    from ::fn_virtualfilestats (default, default )

    Table and trigger SQL:

    CREATE TABLE dbo.InstanceFileIORaw (

    SQLName sysnameNOT NULL ,

    DatabaseName sysname NOT NULL ,

    FileId smallint NOT NULL ,

    AsOfTs datetime NOT NULL ,

    ReadCnt bigint NULL ,

    WriteCnt bigint NULL ,

    ReadBytes bigint NULL ,

    WritteBytes bigint NULL ,

    IoStallMS bigint NULL ,

    CONSTRAINT InstanceFileIORaw_P PRIMARY KEY CLUSTERED

    (SQLName, DatabaseName,FileId,AsOfTs) WITH FILLFACTOR = 80)

    CREATE TABLE dbo.InstanceFileIO (

    SQLName sysnameNOT NULL ,

    DatabaseName sysname NOT NULL ,

    FileId smallint NOT NULL ,

    StartTs datetime NOT NULL ,

    EndTs datetime NOT NULL ,

    ReadCnt bigint NOT NULL ,

    WriteCnt bigint NOT NULL ,

    ReadBytes bigint NOT NULL ,

    WritteBytes bigint NOT NULL ,

    IoStallMS bigint NOT NULL ,

    OverSeconds AS (datediff(second,StartTs,EndTs)) ,

    OverMinutes AS (datediff(minute,StartTs,EndTs)) ,

    IoStallSeconds AS (IoStallMS / 1000.00) ,

    IoStallPercent AS (IoStallMS / (datediff(millisecond,StartTs,EndTs) * 100.00)) ,

    MsPerIo AS (case (ReadCnt + WriteCnt) when 0 then 0 else (IoStallMs * 1.0 / (ReadCnt + WriteCnt)) end) ,

    IoStatus AS (case (ReadCnt + WriteCnt) when 0 then 1 else (case when (IoStallMS / (ReadCnt + WriteCnt) > 10.0) then 5 when (IoStallMS / (ReadCnt + WriteCnt) > 8.0) then 4 when (IoStallMS / (ReadCnt + WriteCnt) > 6.0) then 3 when (IoStallMS / (ReadCnt + WriteCnt) > 4.0) then 2 else 1 end) end) ,

    CONSTRAINT InstanceFileIO_P PRIMARY KEY CLUSTERED

    (SQLName, DatabaseName,FileId,StartTs) WITH FILLFACTOR = 80)

    go

    CREATE trigger InstanceFileIORaw_ia100 on InstanceFileIORaw after insert

    as

    set nocount on

    set xact_abort on

    insert into InstanceFileIO

    (SQLName

    ,DatabaseName

    ,FileId

    ,StartTs

    ,EndTs

    ,ReadCnt

    ,WriteCnt

    ,ReadBytes

    ,WritteBytes

    ,IoStallMs

    )

    select SQLName

    ,DatabaseName

    ,FileId

    ,DATEADD(ms,3,COALESCE(StartTs , EndTs - .041666) ) as StartTs

    ,EndTs

    ,ReadCnt - ReadCnt_Prioras ReadCnt

    ,WriteCnt - WriteCnt_Prioras WriteCnt

    ,ReadBytes - ReadBytes_Prioras ReadBytes

    ,WritteBytes - WritteBytes_Prioras WritteBytes

    ,( IoStallMS - IoStallMS_Prior)as IoStallMs

    FROM(

    select IO_New.SQLName

    ,IO_New.DatabaseName

    ,IO_New.FileId

    ,IO_New.AsOfTsas EndTs

    ,IO_Prior.AsOfTsas StartTs

    ,IO_New.IoStallMS

    ,COALESCE ( IO_Prior.IoStallMS, 0 )as IoStallMS_Prior

    ,IO_New.ReadBytes

    ,COALESCE (IO_Prior.ReadBytes, 0 )as ReadBytes_Prior

    ,IO_New.ReadCnt

    ,COALESCE (IO_Prior.ReadCnt, 0 )as ReadCnt_Prior

    ,IO_New.WriteCnt

    ,COALESCE (IO_Prior.WriteCnt, 0 )as WriteCnt_Prior

    ,IO_New.WritteBytes

    ,COALESCE (IO_Prior.WritteBytes, 0 ) as WritteBytes_Prior

    from inserted AS IO_New

    left outer join(

    select IO_New.SQLName

    ,IO_New.DatabaseName

    ,IO_New.FileId

    ,IO_New.AsOfTsas AsOfTs_Current

    ,MAX(IO_Previous.AsOfTs)as AsOfTs_Prior

    FROMinserted as IO_New

    joinInstanceFileIORaw AS IO_Previous

    on IO_Previous.SQLName= IO_New.SQLName

    and IO_Previous.DatabaseName= IO_New.DatabaseName

    and IO_Previous.FileId= IO_New.FileId

    and IO_Previous.AsOfTs< IO_New.AsOfTs

    group by IO_New.SQLName

    ,IO_New.DatabaseName

    ,IO_New.FileId

    ,IO_New.AsOfTs

    ) as IO_Latest

    on IO_Latest.SQLName= IO_New.SQLName

    and IO_Latest.DatabaseName= IO_New.DatabaseName

    and IO_Latest.FileId= IO_New.FileId

    and IO_Latest.AsOfTs_Current= IO_New.AsOfTs

    left outer join

    InstanceFileIORawAS IO_Prior

    on IO_Prior.SQLName= IO_Latest.SQLName

    and IO_Prior.DatabaseName= IO_Latest.DatabaseName

    and IO_Prior.FileId= IO_Latest.FileId

    and IO_Prior.AsOfTs= IO_Latest.AsOfTs_Prior

    and IO_Prior.ReadCnt<= IO_New.ReadCnt

    and IO_Prior.WriteCnt<= IO_New.WriteCnt

    and IO_Prior.ReadBytes<= IO_New.ReadBytes

    and IO_Prior.WritteBytes<= IO_New.WritteBytes

    ) InstanceFileStatistics

    GO

    SQL = Scarcely Qualifies as a Language

  • Is that script just since server started or per the hour. If you run that now would it be the amount per hour vs since day one.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply