Asynchronous Triggers in SQL Server 2005/2008

Question

I have triggers that manipulate and insert a lot of data into a Change tracking table for audit purposes on every insert, update and delete.

This trigger does its job very well, in other words, we are able to log the desired oldvalues/newvalues as per the business requirements for every transaction.

However in some cases where the source table has a lot columns, it can up to 30 seconds for the transaction to complete. This is the unacceptable part.

Is there a way to make the trigger run asynchronously? Any examples.

Answer 1

Not that I know of, but are you inserting values into the Audit table that also exist in the base table? If so, you could consider tracking just the changes. Therefore an insert would track the change time, user, extra and a bunch of NULLs (in effect the before value). An update would have the change time, user etc and the before value of the changed column only. A delete has the change at, etc and all values.

Also, do you have an audit table per base table or one audit table for the DB? Of course the later can more easily result in waits as each transaction tries to write to the one table.

Answer 2

I wonder if you could tag a record for the change tracking by inserting into a "too process" table including who did the change etc etc.

Then another process could come along and copy the rest of the data on a regular basis.

Answer 3

There's a basic conflict between "does its job very well" and "unacceptable", obviously.

It sounds to me that you're trying to use triggers the same way you would use events in an OO procedural application, which IMHO doesn't map.

I would call any trigger logic that takes 30 seconds - no, more that 0.1 second - as disfunctional. I think you really need to redesign your functionality and do it some other way. I'd say "if you want to make it asynchronous", but I don't think this design makes sense in any form.

As far as "asynchronous triggers", the basic fundamental conflict is that you could never include such a thing between BEGIN TRAN and COMMIT TRAN statements because you've lost track of whether it succeeded or not.

Answer 4

You can't make the trigger run asynchronously, but you could have the trigger synchronously send a message to a SQL Service Broker queue. The queue can then be processed asynchronously by a stored procedure.

Answer 5

I suspect that your trigger is of of these generic csv/text generating triggers designed to log all changes for all table in one place. Good in theory (perhaps...), but difficult to maintain and use in practice.

If you could run asynchronously (which would still require storing data somewhere for logging again later), then you are not auditing and neither do have history to use.

Perhaps you could look at the trigger execution plan and see what bit is taking the longest?

Can you change how you audit, say, to per table? You could split the current log data into the relevant tables.

Answer 6

Create history table(s). While updating (/deleting/inserting) main table, insert old values of record (deleted pseudo-table in trigger) into history table; some additional info is needed too (timestamp, operation type, maybe user context). New values are kept in live table anyway.

This way triggers run fast(er) and you can shift slow operations to log viewer (procedure).

Answer 7

these articles show how to use service broker for async auditing and should be useful:

Centralized Asynchronous Auditing with Service Broker

Service Broker goodies: Cross Server Many to One (One to Many) scenario and How to troubleshoot it

Answer 8

If you show us your trigger, we could possibly make suggestions to speed it up.