November 25, 2013 at 12:48 pm
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 ?
November 25, 2013 at 1:22 pm
john.chiu (11/25/2013)
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 ?
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
SELECT getdate(),'MyTable',MAX(RowVersionColumns) As Val FROM MyTable
that needs to be stuffed into a history table, so that later you can do something like this:
SELECT getdate(),'MyTable',MAX(RowVersionColumn) As Val ,COUNT(*) As TheCount FROM MyTable WHERE RowVersionColumns > (SELECT Val FROM MyHistoryTable WHERE TableName = 'MyTable')
if the column datatype is datetime, then it's a little simpler; but it's still repeated for 500 tables
SELECT getdate(),'MyTable',COUNT(LastUpdatedColumn) As Val FROM MyTable WHERE LastUpdatedColumn BETWEEN DATEADD(hour, -1,getdate()) AND getdate()
Lowell
November 25, 2013 at 1:32 pm
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.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 25, 2013 at 1:43 pm
ScottPletcher (11/25/2013)
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.
doh! i knew about that view too, used it before for last accessed data;
i should have remembered, great job Scott.
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply