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