September 7, 2019 at 9:26 pm
BWAAA-HAAAAA!!!!... I thought you said you had "a lot to think about". 😀 I didn't know you were going to jump right in and do it even on a dev box but now you know why I recommended partitioning as a part of this whole process. To quote a part of my own previous post (the important part has been bolded and colored Red)...
So the bottom line is, yes, we can do LOB out-of-row tricks and a whole lot more and, yes, all that still needs to be done BUT I'd do that as a part of partitioning the table.
... and that DOES take some serious and well thought out planning (heh... "a lot to think about". 😀 ) .
The really good part about this (and I mean that seriously and with no slamming intended) is that you now have some great experience that you might not otherwise have had the chance to see personally. That also means that you've accidentally become an expert in what can happen with such large tables and will now be able to advise others in the future. That's actually something to be coveted because that kind of experience simply doesn't grow on trees.
As you've just found out, we need to plan on not blowing out the log file with updates (it can actually be done with almost zero log file growth). We need to plan on the system being available almost 100% of the time and most certainly not being unavailable for 6 hours. We need to plan on restores both for Disaster Recovery (to get back in business as quickly as possible should the need arise) and (as a serious side benefit and in the process) make it more feasible to make copies of production without the need to make full size copies containing a bunch of audit table information that would never be used in a copy, how to take advantage of the partitioning to greatly reduce backups, etc, etc.
Before we can plan all of this, we need some more information. Making sure that the "current" database is wherever the "live" version of the Audit table is, please run the two following pieces of code on your "live" box (we need to know what's there, not whats on the dev box) and post the results, please. If you'd rather not make any of this information public, then PM me and we'll work around that.
Also, if you want to take the time, you could paste the results into a multi-tab spreadsheet and attach the spreadsheet, if you'd like.
--===== Determine the spread of rows in the Audit table by year and month.
SELECT YearStartDT = DATEPART(yy,AuditDate)
,MonthStartDT = CASE
WHEN GROUPING(DATEPART(yy,AuditDate)) = 1 THEN 'Grand Total'
WHEN GROUPING(DATEDIFF(mm,0,AuditDate)) = 1 THEN 'Year SubTotal'
ELSE CONVERT(CHAR(13),DATEADD(mm,DATEDIFF(mm,0,AuditDate),0),112)
END
,RowCnt = COUNT(*)
FROM dbo.[Audit]
GROUP BY DATEPART(yy,AuditDate), DATEDIFF(mm,0,AuditDate) WITH ROLLUP
ORDER BY GROUPING(DATEPART(yy,AuditDate)) ,DATEPART(yy,AuditDate)
,GROUPING(DATEDIFF(mm,0,AuditDate)),DATEDIFF(mm,0,AuditDate)
;
--===== Get some important stats to make some estimates with.
SELECT database_id
,object_id
,index_id
,partition_number
,alloc_unit_type_desc
,avg_fragmentation_in_percent
,page_count
,SizeMB = page_count/128
,avg_page_space_used_in_percent
,record_count
,compressed_page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.Audit'),NULL,NULL,'SAMPLED')
;
--===== Determine the amount of freespace by drive on the server.
-- We need all the drives in case we need some temporary space to work with
-- which will be returned as free space when we're done. We might not need
-- any temporary space if there's enough on the database related drives but
-- this also checks for that, as well.
EXEC MASTER..xp_fixeddrives
;
To be sure, we can do all of this without partitioning but it would be a real shame not to. Specifically, I'm recommending a "Partitioned VIEW" rather than a "Partitioned TABLE" because there's a serious advantage to be had for making greatly reduced copies of the "live" data.
Once I have that information, I can develop a plan for you and make this as painless for you and the system while keeping the entire evolution as online as possible.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 8, 2019 at 7:31 pm
For those that may be following this thread...
Mike provided the results for the code I asked for in a PM. He didn't want to reveal monthly counts, drive letters, etc, to the general public and I certainly can't blame him for that.
To update what has taken place behind the scenes between Mike and I (and I checked with him prior to publishing this post), it turns out that there was a bit of a misread on the LOGICAL fragmentation of this table. It's virtually not fragmented at all (0.50, which isn't 50%... that's less than 1% and a fair number of people make the same misobservation when looking at a single row from physical stats). The good part about that is 1) it's what I expected if the were no expansive updates (as Mike stated) and 2) it proves there are no expansive updates, which would be a pain to find and and a real pain to repair in the code.
Also, there were no "Row Overflow" allocations and there were no "LOB" allocations. Everything fits in rows.
That's not necessarily a good thing here because there's also some "Physical Fragmentation" going on (which I'll refer to as "Page Density" because it's easier to say with only one cup of coffee in my system 😀 ). The Page Density on this table is only 87% due to "trapped short rows". While that doesn't sound so bad, the inverse is that we're wasting an average of 13% of disk space on a near 348GB Clustered Index, which is a waste of about 45GB. The culprit is the SQLStatement VARCHAR(MAX) column.
There's also the issue of backup times (which will also affect "get back in business" times for restores). Mike and I still need to do a test to see if forcing the VARCHAR(MAX) column out of row will make things much better (it depends on how much space is wasted on the out of row structure because we're on the border line of it not being worthwhile) but the partitioning is still well worth the effort to reel-in the backup times and the related amount of time it takes to "get back in business" during a DR restore while waiting for a bunch of non-critical audit log data to restore (the Partitioned VIEW will make that a non-problem if we can swing it in the "live" system). It will also make removing legacy data (really old data) a whole lot less painful in the future when it finally does come to that.
Another issue is the performance of INSERTs to this table. I can't help but suspect a CLR trigger but I've asked him to post the trigger (if it's atrigger) that so we can have a look at that, as well. Hopefully, it IS a trigger that's feeding the table because that will be relatively easy to fix if it is a problem rather than trying to find all of the code that's fixing it.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 8, 2019 at 7:53 pm
Here are the Update, Insert and Delete triggers for one of the smaller tables in the database.
------------------------------------------------------------------------------------------
----- INSERT -----------------------------------------------------------------------------
------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[o_choice_Audit_Insert] ON [dbo].[o_choice]
AFTER Insert
NOT FOR REPLICATION AS
SET NoCount On
SET ARITHABORT ON
-- generated by AutoAudit on Mar 1 2011 12:29PM
-- created by Paul Nielsen
-- www.SQLServerBible.com
DECLARE @AuditTime DATETIME
SET @AuditTime = GetDate()
Begin Try
-- capture SQL Statement
DECLARE @ExecStr varchar(50), @UserSQL nvarchar(max)
DECLARE @inputbuffer TABLE
(EventType nvarchar(30), Parameters int, EventInfo nvarchar(max))
SET @ExecStr = 'DBCC INPUTBUFFER(@@SPID) with no_infomsgs'
INSERT INTO @inputbuffer
EXEC (@ExecStr)
SELECT @UserSQL = EventInfo FROM @inputbuffer
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, NewValue, RowVersion)
SELECT @AuditTime, COALESCE(Inserted.CreatedBy, Suser_SName()), APP_NAME(), Host_Name(), 'dbo.o_choice', 'i', @UserSQL,
Inserted.[o_choice_id],
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Order Number for an Order Detail Line)
'[o_choice_id]', Cast(Inserted.[o_choice_id] as VARCHAR(50)), 1
FROM Inserted
WHERE Inserted.[o_choice_id] is not null
-----
-- Update the Created and Modified columns
UPDATE [dbo].[o_choice]
SET Created = @AuditTime,
CreatedBy = COALESCE(Inserted.CreatedBy, Suser_SName()),
Modified = @AuditTime,
ModifiedBy = COALESCE(Inserted.CreatedBy, Suser_SName()),
[RowVersion] = 1
FROM [dbo].[o_choice]
JOIN Inserted
ON [o_choice].[o_choice_id] = Inserted.[o_choice_id]
-----
End Try
Begin Catch
DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;
SET @ErrorMessage = ERROR_MESSAGE();
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorState = ERROR_STATE();
RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState) with log;
End Catch
GO
EXEC sp_settriggerorder @triggername=N'[dbo].[o_choice_Audit_Insert]', @order=N'Last', @stmttype=N'INSERT'
------------------------------------------------------------------------------------------
----- UPDATE -----------------------------------------------------------------------------
------------------------------------------------------------------------------------------
/****** Object: Trigger [dbo].[o_choice_Audit_Update] Script Date: 9/8/2019 12:44:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[o_choice_Audit_Update] ON [dbo].[o_choice]
AFTER Update
NOT FOR REPLICATION AS
SET NoCount On
-- generated by AutoAudit on Mar 1 2011 12:29PM
-- created by Paul Nielsen
-- www.SQLServerBible.com
DECLARE @AuditTime DATETIME, @IsDirty BIT
SET @AuditTime = GetDate()
SET @IsDirty = 0
Begin Try
-- capture SQL Statement
DECLARE @ExecStr varchar(50), @UserSQL nvarchar(max)
DECLARE @inputbuffer TABLE
(EventType nvarchar(30), Parameters int, EventInfo nvarchar(max))
SET @ExecStr = 'DBCC INPUTBUFFER(@@SPID) with no_infomsgs'
INSERT INTO @inputbuffer
EXEC (@ExecStr)
SELECT @UserSQL = EventInfo FROM @inputbuffer
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, NewValue, RowVersion)
SELECT @AuditTime, COALESCE(Inserted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(), 'dbo.o_choice', 'u', @UserSQL,
Convert(VARCHAR(50), Inserted.[o_choice_id]),
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Order Number for an Order Detail Line)
'[company_id]', Convert(VARCHAR(50), Deleted.[company_id]), Convert(VARCHAR(50), Inserted.[company_id]),
DELETED.Rowversion + 1
FROM Inserted
JOIN Deleted
ON Inserted.[o_choice_id] = Deleted.[o_choice_id]
AND ISNULL(Inserted.[company_id],0) <> ISNULL(Deleted.[company_id],0)
IF @@RowCount > 0 SET @IsDirty = 1
-----
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, NewValue, RowVersion)
SELECT @AuditTime, COALESCE(Inserted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(), 'dbo.o_choice', 'u', @UserSQL,
Convert(VARCHAR(50), Inserted.[o_choice_id]),
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Order Number for an Order Detail Line)
'', Convert(VARCHAR(50), Deleted.), Convert(VARCHAR(50), Inserted.),
DELETED.Rowversion + 1
FROM Inserted
JOIN Deleted
ON Inserted.[o_choice_id] = Deleted.[o_choice_id]
AND ISNULL(Inserted.,'') <> ISNULL(Deleted.,'')
IF @@RowCount > 0 SET @IsDirty = 1
-----
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, NewValue, RowVersion)
SELECT @AuditTime, COALESCE(Inserted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(), 'dbo.o_choice', 'u', @UserSQL,
Convert(VARCHAR(50), Inserted.[o_choice_id]),
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Order Number for an Order Detail Line)
'[descript]', Convert(VARCHAR(50), Deleted.[descript]), Convert(VARCHAR(50), Inserted.[descript]),
DELETED.Rowversion + 1
FROM Inserted
JOIN Deleted
ON Inserted.[o_choice_id] = Deleted.[o_choice_id]
AND ISNULL(Inserted.[descript],'') <> ISNULL(Deleted.[descript],'')
IF @@RowCount > 0 SET @IsDirty = 1
-----
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, NewValue, RowVersion)
SELECT @AuditTime, COALESCE(Inserted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(), 'dbo.o_choice', 'u', @UserSQL,
Convert(VARCHAR(50), Inserted.[o_choice_id]),
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Order Number for an Order Detail Line)
'[m_type]', Convert(VARCHAR(50), Deleted.[m_type]), Convert(VARCHAR(50), Inserted.[m_type]),
DELETED.Rowversion + 1
FROM Inserted
JOIN Deleted
ON Inserted.[o_choice_id] = Deleted.[o_choice_id]
AND ISNULL(Inserted.[m_type],'') <> ISNULL(Deleted.[m_type],'')
IF @@RowCount > 0 SET @IsDirty = 1
-----
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, NewValue, RowVersion)
SELECT @AuditTime, COALESCE(Inserted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(), 'dbo.o_choice', 'u', @UserSQL,
Convert(VARCHAR(50), Inserted.[o_choice_id]),
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Order Number for an Order Detail Line)
'[amount]', Convert(VARCHAR(50), Deleted.[amount]), Convert(VARCHAR(50), Inserted.[amount]),
DELETED.Rowversion + 1
FROM Inserted
JOIN Deleted
ON Inserted.[o_choice_id] = Deleted.[o_choice_id]
AND ISNULL(Inserted.[amount],0) <> ISNULL(Deleted.[amount],0)
IF @@RowCount > 0 SET @IsDirty = 1
-----
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, NewValue, RowVersion)
SELECT @AuditTime, COALESCE(Inserted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(), 'dbo.o_choice', 'u', @UserSQL,
Convert(VARCHAR(50), Inserted.[o_choice_id]),
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Order Number for an Order Detail Line)
'[ch_sort_num]', Convert(VARCHAR(50), Deleted.[ch_sort_num]), Convert(VARCHAR(50), Inserted.[ch_sort_num]),
DELETED.Rowversion + 1
FROM Inserted
JOIN Deleted
ON Inserted.[o_choice_id] = Deleted.[o_choice_id]
AND ISNULL(Inserted.[ch_sort_num],0) <> ISNULL(Deleted.[ch_sort_num],0)
IF @@RowCount > 0 SET @IsDirty = 1
-----
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, NewValue, RowVersion)
SELECT @AuditTime, COALESCE(Inserted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(), 'dbo.o_choice', 'u', @UserSQL,
Convert(VARCHAR(50), Inserted.[o_choice_id]),
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Order Number for an Order Detail Line)
'[inactive_fl]', Convert(VARCHAR(50), Deleted.[inactive_fl]), Convert(VARCHAR(50), Inserted.[inactive_fl]),
DELETED.Rowversion + 1
FROM Inserted
JOIN Deleted
ON Inserted.[o_choice_id] = Deleted.[o_choice_id]
AND ISNULL(Inserted.[inactive_fl],0) <> ISNULL(Deleted.[inactive_fl],0)
IF @@RowCount > 0 SET @IsDirty = 1
-----
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, NewValue, RowVersion)
SELECT @AuditTime, COALESCE(Inserted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(), 'dbo.o_choice', 'u', @UserSQL,
Convert(VARCHAR(50), Inserted.[o_choice_id]),
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Order Number for an Order Detail Line)
'[system_fl]', Convert(VARCHAR(50), Deleted.[system_fl]), Convert(VARCHAR(50), Inserted.[system_fl]),
DELETED.Rowversion + 1
FROM Inserted
JOIN Deleted
ON Inserted.[o_choice_id] = Deleted.[o_choice_id]
AND ISNULL(Inserted.[system_fl],0) <> ISNULL(Deleted.[system_fl],0)
IF @@RowCount > 0 SET @IsDirty = 1
-----
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, NewValue, RowVersion)
SELECT @AuditTime, COALESCE(Inserted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(), 'dbo.o_choice', 'u', @UserSQL,
Convert(VARCHAR(50), Inserted.[o_choice_id]),
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Order Number for an Order Detail Line)
'[created_on_user_id]', Convert(VARCHAR(50), Deleted.[created_on_user_id]), Convert(VARCHAR(50), Inserted.[created_on_user_id]),
DELETED.Rowversion + 1
FROM Inserted
JOIN Deleted
ON Inserted.[o_choice_id] = Deleted.[o_choice_id]
AND ISNULL(Inserted.[created_on_user_id],0) <> ISNULL(Deleted.[created_on_user_id],0)
IF @@RowCount > 0 SET @IsDirty = 1
-----
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, NewValue, RowVersion)
SELECT @AuditTime, COALESCE(Inserted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(), 'dbo.o_choice', 'u', @UserSQL,
Convert(VARCHAR(50), Inserted.[o_choice_id]),
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Order Number for an Order Detail Line)
'[created_on_dt]', Convert(VARCHAR(50), Deleted.[created_on_dt]), Convert(VARCHAR(50), Inserted.[created_on_dt]),
DELETED.Rowversion + 1
FROM Inserted
JOIN Deleted
ON Inserted.[o_choice_id] = Deleted.[o_choice_id]
AND ISNULL(Inserted.[created_on_dt],'') <> ISNULL(Deleted.[created_on_dt],'')
IF @@RowCount > 0 SET @IsDirty = 1
-----
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, NewValue, RowVersion)
SELECT @AuditTime, COALESCE(Inserted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(), 'dbo.o_choice', 'u', @UserSQL,
Convert(VARCHAR(50), Inserted.[o_choice_id]),
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Order Number for an Order Detail Line)
'[last_updated_user_id]', Convert(VARCHAR(50), Deleted.[last_updated_user_id]), Convert(VARCHAR(50), Inserted.[last_updated_user_id]),
DELETED.Rowversion + 1
FROM Inserted
JOIN Deleted
ON Inserted.[o_choice_id] = Deleted.[o_choice_id]
AND ISNULL(Inserted.[last_updated_user_id],0) <> ISNULL(Deleted.[last_updated_user_id],0)
IF @@RowCount > 0 SET @IsDirty = 1
-----
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, NewValue, RowVersion)
SELECT @AuditTime, COALESCE(Inserted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(), 'dbo.o_choice', 'u', @UserSQL,
Convert(VARCHAR(50), Inserted.[o_choice_id]),
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Order Number for an Order Detail Line)
'[last_updated_dt]', Convert(VARCHAR(50), Deleted.[last_updated_dt]), Convert(VARCHAR(50), Inserted.[last_updated_dt]),
DELETED.Rowversion + 1
FROM Inserted
JOIN Deleted
ON Inserted.[o_choice_id] = Deleted.[o_choice_id]
AND ISNULL(Inserted.[last_updated_dt],'') <> ISNULL(Deleted.[last_updated_dt],'')
IF @@RowCount > 0 SET @IsDirty = 1
-----
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, NewValue, RowVersion)
SELECT @AuditTime, COALESCE(Inserted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(), 'dbo.o_choice', 'u', @UserSQL,
Convert(VARCHAR(50), Inserted.[o_choice_id]),
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Order Number for an Order Detail Line)
'[locked_by_user_id]', Convert(VARCHAR(50), Deleted.[locked_by_user_id]), Convert(VARCHAR(50), Inserted.[locked_by_user_id]),
DELETED.Rowversion + 1
FROM Inserted
JOIN Deleted
ON Inserted.[o_choice_id] = Deleted.[o_choice_id]
AND ISNULL(Inserted.[locked_by_user_id],0) <> ISNULL(Deleted.[locked_by_user_id],0)
IF @@RowCount > 0 SET @IsDirty = 1
-----
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, NewValue, RowVersion)
SELECT @AuditTime, COALESCE(Inserted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(), 'dbo.o_choice', 'u', @UserSQL,
Convert(VARCHAR(50), Inserted.[o_choice_id]),
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Order Number for an Order Detail Line)
'[locked_dt]', Convert(VARCHAR(50), Deleted.[locked_dt]), Convert(VARCHAR(50), Inserted.[locked_dt]),
DELETED.Rowversion + 1
FROM Inserted
JOIN Deleted
ON Inserted.[o_choice_id] = Deleted.[o_choice_id]
AND ISNULL(Inserted.[locked_dt],'') <> ISNULL(Deleted.[locked_dt],'')
IF @@RowCount > 0 SET @IsDirty = 1
-----
-----
-- Update the Modified date
IF @IsDirty = 1
UPDATE [dbo].[o_choice]
SET Modified = @AuditTime,
ModifiedBy = COALESCE(Inserted.ModifiedBy, Suser_SName()),
[RowVersion] = [o_choice].[RowVersion] + 1
FROM [dbo].[o_choice]
JOIN Inserted
ON [o_choice].[o_choice_id] = Inserted.[o_choice_id]
-----
End Try
Begin Catch
DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;
SET @ErrorMessage = ERROR_MESSAGE();
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorState = ERROR_STATE();
RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState) with log;
End Catch
GO
ALTER TABLE [dbo].[o_choice] ENABLE TRIGGER [o_choice_Audit_Update]
GO
EXEC sp_settriggerorder @triggername=N'[dbo].[o_choice_Audit_Update]', @order=N'Last', @stmttype=N'UPDATE'
GO
------------------------------------------------------------------------------------------
----- DELETE -----------------------------------------------------------------------------
------------------------------------------------------------------------------------------
/****** Object: Trigger [dbo].[o_choice_Audit_Delete] Script Date: 9/8/2019 12:47:25 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[o_choice_Audit_Delete] ON [dbo].[o_choice]
AFTER Delete
NOT FOR REPLICATION AS
SET NoCount On
-- generated by AutoAudit on Mar 1 2011 12:29PM
-- created by Paul Nielsen
-- www.SQLServerBible.com
DECLARE @AuditTime DATETIME
SET @AuditTime = GetDate()
-- capture SQL Statement
DECLARE @ExecStr varchar(50), @UserSQL nvarchar(max)
DECLARE @inputbuffer TABLE
(EventType nvarchar(30), Parameters int, EventInfo nvarchar(max))
SET @ExecStr = 'DBCC INPUTBUFFER(@@SPID) with no_infomsgs'
INSERT INTO @inputbuffer
EXEC (@ExecStr)
SELECT @UserSQL = EventInfo FROM @inputbuffer
Begin Try
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, Rowversion)
SELECT @AuditTime, COALESCE(deleted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(),'dbo.o_choice', 'd', @UserSQL,
deleted.[o_choice_id],
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Oder Number for an Order Detail Line)
'[o_choice_id]', Convert(VARCHAR(50), Deleted.[o_choice_id]), deleted.Rowversion FROM deleted
WHERE deleted.[o_choice_id] is not null
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, Rowversion)
SELECT @AuditTime, COALESCE(deleted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(),'dbo.o_choice', 'd', @UserSQL,
deleted.[o_choice_id],
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Oder Number for an Order Detail Line)
'[company_id]', Convert(VARCHAR(50), Deleted.[company_id]), deleted.Rowversion FROM deleted
WHERE deleted.[company_id] is not null
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, Rowversion)
SELECT @AuditTime, COALESCE(deleted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(),'dbo.o_choice', 'd', @UserSQL,
deleted.[o_choice_id],
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Oder Number for an Order Detail Line)
'', Convert(VARCHAR(50), Deleted.), deleted.Rowversion FROM deleted
WHERE deleted.is not null
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, Rowversion)
SELECT @AuditTime, COALESCE(deleted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(),'dbo.o_choice', 'd', @UserSQL,
deleted.[o_choice_id],
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Oder Number for an Order Detail Line)
'[descript]', Convert(VARCHAR(50), Deleted.[descript]), deleted.Rowversion FROM deleted
WHERE deleted.[descript] is not null
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, Rowversion)
SELECT @AuditTime, COALESCE(deleted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(),'dbo.o_choice', 'd', @UserSQL,
deleted.[o_choice_id],
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Oder Number for an Order Detail Line)
'[m_type]', Convert(VARCHAR(50), Deleted.[m_type]), deleted.Rowversion FROM deleted
WHERE deleted.[m_type] is not null
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, Rowversion)
SELECT @AuditTime, COALESCE(deleted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(),'dbo.o_choice', 'd', @UserSQL,
deleted.[o_choice_id],
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Oder Number for an Order Detail Line)
'[amount]', Convert(VARCHAR(50), Deleted.[amount]), deleted.Rowversion FROM deleted
WHERE deleted.[amount] is not null
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, Rowversion)
SELECT @AuditTime, COALESCE(deleted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(),'dbo.o_choice', 'd', @UserSQL,
deleted.[o_choice_id],
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Oder Number for an Order Detail Line)
'[ch_sort_num]', Convert(VARCHAR(50), Deleted.[ch_sort_num]), deleted.Rowversion FROM deleted
WHERE deleted.[ch_sort_num] is not null
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, Rowversion)
SELECT @AuditTime, COALESCE(deleted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(),'dbo.o_choice', 'd', @UserSQL,
deleted.[o_choice_id],
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Oder Number for an Order Detail Line)
'[inactive_fl]', Convert(VARCHAR(50), Deleted.[inactive_fl]), deleted.Rowversion FROM deleted
WHERE deleted.[inactive_fl] is not null
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, Rowversion)
SELECT @AuditTime, COALESCE(deleted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(),'dbo.o_choice', 'd', @UserSQL,
deleted.[o_choice_id],
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Oder Number for an Order Detail Line)
'[system_fl]', Convert(VARCHAR(50), Deleted.[system_fl]), deleted.Rowversion FROM deleted
WHERE deleted.[system_fl] is not null
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, Rowversion)
SELECT @AuditTime, COALESCE(deleted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(),'dbo.o_choice', 'd', @UserSQL,
deleted.[o_choice_id],
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Oder Number for an Order Detail Line)
'[created_on_user_id]', Convert(VARCHAR(50), Deleted.[created_on_user_id]), deleted.Rowversion FROM deleted
WHERE deleted.[created_on_user_id] is not null
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, Rowversion)
SELECT @AuditTime, COALESCE(deleted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(),'dbo.o_choice', 'd', @UserSQL,
deleted.[o_choice_id],
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Oder Number for an Order Detail Line)
'[created_on_dt]', Convert(VARCHAR(50), Deleted.[created_on_dt]), deleted.Rowversion FROM deleted
WHERE deleted.[created_on_dt] is not null
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, Rowversion)
SELECT @AuditTime, COALESCE(deleted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(),'dbo.o_choice', 'd', @UserSQL,
deleted.[o_choice_id],
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Oder Number for an Order Detail Line)
'[last_updated_user_id]', Convert(VARCHAR(50), Deleted.[last_updated_user_id]), deleted.Rowversion FROM deleted
WHERE deleted.[last_updated_user_id] is not null
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, Rowversion)
SELECT @AuditTime, COALESCE(deleted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(),'dbo.o_choice', 'd', @UserSQL,
deleted.[o_choice_id],
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Oder Number for an Order Detail Line)
'[last_updated_dt]', Convert(VARCHAR(50), Deleted.[last_updated_dt]), deleted.Rowversion FROM deleted
WHERE deleted.[last_updated_dt] is not null
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, Rowversion)
SELECT @AuditTime, COALESCE(deleted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(),'dbo.o_choice', 'd', @UserSQL,
deleted.[o_choice_id],
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Oder Number for an Order Detail Line)
'[locked_by_user_id]', Convert(VARCHAR(50), Deleted.[locked_by_user_id]), deleted.Rowversion FROM deleted
WHERE deleted.[locked_by_user_id] is not null
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, Rowversion)
SELECT @AuditTime, COALESCE(deleted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(),'dbo.o_choice', 'd', @UserSQL,
deleted.[o_choice_id],
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Oder Number for an Order Detail Line)
'[locked_dt]', Convert(VARCHAR(50), Deleted.[locked_dt]), deleted.Rowversion FROM deleted
WHERE deleted.[locked_dt] is not null
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, Rowversion)
SELECT @AuditTime, COALESCE(deleted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(),'dbo.o_choice', 'd', @UserSQL,
deleted.[o_choice_id],
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Oder Number for an Order Detail Line)
'[CreatedBy]', Convert(VARCHAR(50), Deleted.[CreatedBy]), deleted.Rowversion FROM deleted
WHERE deleted.[CreatedBy] is not null
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, Rowversion)
SELECT @AuditTime, COALESCE(deleted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(),'dbo.o_choice', 'd', @UserSQL,
deleted.[o_choice_id],
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Oder Number for an Order Detail Line)
'[ModifiedBy]', Convert(VARCHAR(50), Deleted.[ModifiedBy]), deleted.Rowversion FROM deleted
WHERE deleted.[ModifiedBy] is not null
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, Rowversion)
SELECT @AuditTime, COALESCE(deleted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(),'dbo.o_choice', 'd', @UserSQL,
deleted.[o_choice_id],
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Oder Number for an Order Detail Line)
'[Created]', Convert(VARCHAR(50), Deleted.[Created]), deleted.Rowversion FROM deleted
WHERE deleted.[Created] is not null
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, Rowversion)
SELECT @AuditTime, COALESCE(deleted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(),'dbo.o_choice', 'd', @UserSQL,
deleted.[o_choice_id],
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Oder Number for an Order Detail Line)
'[Modified]', Convert(VARCHAR(50), Deleted.[Modified]), deleted.Rowversion FROM deleted
WHERE deleted.[Modified] is not null
INSERT dbo.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, SQLStatement, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, Rowversion)
SELECT @AuditTime, COALESCE(deleted.ModifiedBy, Suser_SName()), APP_NAME(), Host_Name(),'dbo.o_choice', 'd', @UserSQL,
deleted.[o_choice_id],
NULL, -- Row Description (e.g. Order Number)
NULL, -- Secondary Row Value (e.g. Oder Number for an Order Detail Line)
'[RowVersion]', Convert(VARCHAR(50), Deleted.[RowVersion]), deleted.Rowversion FROM deleted
WHERE deleted.[RowVersion] is not null
End Try
Begin Catch
DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;
SET @ErrorMessage = ERROR_MESSAGE();
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorState = ERROR_STATE();
RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState) with log;
End Catch
GO
ALTER TABLE [dbo].[o_choice] ENABLE TRIGGER [o_choice_Audit_Delete]
GO
EXEC sp_settriggerorder @triggername=N'[dbo].[o_choice_Audit_Delete]', @order=N'Last', @stmttype=N'DELETE'
GO
September 8, 2019 at 9:31 pm
For such a table I would consider checking for how many repetitions of the SQL exist - and if number is less than 20% of total records perhaps adding another table just for the SQL statement itself with 2 columns
hashkey - varbinary(20) - (as PK) - output of hashbytes('SHA1', EventInfo)
sqlstatement varchar(max) - would save considerable amount of space.
your code would become
select @Usersql = EventInfo
, @HashKey = hashbytes('SHA1', EventInfo)
from @InputBuffer
insert into #sqllookup
select @HashKey
, @Usersql
where not exists (select * from #sqllookup where hashkey = @HashKey)
and throughout the inserts onto the audit table you would use @hashkey instead of the sql itself (would require changing the datatype of existing table and a once off "move" the SQL to its new lookup table)
And potentially compress (zip CLR easy to build) the sql itself - can be done with either current table and with the table I mention above
September 8, 2019 at 10:39 pm
Heh... well, now we know why the Audit table has gotten so big. 😀 The audit triggers are auditing the "poor man's" auditing columns and attempts at "poor man's" pessimistic locking in the table on top of everything else and more.
IMHO, there's no need to make entries into the Audit table for any of the following columns...
created_on_user_id - Covered by the SysUser column in the Audit table during INSERTs
created_on_dt - Covered by the AuditDate column in the Audit table during INSERTs
last_updated_user_id - Covered by the SysUser column in the Audit table during UPDATEs and DELETEs
last_updated_dt - Covered by te AuditDate column in the Audit table during UPDATEs and DELETEs
In fact and considering the auditing triggers themselves and the purpose of the Audit table, I don't actually see a reason to have any of those 4 columns in any table never mind auditing their change in values. At the very least, auditing of those columns should be deleted from the audit triggers and the rows in the Audit table itself could be deleted with no loss of data because it's actually duplicated in the SysUser and AuditDate columns of the Audit table.
I won't get into the attempt at achieving pessimistic locking using the locked_by_user_id and locked_dt columns I'm seeing as an audit source in the dbo.o_choice column but I am thinking that it's a total waste of time to log changes to those column in the Audit table. I'd be real tempted to delete that information from the Audit table because it serves no additional Audit purpose.
I'd also delete all six of those columns from the audit triggers.
To add insult to injury, the UPDATE triggers then turn around an update the MODIFIED and MODIFIEDBY columns in the original table. That's even more unnecessary "Poor Man's" auditing. And, of course, that RowVersion+1 thing is another attempt at pessimistic locking and there are a whole lot of better ways to pull that off nowadays. I also understand that it would require a whole lot of code changes to make that change and so it probably won't be done but there's no need (IMHO) to even have the MODIFIED and MODIFIEDBY columns on the tables.
Second, and this is a killer performance problem for wider tables and still a major annoyance for even narrow tables, the UPDATE version of the audit triggers unconditionally compare every column (except the PK column) in the INSERTED and DELETED logical tables EVEN IF THE COLUMN BEING COMPARED HAS NOT BEEN UPDATED!
To fix that problem, each section of code in the UPDATE triggers that does such a comparison, needs to be wrapped in an IF UPDATE(column_name) BEGIN/END. It'll make UPDATES a whole lot faster when just a couple of columns are actually updated especially if you remove the auditing on the Created_on and last_updated columns. In fact, THIS IS THE MOST IMPORTANT CHANGE YOU COULD MAKE ON THE AUDIT TRIGGERS even if you did nothing else (and you should still consider doing those something-elses but I know it'll take time to convince other people).
See the following link for the official MS documentation of the UPDATE() trigger function. The example they use sucks (it prevents changes instead of just detecting them) but it's a decent example of how to us it in an IF block, like I suggested (one IF block per column being audited). Let me know if you have any questions there.
Last but not least, it makes no sense to have an identical copy of the SQL that caused an UPDATE on more than 1 row on every row added to the Audit especially if you update hundreds or thousands of rows in a single update. Again, this would be a big change but would go a very long way in keeping the size of the Audit table under control.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 8, 2019 at 11:12 pm
On the subject of capturing the SQL differently, Frederico's idea (which I missed because it took me a while to investigate and write the post above) is even better than what I was originally thinking of because the hash code is a much smaller comparison and can actually be indexed quite easily. It would definitely solve the issues of a single Insert/Update/Delete that had hundreds or thousands of rows involved. The space savings would be fairly well incredible.
It would (like the IF UPDATE() thing) require a change to the trigger generation code but that would be in a single place and easily controlled. Once in place, we'd just need to regenerate/replace the existing triggers with no changes in managed code, etc.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 16, 2019 at 3:00 am
Does the hash code compress the data? Can you reverse out the sqlstatement from the hash result?
September 16, 2019 at 7:24 am
Does the hash code compress the data? Can you reverse out the sqlstatement from the hash result?
No it does not reverse the sql statement - but by using a secondary table to hold the hash and the sql statement you can use the hash on your audit table as a key to retrieve the sql used as per my example.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply