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?
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.
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