Trigger Performance: Blocks and Deadlocks

Question

Post reply

Trigger Performance: Blocks and Deadlocks

am-244616

SSCarpal Tunnel

Points: 4936
More actions
April 1, 2010 at 2:50 pm

#138044

Guys,
I have to track all the DMLs on bunch of tables to see when the data got modified.
I have come up with two options
Option 1
TRACK table has been created and each insert, update, delete corresponding trigger on
table registers an entry into the table with PK_KEY (PRIMARY KEY OF TABLE), TAB_NAME
(TABLE NAME), DML_TYP ('D' FOR DELETE, 'I' FOR INSERT, 'U' FOR UPDATE)
Below is the Update trigger
CREATE TRIGGER [dbo].[TRG_U_TRACK_TABLE]
ON [dbo].
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
SET NOCOUNT ON
IF TRIGGER_NESTLEVEL() > 1
RETURN
INSERT
INTO TRACK
(
PK_KEY,
DML_TYP,
TAB_NAME
)
SELECT table_id,'U','TABLE' FROM INSERTED
END
GO
Option 2
For any DML I update DATE_TIME_MOD on the table
However, my question which is the better option in an high volume OLTP envrionment.
I believe option 2 of updating itself for any DMLs is prone to blocking and deadlocks for
mass updates. In terms of transactions how does the trigger updates on the table itself
work as compared to inserting rows into another table by trigger to track DML operations.
Any suggestions/inputs would help
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

If I am understanding your post correctly then it ties into a responce I just posted for another thread.

the trigger will only fire once per update so during mass updates you will only get one row.

based on other threads and based on my limited knowledge to ackomplish that you would have to use a cursor which of course affects performance.

here is a link to the thread I was reading for the other reply.

http://www.sqlservercentral.com/Forums/Topic730493-338-1.aspx

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

WayneS SSC Guru Points: 95460 More actions · Answer 2

am-244616 (4/1/2010)
Guys,
I have to track all the DMLs on bunch of tables to see when the data got modified.
I have come up with two options
Option 1
TRACK table has been created and each insert, update, delete corresponding trigger on
table registers an entry into the table with PK_KEY (PRIMARY KEY OF TABLE), TAB_NAME
(TABLE NAME), DML_TYP ('D' FOR DELETE, 'I' FOR INSERT, 'U' FOR UPDATE)
Option 2
For any DML I update DATE_TIME_MOD on the table
However, my question which is the better option in an high volume OLTP envrionment.
I believe option 2 of updating itself for any DMLs is prone to blocking and deadlocks for
mass updates. In terms of transactions how does the trigger updates on the table itself
work as compared to inserting rows into another table by trigger to track DML operations.
Any suggestions/inputs would help
Thanks

Option 1 would provide more of an audit trail. You could even add all the fields of the table, and insert from both the inserted and deleted tables, showing both the before and after effects of the DML operation. If the table being inserted into only has a PK on an identity column, you should be pretty happy with the performance of it.

Option 2 should not be a problem with blocking since you're just updating the records that were just updated... they should still be unlocked. However, of the two options, this would be more prone to blocking than option 1.

Realize that if you're updating a lot of rows in one DML operation, you have the potential for your locks to escalate, potentially all the way to a table lock, which would hinder other DML operations going on at the same time.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 3

Dan.Humphries (4/1/2010)

If I am understanding your post correctly then it ties into a responce I just posted for another thread.

the trigger will only fire once per update so during mass updates you will only get one row.

based on other threads and based on my limited knowledge to ackomplish that you would have to use a cursor which of course affects performance.

here is a link to the thread I was reading for the other reply.

http://www.sqlservercentral.com/Forums/Topic730493-338-1.aspx%5B/quote%5D

Dan, please see my reply to you at the other thread: http://www.sqlservercentral.com/Forums/FindPost895329.aspx

This other thread also shows how to PROPERLY do a trigger during a mass update (defined as a single DML statement that affect > 1 row) WITHOUT using a cursor.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Paul White SSC Guru Points: 150467 More actions · Answer 4

Setting up a robust and efficient auditing scheme can be a lot of work, and hard to get right.

If you are planning to upgrade to SQL Server 2008 any time soon (and there are many good reasons to), take a look at the following link:

Change Data Capture

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi