May 11, 2006 at 7:55 am
I have been asked to determine how many INSERTs, UPDATEs, and DELETEs are done per day on the tables in a particular database. How can I do this? Is there some sort of monitor I can use?
May 11, 2006 at 8:27 am
SQL Profiler is your friend.
May 11, 2006 at 9:03 am
The best way depends on the answers to various questions;
1. is this a one of request (are you certain. it is a lot less effort to record this info and have it on hand for the next request)
2. is the table only accessed through known methods e.g do applications all call the same stored procedures or are users allowed ad hoc access?
if this is a repeat request and the table is accessed through known sp (or the resutls you want to record are) then you can catch @@rowcount in those procedures and insert that value into another table. I do this for a lot of processes and i insert the time, nest level, procedure etc into a central logging table. I can then see historically how long my procedures have taken, if the time is increasing, if the increase is linear/proportional to rows etc.
I check this table periodically and it identifes when I should archive/partion tables/optimise code etc
For any ad hoc or uncontrolled access you are best using sql profiler. My only complaint with profiler is that it needs to be manually started after any network/power outage or reboot
May 12, 2006 at 3:09 am
You could also consider the use of triggers with code like (I'll assume you know what to do once you've got the number of rows inserted,updated or deleted):
select @numberOfRowsInserted = count(1) from inserted
select @numberOfRowsUpdated = count(1) from inserted
select @numberOfRowsDeleted = count(1) from deleted
Disadvantage: if you insert the number of rows inserted/updated/deleted into an audit table, your primary DML action will fail if somehow the insert into the audit tabel fails.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply