0

I'm using SQL Server 2008, and will migrate DynamoDB some tables.

I want to get I/O statistics of read bytes, and write bytes from table.

I don't know how to do this. What command should I use?

0

My understanding is this is only possible by 'collecting' the data from something like sys.dm_db_index_usage_stats between restarts of SQL Server of course.

MS Systems Centre Ops Manager has mechanisms to collect this data for you, but that's outside the scope of SQL Server itself. You could maybe look at "turning on" SQL Server Management Data Warehouse which could act as the collection mechanism for this type of data.

Personally I have a daily collection of index usage which I use to drive a custom I/O stats dashboard that is part of a 'SQL Health' web app. I simply run an ETL routine in SSIS to pick up the data from sys.dm_db_index_usage_stats and create a running commentary of usage, I/O approximations, unused indexes etc.

The bottom line is, there is no built in view of monthly usage, you have to take what's there and create it.

0

This will give you correct estimates only if you are sure that the server has not been restarted during the time frame you are intending to check..

--to check when server is last restarted

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

--to see stats for table

SELECT TableName = object_name(s.object_id),
       Reads = SUM(user_seeks + user_scans + user_lookups), Writes =  SUM(user_updates)
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE objectproperty(s.object_id,'IsUserTable') = 1
GROUP BY object_name(s.object_id)
ORDER BY writes DESC

References:https://stackoverflow.com/questions/1575676/how-to-find-out-sql-server-tables-read-write-statistics

  • Thanks for your help, I already read similar answer you written already. but I really want to see reads/writs of "each month" – Minkyu Kim Mar 25 '16 at 9:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.