February 14, 2011 at 7:31 am
Populated a table with results from a sys.dm_io_virtual_file_stats query run across the entire MSSQL estate. (around a 1000)
Wish to know the sum of num_of_bytes_read, num_of_bytes_written per server. Columns are
[server_name], [file_id], [num_of_bytes_read],[num_of_bytes_written], [date].
No matter which method I try of using SUM and GROUP BY, I cannot obtain the sum of num_of_bytes_read, num_of_bytes_written grouped by server. Descrepencies I get are multple rows per server x the number of file_id'ss. And [num_of_bytes_read],[num_of_bytes_written] are the same value for all server's.
I have tried every combination possible of GROUP BY and column order in the GROUP BY statement. noting works.
The below script is the most basic but does not work.This will return a single row per server name howvever there are duplicate values in the other columns. Any ideas?
select
[SQLServer_name],
sum(num_of_bytes_read),
sum(num_of_bytes_written)
from IOStats
GROUP BY [SQLServer_name]
February 23, 2011 at 12:48 pm
I'm not sure i follow the problem...the SQL you posted should return exactly what you are looking for. The problem may be in how you are putting the data into your table.
Perhaps some of the data would help us help you.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
February 25, 2011 at 11:57 am
Maybe it's just a typo in your post, but if the collumn name in the table is [server_name], then the query should use that same name and not [SQLServer_name].
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply