May 8, 2015 at 6:21 am
how to track how many rows updated or deleted per day in a single table
and load the information in another table .
please help with this.
May 8, 2015 at 6:27 am
SQL doesn't keep track of insert or deletion rates, so you'll need a trigger on the table or something like Change Data Capture or Change Tracking to do this.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 8, 2015 at 7:12 am
You could compute changes to the clustered index as updates to the table (insert/delete/update).
I have a script here: http://spaghettidba.com/2015/04/20/tracking-table-usage-and-identifying-unused-objects/
Save index usage stats on a daily basis and compare with the previous day. Not exactly what you asked, but pretty close.
-- Gianluca Sartori
May 8, 2015 at 8:32 am
All good choices. Another way would be to capture extended events (or trace) for all insert/update/delete commands against the table. It won't show rows affected, but you can tell what has been accessed.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 8, 2015 at 9:48 am
SQL does sort of track modification counts (for indexes, not heaps), but the data is cumulative and does necessarily persist. Look at view:
sys.dm_db_index_operational_stats
However, while an index is continuously active, the view should give you what you want. Capture the stats you want compare periodically, then compare one time snapshot to another to see what has occurred during that time interval. You can tell from the stats themselves whether such a comparison is valid.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
May 8, 2015 at 6:59 pm
super48 (5/8/2015)
how to track how many rows updated or deleted per day in a single tableand load the information in another table .
please help with this.
Your turn. Why would you need to do such a thing?
--Jeff Moden
May 8, 2015 at 10:45 pm
This is the interview question I faced 2 days back.
May 9, 2015 at 7:43 am
Thanks. I guess I should ask the interviewers why they would want to do such a thing without it being a full blown audit of the table with a trigger to capture the changes. 😉
--Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply