check how many rows delete updated per day and store it in another table?

Question

check how many rows delete updated per day and store it in another table?

super48

SSCrazy

Points: 2682
More actions
May 8, 2015 at 6:21 am

#303724

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.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

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

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 3

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ScottPletcher SSC Guru Points: 100949 More actions · Answer 4

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) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 5

super48 (5/8/2015)
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.

Your turn. Why would you need to do such a thing?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

super48 SSCrazy Points: 2682 More actions · Answer 6

This is the interview question I faced 2 days back.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 7

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)