SQL Data Counter

Question

Post reply

SQL Data Counter

rishgup

Ten Centuries

Points: 1106
More actions
October 22, 2008 at 11:37 am

#124424

Is there any procedure or function to find out number of Insertion, Updation and Deletion occurs on database. I just want to count number of insert, update and delete.
Thanks
Rishabh

Viewing 15 posts - 1 through 15 (of 25 total)

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

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 1

Interesting problem.

I have a suggested approach:

Write an after trigger that adds the event's rowcounts to a counter table.

This will only get you counts AFTER the trigger is in place, but its something.

Either that or upgrade to 2008 and use change data capture.

~SQLBOT

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

I'd look at a trace, grab just insert/update/delete events to a table.

Then roll those up periodically to another table and delete them from the trace table.

rishgup Ten Centuries Points: 1106 More actions · Answer 3

Steve Jones - Editor (10/22/2008)
I'd look at a trace, grab just insert/update/delete events to a table.
Then roll those up periodically to another table and delete them from the trace table.

Steve, Can you please explain me the trace process?

rishgup Ten Centuries Points: 1106 More actions · Answer 4

rishgup

Ten Centuries

Points: 1106

October 23, 2008 at 8:39 am

#888641

Any suggestions????

rishgup Ten Centuries Points: 1106 More actions · Answer 5

SQLBOT (10/22/2008)
Interesting problem.
I have a suggested approach:
Write an after trigger that adds the event's rowcounts to a counter table.
This will only get you counts AFTER the trigger is in place, but its something.
Either that or upgrade to 2008 and use change data capture.
~SQLBOT

SQL BOT can you write a Sample trigger for me. I am very confused. Help would be appreciated.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

Take a look at the DMV's:

sys.dm_db_index_operational_stats

sys.dm_db_index_usage_stats

You should be able to get the information you want by looking at only the heaps and clustered indexes. That is, indexes with an index_id = 0 or 1.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

rishgup Ten Centuries Points: 1106 More actions · Answer 7

I have database which has 2 tables tblCount with counter_ins field and tblEmployee with emp_name, emp_last, emp_add adn emp_ph fields.

There is no indexing on tables. And I want to find out the count when ever there is insertion of record in tblEmployee.

I was trying to create a Trigger which increment the count_inc filed whenever a new record in added on tblEmployee

CREATE TRIGGER InsertEmployee

ON tblEmployee

AFTER INSERT

AS

BEGIN

UPDATE tblcount

SET count_ins = count_ins + 1

FROM tblCount

END

but its not working for me. It gives an error message. Can anyone tell me what wrong I am doing with trigger?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

rishgup (10/23/2008)
I have database which has 2 tables tblCount with counter_ins field and tblEmployee with emp_name, emp_last, emp_add adn emp_ph fields.
There is no indexing on tables. And I want to find out the count when ever there is insertion of record in tblEmployee.
I was trying to create a Trigger which increment the count_inc filed whenever a new record in added on tblEmployee
CREATE TRIGGER InsertEmployee
ON tblEmployee
AFTER INSERT
AS
BEGIN
UPDATE tblcount
SET count_ins = count_ins + 1
FROM tblCount
END
but its not working for me. It gives an error message. Can anyone tell me what wrong I am doing with trigger?

Can't see the error message from here... would you please post it? 😉

--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)

rishgup Ten Centuries Points: 1106 More actions · Answer 9

I figure out the error in my trigger. That was silly error. now Trigger is working fine and I am getting the result which I was looking for

But my only worry is database performance???

As I have 30 tables and for each table I created 3 triggers one each for insert, update and delete. over all 90 triggers. Will it effectDatabase Performance?

Thanks

Rishabh

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 10

rishgup (10/24/2008)
I figure out the error in my trigger. That was silly error. now Trigger is working fine and I am getting the result which I was looking for
But my only worry is database performance???
As I have 30 tables and for each table I created 3 triggers one each for insert, update and delete. over all 90 triggers. Will it effectDatabase Performance?
Thanks
Rishabh

It might effect. It depends on the rate of the data modification statements. According to the trigger that you posted, all of the triggers are updating the same record on a single table. If you have lots of simultaneous data modification statements, the triggers might cause blocking problem. If the data modification statements’ rate is not that high, then you have nothing to worry about.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

rishgup Ten Centuries Points: 1106 More actions · Answer 11

wrote:

All triggers are updating DIFFERENT fields but in SAME table.

You can say each trigger update each field. Say You have table employee and address, whenever data is inserted into the table employee only emp_ins field will be incremented by 1 in the another table Counter. And if data is updated in address table field named add_upd will be incremented.

Rishabh

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 12

rishgup (10/24/2008)
wrote:
All triggers are updating DIFFERENT fields but in SAME table.
You can say each trigger update each field. Say You have table employee and address, whenever data is inserted into the table employee only emp_ins field will be incremented by 1 in the another table Counter. And if data is updated in address table field named add_upd will be incremented.
Rishabh

When SQL Server updates a row, it locks the row so only one session will be able to update it. If session A is updating a row, and session B wants to update the same row, then session B will have to wait until session A will finish it’s transaction. It doesn’t matter if each session tries to update a different column. SQL Server locking mechanism smallest unit is row and not column. As long as all the triggers are trying to update the same row, they can block each other.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

rishgup (10/24/2008)
You can say each trigger update each field. Say You have table employee and address, whenever data is inserted into the table employee only emp_ins field will be incremented by 1 in the another table Counter. And if data is updated in address table field named add_upd will be incremented.
Rishabh

THAT is a "Sequence table update" trigger and, depending on how it is written, can be a HUGE and certain source of hundreds of deadlocks per day. Further, if improperly written, it can allow things to get out of sync.

Please post the code for one of these triggers, the schema for the sequence table (target of the update of the trigger), and the data the sequence table contains so that we might be able to help you avoid the "SQL Deathtrap" this type of trigger is commonly associated with.

How do I know this... heh... thanks to a 3rd party and an average of 640 deadlocks per day as well as designing the fix for it, I think I can help. 🙂