asked on

ms sql query

Hi,

I have a query which tells me the i/o stats for my databases for each database file for each day which experts help me with below - this works fine;
However i need to change this to see the total values for each column e.g the total number_of_bytes_read, total_number_of_bytes_written etc... for each day for the overall system. I do not need the physical name in the output as it would be the overall values of the system i need to report on. So my output would be;

Date total num_of_bytes_read total num_of_bytes_written etc

3/4/2011 765897659658 87958758957 etc
4/4/2011 865958975874 06706096096 etc
5/4/2011 875606760899 67697698689 etc

Please show me how to change this query or is there a better way of doing this

SELECT CONVERT(VARCHAR(10), n.[Timestamp], 120) as WhatDate,
n.[physical_name],
(n.[] - o.[num_of_bytes_read]) as [num_of_bytes_read],
(n.[num_of_bytes_written] - o.[num_of_bytes_written]) as [num_of_bytes_written],
(n.[io_stall_write_ms] - o.[io_stall_write_ms]) as [io_stall_write_ms],
(n.[io_stall] - o.[io_stall]) as [io_stall]
FROM iostats n
JOIN iostats o
ON n.physical_name = o.physical_name
AND CONVERT(VARCHAR(10), n.[Timestamp], 120) = CONVERT(VARCHAR(10), DATEADD(dd, 1, o.[Timestamp]), 120)

thanks
H

batchakamal

Run the below query on master database..

SELECT
GETDATE(),
SUM(numberReads) AS [Total Number of Reads],
SUM(BytesRead) AS [Total Bytes Read],
SUM(NumberWrites) AS [Total Number of Writes],
SUM(BytesWritten) AS [Total Amount of Writes]
FROM
fn_virtualfilestats(NULL,NULL) a INNER JOIN
sysaltfiles b ON a.dbid = b.dbid AND a.fileid = b.fileid

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hraja77

ASKER

i will test this tommorrow but do i need to do this;
ON n.physical_name = o.physical_name
as i want the overall system sums

thanks
H

hraja77

ASKER

thanks - excellent answer