1

From the question,

How to find out SQL Server table's read/write statistics?

and the best answer for me was provided by TechCoze:

DECLARE @dbid int
SELECT @dbid = db_id('database_name')

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
AND s.database_id = @dbid
GROUP BY object_name(s.object_id)
ORDER BY writes DESC

How can I place a date filter in this query so I can monitor read write data with date filters?

Like From April 1 2016 to May 1 2016

Community
  • 1
  • 1
Philip Morris
  • 459
  • 1
  • 9
  • 26
  • 2
    The view this information comes from always reflects cumulative statistics up to the present time. If you want to know what the reads and writes were over a specific period, you'll have to take periodic snapshots of this result. Unless I'm misinterpreting your question and you mean something else with "date filter"? – Jeroen Mostert Apr 28 '16 at 11:13
  • The date filter that I am referring to is like data From April 1 2016 to May 1 2016 – Philip Morris Apr 28 '16 at 11:21
  • The only date/time information included in the view is when the *last* read/write operation occurred (`last_user_seek`, `last_user_scan`, etc.) Do you want to know which tables got updated/read from in that period, or do you want to know the number of reads and writes that occurred in that period? The former can be done by adding a `WHERE`, the latter can't. – Jeroen Mostert Apr 28 '16 at 11:23
  • Is there another approach when the latter can be done? i.e. not using this query? – Philip Morris Apr 28 '16 at 11:28
  • As mentioned you will need periodic snapshots. In other words you need to save the results of the query into a history table periodically. Then you can query the history table. – Sean Pearce Apr 28 '16 at 11:31

0 Answers0