Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Delta of Disk IO Expand / Collapse
Author
Message
Posted Wednesday, July 3, 2013 6:55 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 6:57 AM
Points: 598, Visits: 1,609
Hi all - I am trying to work out some deltas from information I collect every 5mins from the sys.dm_io_virtual_file_stats DMV. I am currently struggling to find a way to minus one value from the other for each row in the table.

I am trying to do the following to get this to work however I have syntax problems in the CASE statement and also issues when trying to + 1 to the ROW total.

All help appreciated, apologies if this is not clear.

WITH IOPS   ([IO_STALL]
,[IO_STALL_READ_MS]
,[IO_STALL_WRITE_MS]
,[NUM_OF_READS]
,[NUM_OF_WRITES]
,[SIZE_ON_DISK_MB]
,[DBNAME]
,[NAME]
,[FILE_ID]
,[DB_FILE_TYPE]
,[FILE_LOCATION]
,[TIMESTAMP]
,[ROW])
AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY FILE_LOCATION ORDER BY TIMESTAMP DESC) AS [ROW]
FROM dbo.DISKIOPS
)
SELECT
CASE
WHEN ROW = 1 THEN 0
ELSE ((SELECT IO_STALL FROM IOPS WHERE ROW = (ROW+1)) - IO_STALL)
END AS IO_STALL,
FILE_LOCATION
FROM IOPS


Post #1470027
Posted Wednesday, July 3, 2013 7:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 4:14 PM
Points: 264, Visits: 319
Typically I would add the SQL here but I'm running into a meeting...

You could join the CTE to itself (aka make it recursive) but join each row to ROW# - 1, so ROW 1 would be joined to ROW 2, etc. Then you can do your math without needing the CASE statement.

Just my 2 cents...
Post #1470043
Posted Wednesday, July 3, 2013 8:56 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 6:57 AM
Points: 598, Visits: 1,609
Hi all for anyone who is interested I got the script created as follows:

--Code to pull out deltas between collected IO stats.
;WITH IOPS ([IO_STALL]
,[IO_STALL_READ_MS]
,[IO_STALL_WRITE_MS]
,[NUM_OF_READS]
,[NUM_OF_WRITES]
,[SIZE_ON_DISK_MB]
,[DBNAME]
,[NAME]
,[FILE_ID]
,[DB_FILE_TYPE]
,[DISK]
,[FILE_LOCATION]
,[TIMESTAMP]
,[ROW])
AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY FILE_LOCATION ORDER BY TIMESTAMP DESC) AS [ROW]
FROM dbo.DISKIOPS
)

SELECT [IO1].[NAME]
,[IO1].[TIMESTAMP]
,[IO1].[DISK]
,[IO_STALL] = ([IO2].[IO_STALL] - [IO1].[IO_STALL])
,[IO_STALL_READ_MS] = ([IO2].[IO_STALL_READ_MS] - [IO1].[IO_STALL_READ_MS])
,[IO_STALL_WRITE_MS] = ([IO2].[IO_STALL_WRITE_MS] - [IO1].[IO_STALL_WRITE_MS])
,[NUM_OF_READS] = ([IO2].[NUM_OF_READS] - [IO1].[NUM_OF_READS])
,[NUM_OF_WRITES] = ([IO2].[NUM_OF_WRITES] - [IO1].[NUM_OF_WRITES]) --IO1.IO_STALL, IO2.IO_STALL, *
FROM IOPS IO1 JOIN IOPS IO2 ON IO1.ROW = (IO2.ROW+1)
WHERE IO1.NAME = IO2.NAME

Post #1470096
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse