question

simmonnd avatar image
simmonnd asked

Audit trail trigger overloaded

Hi SSC I have created a dynamic SQL trigger to audit any changes made on a tables values. The trigger works like a charm except for the one table which is accessed repeatedly throughout the day (+/- 3000 hits per min). We need to monitor this table but the trigger is causing a lock which in turn causes our VOIP application doing all the writes to fall over. I have tried to add SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; but with no success. Any idea on how I can get around this issue? USE [ProcessDB] GO /****** Object: StoredProcedure [dbo].[CRT_TableAuditLogging] Script Date: 2014-02-12 02:35:43 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Me -- Create date: 2014/01/23 -- Description: Enable Audit logging on a spesific table -- ============================================= ALTER PROCEDURE [dbo].[CRT_TableAuditLogging] -- Add the parameters for the stored procedure here @DBName VARCHAR(200), @TableName VARCHAR(200), @TriggerName VARCHAR(200) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here DECLARE @CreateTrigger NVARCHAR(MAX) DECLARE @BigSQL VARCHAR(MAX) SET @CreateTrigger = ' IF OBJECT_ID(''''AuditTrail'''') IS NULL BEGIN CREATE TABLE [dbo].[AuditTrail]( [ID] [int] IDENTITY(1,1) NOT NULL, [TableID] [int] NOT NULL, [UserID] [int] NOT NULL, [FK_Action] [int] NOT NULL, [DateTimeStamp] [datetime] NOT NULL, [OldValue] [xml] NULL, [NewValue] [xml] NULL, CONSTRAINT [PK_AuditTrail] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END ELSE BEGIN PRINT ''''AUDIT TRACKING TABLE ALREADY EXSISTS'''' END ' SET @BigSQL = 'USE ' + @DBName + '; EXEC sp_executesql N''' + @CreateTrigger + ''''; EXEC (@BigSQL) SET @CreateTrigger = ' IF OBJECT_ID(''''' + @TriggerName + ''''', ''''TR'''') IS NOT NULL BEGIN DROP TRIGGER ' + @TriggerName + ' END ELSE BEGIN PRINT ''''The Trigger ' + @TriggerName + ' does not exsist'''' END;' SET @BigSQL = 'USE ' + @DBName + '; EXEC sp_executesql N''' + @CreateTrigger + ''''; EXEC (@BigSQL) SET @CreateTrigger = ' CREATE TRIGGER [dbo].[' + @TriggerName + '] ON [dbo].[' + @TableName + '] AFTER INSERT,UPDATE,DELETE AS BEGIN SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; DECLARE @CapturedBy INT DECLARE @DeletedCount INT DECLARE @InsertCount INT DECLARE @Action INT = 0 DECLARE @InsertedXML XML DECLARE @DeletedXML XML DECLARE @GetColumnList VARCHAR(MAX) DECLARE @ColumnCount INT = 0 DECLARE @CurrentPosition INT = 0 DECLARE @ColumnName VARCHAR(200) DECLARE @TableCompare NVARCHAR(MAX) DECLARE @SelectStatement VARCHAR(MAX) DECLARE @BuildInserted VARCHAR(MAX) DECLARE @BuildDeleted NVARCHAR(MAX) DECLARE @ParmList NVARCHAR(4000) DECLARE @IncludeColumn BIT DECLARE @AffectedTable INT DECLARE @PK_FieldList VARCHAR(MAX) GetUserID: SET @CapturedBy = CAST(REPLACE(CAST(CONTEXT_INFO() AS VARCHAR(10)),CHAR(0),'''''''') AS INT) IF(@CapturedBy) IS NULL BEGIN SELECT @CapturedBy = P.[Code] FROM sys.dm_exec_sessions AS S INNER JOIN [dbo].[Person] AS P ON S.[login_name] = P.[User_Name] WHERE S.[session_id] = @@spid END IF(@CapturedBy) IS NULL BEGIN INSERT INTO [dbo].[Person] ([Name] ,[Active] ,[User_Name] ,[Lowered_User_Name] ,[Creation_Date]) SELECT [login_name] ,''''Y'''' ,[login_name] ,LOWER([login_name]) ,GETDATE() FROM sys.dm_exec_sessions WHERE session_id = @@spid GOTO GetUserID END DECLARE @PK_Fields TABLE ([ColumnName] VARCHAR(200)) SELECT @AffectedTable = parent_obj FROM sysobjects WHERE xtype = ''''tr'''' AND name = OBJECT_NAME(@@PROCID); INSERT @PK_Fields SELECT DISTINCT C.[NAME] FROM sys.key_constraints KC INNER JOIN sys.index_columns IC ON KC.[parent_object_id] = IC.[object_id] INNER JOIN sys.columns C ON IC.[object_id] = C.[object_id] AND IC.[column_id] = C.[column_id] INNER JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] AS ISK ON KC.[Name] = ISK.[Constraint_NAME] WHERE KC.[type] = ''''PK'''' AND ISK.[TABLE_NAME] = OBJECT_NAME(@AffectedTable) SELECT @ColumnCount = COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE [TABLE_NAME] = OBJECT_NAME(@AffectedTable) SELECT @DeletedCount = COUNT(*) FROM DELETED SELECT @InsertCount = COUNT(*) FROM INSERTED EXEC [ProcessDB].[dbo].[DROPEXSISTING] ''''##InsertedStorage'''' EXEC [ProcessDB].[dbo].[DROPEXSISTING] ''''##DeletedStorage'''' SELECT * INTO ##InsertedStorage FROM INSERTED SELECT * INTO ##DeletedStorage FROM DELETED IF(@DeletedCount = 0 AND @InsertCount > 0) BEGIN SET @Action = 1; WHILE(@ColumnCount) > @CurrentPosition BEGIN SET @CurrentPosition += 1 SELECT @ColumnName = [COLUMN_NAME] FROM INFORMATION_SCHEMA.COLUMNS WHERE [TABLE_NAME] = OBJECT_NAME(@AffectedTable) AND [ORDINAL_POSITION] = @CurrentPosition SET @TableCompare = '''' SELECT @IncludeColumnOUT = CASE WHEN ['''' + @ColumnName + ''''] IS NULL THEN 0 ELSE 1 END FROM ##InsertedStorage'''' SET @ParmList = N''''@IncludeColumnOUT BIT OUTPUT'''' EXEC sys.sp_executesql @TableCompare ,@ParmList ,@IncludeColumnOUT = @IncludeColumn OUTPUT IF(@IncludeColumn = 1) BEGIN SET @SelectStatement = ISNULL(@SelectStatement,'''''''') + ''''['''' + @ColumnName + ''''], ''''; DELETE FROM @PK_Fields WHERE [ColumnName] = @ColumnName END END END IF(@DeletedCount > 0 AND @InsertCount > 0) BEGIN SET @Action = 2; WHILE(@ColumnCount) > @CurrentPosition BEGIN SET @CurrentPosition += 1 SELECT @ColumnName = [COLUMN_NAME] FROM INFORMATION_SCHEMA.COLUMNS WHERE [TABLE_NAME] = OBJECT_NAME(@AffectedTable) AND [ORDINAL_POSITION] = @CurrentPosition SET @TableCompare = '''' SELECT ['''' + @ColumnName + ''''] INTO #'''' + @ColumnName + '''' FROM ##InsertedStorage EXCEPT SELECT ['''' + @ColumnName + ''''] FROM ##DeletedStorage'''' EXEC(@TableCompare) IF(@@ROWCOUNT > 0) BEGIN SET @SelectStatement = ISNULL(@SelectStatement,'''''''') + ''''['''' + @ColumnName + ''''], ''''; DELETE FROM @PK_Fields WHERE [ColumnName] = @ColumnName END END END IF(@DeletedCount > 0 AND @InsertCount = 0) BEGIN SET @Action = 3 WHILE(@ColumnCount) > @CurrentPosition BEGIN SET @CurrentPosition += 1 SELECT @ColumnName = [COLUMN_NAME] FROM INFORMATION_SCHEMA.COLUMNS WHERE [TABLE_NAME] = OBJECT_NAME(@AffectedTable) AND [ORDINAL_POSITION] = @CurrentPosition SET @TableCompare = '''' SELECT @IncludeColumnOUT = CASE WHEN ['''' + @ColumnName + ''''] IS NULL THEN 0 ELSE 1 END FROM ##DeletedStorage'''' SET @ParmList = N''''@IncludeColumnOUT BIT OUTPUT'''' EXEC sys.sp_executesql @TableCompare ,@ParmList ,@IncludeColumnOUT = @IncludeColumn OUTPUT IF(@IncludeColumn = 1) BEGIN SET @SelectStatement = ISNULL(@SelectStatement,'''''''') + ''''['''' + @ColumnName + ''''], ''''; DELETE FROM @PK_Fields WHERE [ColumnName] = @ColumnName END END END SELECT @PK_FieldList = REPLACE((SELECT ([ColumnName] + ''''|'''') FROM @PK_Fields FOR XML PATH ('''''''')),''''|'''','''','''') SET @PK_FieldList = LEFT(@PK_FieldList,LEN(@PK_FieldList) - 1) SET @SelectStatement = ''''SELECT '''' + CASE ISNULL(@PK_FieldList,''''1'''') WHEN ''''1'''' THEN '''''''' ELSE @PK_FieldList + '''','''' END + LEFT(@SelectStatement,LEN(@SelectStatement) - 1); IF(@Action < 3) BEGIN SET @BuildInserted = @SelectStatement + '''' FROM ##InsertedStorage FOR XML RAW,ELEMENTS,ROOT(''''''''Audit''''''''), TYPE''''; DECLARE @TempInsertedStorage TABLE ([Data] XML) INSERT @TempInsertedStorage EXEC (@BuildInserted) SELECT @InsertedXML = [Data] FROM @TempInsertedStorage END IF(@Action > 1) BEGIN SET @BuildDeleted = @SelectStatement + '''' FROM ##DeletedStorage FOR XML RAW,ELEMENTS,ROOT(''''''''Audit''''''''), TYPE''''; DECLARE @TempDeletedStorage TABLE ([Data] XML) INSERT @TempDeletedStorage EXEC (@BuildDeleted) SELECT @DeletedXML = [Data] FROM @TempDeletedStorage END EXEC [ProcessDB].[dbo].[DROPEXSISTING] ''''##InsertedStorage'''' EXEC [ProcessDB].[dbo].[DROPEXSISTING] ''''##DeletedStorage'''' IF(@DeletedXML IS NULL AND @InsertedXML IS NULL) GOTO EndTrigger INSERT INTO [dbo].[AuditTrail] ([TableID] ,[UserID] ,[FK_Action] ,[DateTimeStamp] ,[OldValue] ,[NewValue]) SELECT @AffectedTable ,@CapturedBy ,@Action ,GETDATE() ,@DeletedXML ,@InsertedXML EndTrigger: END' SET @BigSQL = 'USE ' + @DBName + '; EXEC sp_executesql N''' + @CreateTrigger + ''''; EXEC (@BigSQL) END
tableauditlockinglocked
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
That is an awful lot of dynamic code that you want to run at runtime when the trigger fires. You are also firing into a central audit table - if this is auditing changes on all/lots of tables then you are going to hit contention issues (as you described). I would suggest looking at isolating auditing per table and to only audit what is really necessary. I would also look at possibly moving the audit tables to separate filegroups or even a separate database. Have you looked at the in-built auditing capabilities of SQL Server using Change Data Capture? Unfotunately, this is an Enterprise Edition only feature, but may be something you could look into. Either way, auditing will obviously increase the writes in your system, regardless of which solution you choose. You will need to make sure that the inserts for the audit are done as fast as possible. The changes I suggested could help, also making sure the audit table is designed for inserts (table structure and index choices).
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The actual trigger has very little dynamic sql. The code above actually creates the audit table as well as the trigger onto the table specified in the parameter. The trigger works perfectly on all the other tables as none have high volumes like this one particular table. I've seen IBM has a add on to a trigger where you can specify the trigger only onto certain columns. Does SQL not have something similar? I know of IF UPDATE but that would still start up the trigger wont it?
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Good gosh that's a convoluted and deep trigger. How about just using Change Data Capture instead? If not, my first suggestion for tuning is to question the use of the temporary tables in this structure. That's adding a very large overhead to a trigger, which is automatically additional overhead. After that, get the execution plans and wait stats for the queries and see where the overhead lies and do traditional tuning. But, you would be better off avoiding triggers entirely.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What do you mean "Change Data Capture"? I used the temp tables to try and avoid any locking that might occur as it seems the issue might also be that the next write is happening and the record is still locked.
0 Likes 0 ·
Change Data Capture: http://technet.microsoft.com/en-us/library/bb522489(v=SQL.105).aspx But the temporary tables are extending the time and load of the trigger itself, probably expanding the amount of time of locking, depending on how the queries that pull data back out of the temp tables perform.
0 Likes 0 ·
simmonnd avatar image
simmonnd answered
I have looked into CDC and there is one huge issue with it. You cnt capture the user that made the change. I need to be able to log which user on the UI made a change which I use context_info for and also if the change is made db side which sql user made the change. Do you know if its possible for CDC to do this as all my searches has been unsuccessful.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

simmonnd avatar image
simmonnd answered
The issue with removing the temp tables is that I'm not able to do the column compare as that runs in a separate batch
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.