Collect tables have most row changed during specfic period

Question

Collect tables have most row changed during specfic period

john.chiu

Grasshopper

Points: 11
More actions
November 25, 2013 at 12:48 pm

#395894

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 ?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ScottPletcher SSC Guru Points: 100940 More actions · Answer 2

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.

Lowell SSC Guru Points: 323504 More actions · Answer 3

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!