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
LVL 1
hraja77Asked:
Who is Participating?
 
LowfatspreadConnect With a Mentor Commented:
select whatdate
,sum(num_of_bytes_read) as num_of_bytes_read
,sum(num_of_bytes_written) as num_of_bytes_written
,sum(io_stall_write_ms) as io_stall_write_ms
,sum(io_stall) as io_stall
from (
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)
) as x
group by whatdate
order by 1 desc
0
 
batchakamalCommented:
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

0
 
hraja77Author Commented:
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
0
 
hraja77Author Commented:
thanks - excellent answer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.