SQLServerCentral.com / SQL Server 2008 - General / SQL Server 2008 / Collect tables have most row changed during specfic period / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 31 Mar 2015 16:32:54 GMT20RE: Collect tables have most row changed during specfic periodhttp://www.sqlservercentral.com/Forums/Topic1517434-391-1.aspx[quote][b]ScottPletcher (11/25/2013)[/b][hr]Use view "sys.dm_db_index_operational_stats".Capture the values at the start time, then recapture them at any subsequent time, subtract the two values, and voila, you have the mods that occurred during that time.[/quote]doh! i knew about that view too, used it before for last accessed data;i should have remembered, great job Scott.Mon, 25 Nov 2013 13:43:19 GMTLowellRE: Collect tables have most row changed during specfic periodhttp://www.sqlservercentral.com/Forums/Topic1517434-391-1.aspxUse view "sys.dm_db_index_operational_stats".Capture the values at the start time, then recapture them at any subsequent time, subtract the two values, and voila, you have the mods that occurred during that time.Mon, 25 Nov 2013 13:32:52 GMTScottPletcherRE: Collect tables have most row changed during specfic periodhttp://www.sqlservercentral.com/Forums/Topic1517434-391-1.aspx[quote][b]john.chiu (11/25/2013)[/b][hr]I was tasked to collect row count information in a system with approx. 500+ tables. They want a way to easily identify which top 20 tables insert the most rows during any period of time. The rowcount of all the tables are populated every hour to a table with tblname, rowcount and datetime stamp.Any idea how this can be done ?[/quote]to be efficient, each of the 500 tables would need an index on their timestamp columns; are those TIMESTAMP/ROWVERSION datatypes, or are they actually DateTime datatypes?if they are ROWVERSION, then you need a history table, that keeps track of the highest rowversion value at a given hour, per table[code]SELECT getdate(),'MyTable',MAX(RowVersionColumns) As Val FROM MyTable[/code]that needs to be stuffed into a history table, so that later you can do something like this:[code]SELECT getdate(),'MyTable',MAX(RowVersionColumn) As Val ,COUNT(*) As TheCount FROM MyTable WHERE RowVersionColumns &gt; (SELECT Val FROM MyHistoryTable WHERE TableName = 'MyTable')[/code]if the column datatype is datetime, then it's a little simpler; but it's still repeated for 500 tables[code]SELECT getdate(),'MyTable',COUNT(LastUpdatedColumn) As Val FROM MyTable WHERE LastUpdatedColumn BETWEEN DATEADD(hour, -1,getdate()) AND getdate()[/code]Mon, 25 Nov 2013 13:22:00 GMTLowellCollect tables have most row changed during specfic periodhttp://www.sqlservercentral.com/Forums/Topic1517434-391-1.aspxI was tasked to collect row count information in a system with approx. 500+ tables. They want a way to easily identify which top 20 tables insert the most rows during any period of time. The rowcount of all the tables are populated every hour to a table with tblname, rowcount and datetime stamp.Any idea how this can be done ?Mon, 25 Nov 2013 12:48:50 GMTjohn.chiu