Execute SQL Asynchronously or change locking from Trigger

I have a complex unit of work from an application that might commit changes to 10-15 tables as a single transaction. The unit of work executes under snapshot isolation.

Some of the tables have a trigger which executes a stored procedure to log messages into a queue. The message contains the Table Name, Key and Change Type. This is necessary to provide backwards compatibility with SQL2005, I can’t use the built in queuing.

The problem is I am getting blocking and time-outs in the queue writing stored procedure. I either get a message saying:

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.tblObjectChanges' directly or indirectly in database 

or I get a timeout writing to that table.

Is there a way to change the transaction isolation of the particular call to (or within) the stored procedure that does the message queue writing, from within the trigger? As a last resort, can I make the call to the delete or update parts of the stored procedure run asynchronously?

Here is the SQL for the Stored Procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_NotifyObjectChanges]
    @ObjectType varchar(20),
    @ObjectKey int,
    @Level int, 
    @InstanceGUID varchar(50),
    @ChangeType int = 2

AS

SET NOCOUNT ON

DECLARE @ObjectChangeID int

--Clean up any messages older than 10 minutes
DELETE from tblObjectChanges Where CreatedTime < DATEADD(MINUTE, -10, GetDate())

--If the object is already in the queue, change the time and instanceID
SELECT @ObjectChangeID =  [ObjectChangeID]  FROM tblObjectChanges WHERE [ObjectType] = @ObjectType AND [ObjectKey] = @ObjectKey AND [Level] = @Level

IF NOT @ObjectChangeID is NULL
BEGIN
    UPDATE [dbo].[tblObjectChanges] SET
        [CreatedTime] = GETDATE(), InstanceGUID = @InstanceGUID 
    WHERE
        [ObjectChangeID] = @ObjectChangeID
END
ELSE
BEGIN
    INSERT INTO [dbo].[tblObjectChanges] (
        [CreatedTime],
        [ObjectType],
        [ObjectKey],
        [Level],
        ChangeType,
        InstanceGUID 
    ) VALUES (
        GETDATE(),
        @ObjectType,
        @ObjectKey,
        @Level,
        @ChangeType,
        @InstanceGUID 
    )
END

Definition of tblObjectChanges:

CREATE TABLE [dbo].[tblObjectChanges](
    [CreatedTime] [datetime] NOT NULL,
    [ObjectType] [varchar](20) NOT NULL,
    [ObjectKey] [int] NOT NULL,
    [Rowversion] [timestamp] NOT NULL,
    [Level] [int] NOT NULL,
    [ObjectChangeID] [int] IDENTITY(1,1) NOT NULL,
    [InstanceGUID] [varchar](50) NULL,
    [ChangeType] [int] NOT NULL,
 CONSTRAINT [PK_tblObjectChanges] PRIMARY KEY CLUSTERED 
(
    [ObjectChangeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

Answer

This line is almost certainly your problem:

DELETE from tblObjectChanges Where CreatedTime < DATEADD(MINUTE, -10, GetDate())

There are two BIG problems with this statement. First, according to your table definition, CreatedTime is not indexed. This means that in order to execute this statement, the entire table must be scanned, and that will cause the entire table to be locked for the duration of whatever transaction this happens to be a part of. So put an index on this column.

The second problem, is that even with an index, you really shouldn’t be performing operational maintenance tasks like this from within a trigger. Besides slowing down the OLTP transactions that have to execute it, this statement only really needs to be executed once every 5-10 minutes. Instead, you are executing it any time (and every time) any of these tables are modified. That is a lot of additional load that gets worse as your system gets busier.

A better approach would be to take this statement out of the triggers entirely, and instead have a SQL Agent Job that runs every 5-10 minutes to execute this clean-up operation. If you do this along with adding the index, most of your problems should disappear.

---

An additional problem is this statement:

SELECT @ObjectChangeID =  [ObjectChangeID]  FROM tblObjectChanges WHERE [ObjectType] = @ObjectType AND [ObjectKey] = @ObjectKey AND [Level] = @Level

Unlike the first statement above, this statement belongs in the trigger. However, like the first statement, it too will have (and cause) serious performance and locking issues under load, because again, according to your posted table definition, none of the columns being searched are indexed.

The solution again is to put an additional index on these columns as well.