July 3, 2013 at 6:55 am
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
July 3, 2013 at 7:18 am
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...
July 3, 2013 at 8:56 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply