June 20, 2008 at 2:41 pm
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
June 20, 2008 at 2:47 pm
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
June 20, 2008 at 3:12 pm
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 @@ServerName as SQLName
, db_name(DbId) as DatabaseName
, FileId
, Current_timestamp as AsOfTs
, NumberReads as 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 sysname NOT 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 sysname NOT 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_Prior as ReadCnt
, WriteCnt - WriteCnt_Prior as WriteCnt
, ReadBytes - ReadBytes_Prior as ReadBytes
, WritteBytes - WritteBytes_Prior as WritteBytes
, ( IoStallMS - IoStallMS_Prior) as IoStallMs
FROM (
select IO_New.SQLName
, IO_New.DatabaseName
, IO_New.FileId
, IO_New.AsOfTs as EndTs
, IO_Prior.AsOfTs as 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.AsOfTs as AsOfTs_Current
, MAX(IO_Previous.AsOfTs) as AsOfTs_Prior
FROM inserted as IO_New
join InstanceFileIORaw 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
InstanceFileIORaw AS 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
June 27, 2008 at 6:18 am
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