quote:
Originally posted by denis_the_thief
quote:
Originally posted by ferrethouse
What value to you get for diskcontention?
Note: Change "5" to your primary database id...
SELECT iostallms/(NumberReads+NumberWrites) as diskcontention ,* FROM :: fn_virtualfilestats(5, 1)
Anything over 20 indicates a possible problem.
Cool query!
I ran this for all Databases. Here is my summary.
Old Server
Small Databases: 6 to 10
Medium/Large Databases: 10 to 35
New Server
Small Databases: 10 to 35
Medium/Large Databases: 35 to 400
Bad?
I'm curious if this query can look at different time intervals. I'm also wondering why we are looking at Number of Reads/Writes rather than Bytes.
Looks bad to me. I can't explain why bytes are excluded. Brent Ozar Ltd. provided me the query. I have seen variations that do make use of bytes.
I found this query that calculates the value over a set interval. I modified it slightly to include the "contention" column...
-- create table
IF NOT EXISTS (SELECT *
FROM sys.objects
WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[filestats]')
AND type IN (N'U'))
BEGIN
CREATE TABLE filestats
(dbname VARCHAR(128),
fName VARCHAR(2048),
timeStart datetime,
timeEnd datetime,
timeDiff bigint,
readsNum1 bigint,
readsNum2 bigint,
readsBytes1 bigint,
readsBytes2 bigint,
readsIoStall1 bigint,
readsIoStall2 bigint,
writesNum1 bigint,
writesNum2 bigint,
writesBytes1 bigint,
writesBytes2 bigint,
writesIoStall1 bigint,
writesIoStall2 bigint,
ioStall1 bigint,
ioStall2 bigint
)
END
-- clear data
TRUNCATE TABLE dbo.filestats
-- insert first segment counters
INSERT INTO dbo.filestats
(dbname,
fName,
TimeStart,
readsNum1,
readsBytes1,
readsIoStall1,
writesNum1,
writesBytes1,
writesIoStall1,
IoStall1
)
SELECT
DB_NAME(a.dbid) AS Database_name,
b.filename,
GETDATE(),
numberReads,
BytesRead,
IoStallReadMS,
NumberWrites,
BytesWritten,
IoStallWriteMS,
IoStallMS
FROM
fn_virtualfilestats(NULL,NULL) a INNER JOIN
sysaltfiles b ON a.dbid = b.dbid AND a.fileid = b.fileid
ORDER BY
Database_Name
/*Delay second read */
WAITFOR DELAY '000:02:00'
-- add second segment counters
UPDATE dbo.filestats
SET
timeEnd = GETDATE(),
readsNum2 = a.numberReads,
readsBytes2 = a.BytesRead,
readsIoStall2 = a.IoStallReadMS ,
writesNum2 = a.NumberWrites,
writesBytes2 = a.BytesWritten,
writesIoStall2 = a.IoStallWriteMS,
IoStall2 = a.IoStallMS,
timeDiff = DATEDIFF(s,timeStart,GETDATE())
FROM
fn_virtualfilestats(NULL,NULL) a INNER JOIN
sysaltfiles b ON a.dbid = b.dbid AND a.fileid = b.fileid
WHERE
fName= b.filename AND dbname=DB_NAME(a.dbid)
-- select data
SELECT
dbname,
fName,
timeDiff,
(ioStall2 - ioStall1)/nullif((readsNum2 - readsNum1)+(writesNum2 - writesNum1),0) contention,
readsNum2 - readsNum1 AS readsNumDiff,
readsBytes2 - readsBytes2 AS readsBytesDiff,
readsIoStall2 - readsIOStall1 AS readsIOStallDiff,
writesNum2 - writesNum1 AS writesNumDiff,
writesBytes2 - writesBytes2 AS writesBytesDiff,
writesIoStall2 - writesIOStall1 AS writesIOStallDiff,
ioStall2 - ioStall1 AS ioStallDiff
FROM dbo.filestats
|
Suspecting Disk I/O is slow
Posted - 02/04/2013 : 10:18:20
