quote:

---

Originally posted by jackv

If you still have access to the old server , you could do a quick comparison .
Here are some notes on SQLIO (suggested above ) and how to use http://www.sqlserver-dba.com/2011/08/disk-io-performance-and-sqlio.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

---

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.

---

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.

---

-- 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