Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Collect tables have most row changed during specfic period Expand / Collapse
Author
Message
Posted Monday, November 25, 2013 12:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 25, 2013 2:17 PM
Points: 1, Visits: 4
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 ?
Post #1517434
Posted Monday, November 25, 2013 1:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:38 AM
Points: 12,880, Visits: 31,798
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1517442
Posted Monday, November 25, 2013 1:32 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 1,960, Visits: 2,894
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1517444
Posted Monday, November 25, 2013 1:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:38 AM
Points: 12,880, Visits: 31,798
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1517450
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse