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, by using it 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 take up to 30 seconds for the transaction to complete which is unacceptable.

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

Solution 1

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.

Solution 2

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

Solution 3

SQL Server 2014 introduced a very interesting feature called Delayed Durability. If you can tolerate loosing a few rows in case of an catastrophic event, like a server crash, you could really boost your performance in schenarios like yours.

Delayed transaction durability is accomplished using asynchronous log writes to disk. Transaction log records are kept in a buffer and written to disk when the buffer fills or a buffer flushing event takes place. Delayed transaction durability reduces both latency and contention within the system

The database containing the table must first be altered to allow delayed durability.

ALTER DATABASE dbname SET DELAYED_DURABILITY = ALLOWED

Then you could control the durability on a per-transaction basis.

begin tran

insert into ChangeTrackingTable select * from inserted

commit with(DELAYED_DURABILITY=ON)

The transaction will be commited as durable if the transaction is cross-database, so this will only work if your audit table is located in the same database as the trigger.

There is also a possibility to alter the database as forced instead of allowed. This causes all transactions in the database to become delayed durable.

ALTER DATABASE dbname SET DELAYED_DURABILITY = FORCED

For delayed durability, there is no difference between an unexpected shutdown and an expected shutdown/restart of SQL Server. Like catastrophic events, you should plan for data loss. In a planned shutdown/restart some transactions that have not been written to disk may first be saved to disk, but you should not plan on it. Plan as though a shutdown/restart, whether planned or unplanned, loses the data the same as a catastrophic event.

This strange defect will hopefully be addressed in a future release, but until then it may be wise to make sure to automatically execute the 'sp_flush_log' procedure when SQL server is restarting or shutting down.

Solution 4

To perform asynchronous processing you can use Service Broker, but it isn't the only option, you can also use CLR objects.

The following is an example of an stored procedure (AsyncProcedure) that asynchronous calls another procedure (SyncProcedure):

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Runtime.Remoting.Messaging;
using System.Diagnostics;

public delegate void AsyncMethodCaller(string data, string server, string dbName);

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void AsyncProcedure(SqlXml data)
    {
        AsyncMethodCaller methodCaller = new AsyncMethodCaller(ExecuteAsync);
        string server = null;
        string dbName = null;
        using (SqlConnection cn = new SqlConnection("context connection=true"))
        using (SqlCommand cmd = new SqlCommand("SELECT @@SERVERNAME AS [Server], DB_NAME() AS DbName", cn))
        {
            cn.Open();
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                reader.Read();
                server = reader.GetString(0);
                dbName = reader.GetString(1);
            }
        }
        methodCaller.BeginInvoke(data.Value, server, dbName, new AsyncCallback(Callback), null);
        //methodCaller.BeginInvoke(data.Value, server, dbName, null, null);
    }

    private static void ExecuteAsync(string data, string server, string dbName)
    {
        string connectionString = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=SSPI", server, dbName);
        using (SqlConnection cn = new SqlConnection(connectionString))
        using (SqlCommand cmd = new SqlCommand("SyncProcedure", cn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@data", SqlDbType.Xml).Value = data;
            cn.Open();
            cmd.ExecuteNonQuery();
        }
    }

    private static void Callback(IAsyncResult ar)
    {
        AsyncResult result = (AsyncResult)ar;
        AsyncMethodCaller caller = (AsyncMethodCaller)result.AsyncDelegate;
        try
        {
            caller.EndInvoke(ar);
        }
        catch (Exception ex)
        {
            // handle the exception
            //Debug.WriteLine(ex.ToString());
        }
    }
}

It uses asynchronous delegates to call SyncProcedure:

CREATE PROCEDURE SyncProcedure(@data xml)
AS
  INSERT INTO T(Data) VALUES (@data)

Example of calling AsyncProcedure:

EXEC dbo.AsyncProcedure N'<doc><id>1</id></doc>'

Unfortunatelly, the assembly requires UNSAFE permission.

Solution 5

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

View more solutions

Comments

[dkretz]

But then you're still breaking transaction control.

[Rob Garrison]

You commented above that using Service Broker is "still breaking transaction control." I haven't used Service Broker, but wouldn't it be transactional?

[Rob Garrison]

Can someone who really understands Service Broker explain whether the comment above ("breaking transaction control") is true?

[dkretz]

In order to commit or roll back a transaction, you must wait until everything has succeeded (to commit), or something has failed (to roll back). Asynchronous means you don't wait for it to finish to continue the rest of the logic.

[Rob Garrison]

But you've committed the data to Service Broker's queue, and that queue itself is reliable. I guess it could fail after being successfully written to SB's queue, but that seems like a different issue. It's an interesting question.

[Rob Garrison]

I apologize for splitting this conversation between two comment trails. I would think that if SB can allow a rollback from its queue, then that would be transactional. Once you've written it to the queue, you consider it successful. You make a good point, but I would see it as more of a design/definition issue (assuming that the write to the SB queue can be rolled back as part of the overall transaction).

[Sean Reilly]

If you rollback the transaction, it reverts the "send to queue". If an error occurs processing the queue, the processor can send a reply to the original message. Asynchronous processing isn't exactly like traditional sql, but you have all the tools you need for reliable processing.

[Rob Garrison]

Thanks for the interesting discussion.