I've been working with extracting some of the data out of the repository, and I'd like to add to the requests for a data dictionary. I used the ssrs-pack reports as a starting point (http://www.red-gate.com/products/dba/sq ... /ssrs-pack), but I do have some questions. Currently, I am attempting to graph average IO in MB/Sec.
The column
data.[Cluster_Machine_LogicalDisk_UnstableSamples_View].Cluster_Machine_LogicalDisk_CumulativeReadBytes
seems like it should match the sum of the values in
sys.dm_io_virtual_file_stats()
for all of the files on the drive; however these values are way off. Could you shed some light?
Here is my code, in case it is useful to anyone (this does create values that seem to match what is in Activity Monitor, but I haven't done any in-depth validation):
DECLARE @servername VARCHAR(100) ,
@disk CHAR(1) ,
@pStart DATETIME ,
@pEnd DATETIME
SELECT @servername = 'myserver'
SELECT @pEnd = '20110915'
SELECT @pStart = '20110914'
SELECT @disk = 'I' ;
--Set Default Dates if none provided
IF @pEnd IS NULL
SELECT @pEnd = GETDATE() ;
IF @pStart IS NULL
SELECT @pStart = DATEADD(HOUR, -2, @pEnd)
--Set disk and server to proper case used by DB
SELECT @disk = UPPER(@disk) ,
@servername = LOWER(@servername) ;
--Create CTE with IO info for time period
WITH Disk_CTE
AS ( SELECT ROW_NUMBER() OVER ( ORDER BY CollectionDate DESC ) AS rownum ,
cs.CollectionDate ,
cs.Cluster_Machine_LogicalDisk_CumulativeReadBytes ,
cs.Cluster_Machine_LogicalDisk_CumulativeWriteBytes
FROM data.[Cluster_Machine_LogicalDisk_UnstableSamples_View]
AS cs
WHERE cs.Cluster_Name = @servername
AND cs.Cluster_Machine_LogicalDisk_Name = @disk + ':'
AND CollectionDate BETWEEN utils.DateTimeToTicks(@pStart)
AND utils.DateTimeToTicks(@pEnd)
),
--Create CTE with Deltas between measurements
Delta_CTE
AS ( SELECT ( CAST(( DATEDIFF(ms, utils.DateTimeToTicks(earlier.CollectionDate),
utils.DateTimeToTicks(later.CollectionDate) )) AS FLOAT)
/ 1000 ) AS TimeDiff ,
later.CollectionDate AS DateInt ,
( ( later.Cluster_Machine_LogicalDisk_CumulativeReadBytes
- earlier.Cluster_Machine_LogicalDisk_CumulativeReadBytes )
/ ( 1024 * 1024 ) ) AS MBReadDiff ,
( ( later.Cluster_Machine_LogicalDisk_CumulativeWriteBytes
- earlier.Cluster_Machine_LogicalDisk_CumulativeWriteBytes )
/ ( 1024 * 1024 ) ) AS MBWriteDiff ,
CONVERT(CHAR(12), utils.DateTimeToTicks(later.CollectionDate), 106) AS CollectionDate ,
CONVERT(SMALLDATETIME, utils.DateTimeToTicks(earlier.CollectionDate), 106) AS CollectionDateTime ,
DATEADD(mi,
( DATEPART(mi,
utils.DateTimeToTicks(earlier.CollectionDate))
/ 10 ) * 10,
DATEADD(hh,
DATEDIFF(hh, 0,
utils.DateTimeToTicks(earlier.CollectionDate)),
0)) AS [Ten Min] ,
DATEADD(mi,
( DATEPART(mi,
utils.DateTimeToTicks(earlier.CollectionDate))
/ 30 ) * 30,
DATEADD(hh,
DATEDIFF(hh, 0,
utils.DateTimeToTicks(earlier.CollectionDate)),
0)) AS [Thirty Min] ,
DATEADD(mi,
( DATEPART(mi,
utils.DateTimeToTicks(earlier.CollectionDate))
/ 60 ) * 60,
DATEADD(hh,
DATEDIFF(hh, 0,
utils.DateTimeToTicks(earlier.CollectionDate)),
0)) AS [Hour]
FROM disk_CTE AS later
INNER JOIN disk_CTE AS earlier ON later.rownum = earlier.rownum
- 1
)
--Create the final Dataset to be consumed by SSRS
SELECT Delta_CTE.CollectionDateTime ,
CASE WHEN TimeDiff = 0 THEN 0
ELSE CAST(Delta_CTE.MBReadDiff / TimeDiff AS NUMERIC(18, 2))
END AS MBReadPerSec ,
CASE WHEN TimeDiff = 0 THEN 0
ELSE CAST(Delta_CTE.MBWriteDiff / TimeDiff AS NUMERIC(18, 2))
END AS MBWritePerSec ,
Delta_CTE.[Ten Min] ,
Delta_CTE.[Thirty Min] ,
Delta_CTE.[Hour]
FROM Delta_CTE
ORDER BY Delta_CTE.DateInt ASC
/ comments
- Community
- SQL Monitor Previous Versions
- Data dictionary?
I've been working with extracting some of the data out of the repository, and I'd like to add to the requests for a data dictionary. I used the ssrs-pack reports as a starting point (http://www.red...
0 votes
