January 6, 2009 at 10:08 am
I have an UPDATE trigger that every morning generates about 3 GB of version-store data in the tempdb of my sql instance. Code shown below.
The trigger's function is to populate 2 audit columns of the updated table ([dbo].[Amt]) - updDate and updBy - with the time of the update and the name of the user who effected the change.
Is there a more efficient way of doing this than using a trigger?
Alternatively, is there anything I can do to optimize the existing code?
CREATE TRIGGER [TG_UPD_Amt]
ON [dbo].[Amt]
FORUPDATE
AS
SET NOCOUNT ON
DECLARE @ActionType char(1)
SELECT @ActionType = CASE
WHEN EXISTS( SELECT null FROM inserted ) THEN 'U'
WHEN EXISTS( SELECT null FROM deleted ) THEN 'D'
ELSE 'X'
END
IF @ActionType = 'X'
BEGIN
RETURN
END
-- if it is an update, save the 'who' and the 'when'
IF @ActionType = 'U'
BEGIN
UPDATE D
SET D.[updDate] = getdate()
,D.[updBy] = system_user
FROM [inserted] AS i
INNER JOIN [dbo].[Amt] AS D ON i.[AmtOID] = D.[AmtOID]
END
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 6, 2009 at 10:22 am
Marios Philippopoulos (1/6/2009)
DECLARE @ActionType char(1)SELECT @ActionType = CASE
WHEN EXISTS( SELECT null FROM inserted ) THEN 'U'
WHEN EXISTS( SELECT null FROM deleted ) THEN 'D'
ELSE 'X'
END
IF @ActionType = 'X'
BEGIN
RETURN
END
What's the point of all that? It's an update trigger. The action cannot be delete or insert as the trigger only fires on update.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2009 at 10:28 am
GilaMonster (1/6/2009)
Marios Philippopoulos (1/6/2009)
DECLARE @ActionType char(1)SELECT @ActionType = CASE
WHEN EXISTS( SELECT null FROM inserted ) THEN 'U'
WHEN EXISTS( SELECT null FROM deleted ) THEN 'D'
ELSE 'X'
END
IF @ActionType = 'X'
BEGIN
RETURN
END
What's the point of all that? It's an update trigger. The action cannot be delete or insert as the trigger only fires on update.
That's true, this code is superfluous and should be removed. It's not where the heavy lifting is being done though.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 6, 2009 at 10:57 am
you need to track changes in the TEMPDB? maybe you mentioned the wrong db?
Are you sure you need to do that? all the tables would get dropped at the stop and start of the Server instance anyway.
Since SQL server would create temp tables itself automatically if it needs to merge any data via joins for queries, you'd get a lot of stuff that was done by spids less than 50.
Fro end users, How does tracking changes to tables that get destroyed on the end of a connection help with an Audit?
Lowell
January 6, 2009 at 11:14 am
Lowell (1/6/2009)
you need to track changes in the TEMPDB? maybe you mentioned the wrong db?
In 2005/2008, the inserted and deleted tables are materialised from the row version store that's kept in TempDB. Same thing as snapshot isolation uses.
Hence triggers can have a serious impact on TempDB if a lot of rows are affected.
Marios Philippopoulos (1/6/2009)
Is there a more efficient way of doing this than using a trigger?
The only other way requires that all access to the table is via known stored procedures. Is that the case?
How many rows are been updated at once to generate that much row-version information?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2009 at 12:18 pm
If you can guarantee that the only way for this data to be updated is by procs, then including the update date and user in the procswould be better, since you'd have half the row versions. (One version for update, one version for audit update.)
Personally, I don't like to store that kind of data in the parent table. Yeah, so you know who was the last person to update a row, and you know when they did it, but you don't know WHAT they updated, unless you have some other means of auditing that. If you have that kind of auditing going on, then you don't need the data in the audited table.
When I need that kind of audit data, I use trace files and audit triggers that insert into a separate audit database.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2009 at 12:19 pm
GilaMonster (1/6/2009)
Marios Philippopoulos (1/6/2009)
Is there a more efficient way of doing this than using a trigger?The only other way requires that all access to the table is via known stored procedures. Is that the case?
How many rows are been updated at once to generate that much row-version information?
Yes, it's most likely access to the table is done through a small set of stored procedures.
I will try to find out.
The number of records updated in the table at that single point in time is 2,918,968.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 6, 2009 at 12:31 pm
GSquared (1/6/2009)
If you can guarantee that the only way for this data to be updated is by procs, then including the update date and user in the procswould be better, since you'd have half the row versions. (One version for update, one version for audit update.)Personally, I don't like to store that kind of data in the parent table. Yeah, so you know who was the last person to update a row, and you know when they did it, but you don't know WHAT they updated, unless you have some other means of auditing that. If you have that kind of auditing going on, then you don't need the data in the audited table.
When I need that kind of audit data, I use trace files and audit triggers that insert into a separate audit database.
Unfortunately, the present table structure is something I have to live with; we have had it in place for a while, and it's been decided by forces way above me... 😎
I don't follow your point about getting half the row versions if the update date and user is calculated in the sproc.
Can you pls elaborate?
Let me see if I understand this. Is this what you mean?
...
SET @updDate = GETDATE();
SET @updBy = system_user;
UPDATE tblName
SET
col1 = ...
, col2 = ...
, ...
,
, updDate = @updDate
, updBy = @updBy
WHERE ...
...
In that case, we don't even need the trigger.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 6, 2009 at 12:45 pm
Regarding my earlier posting:
Having the trigger takes the onus away from the developers of remembering to include the right code in all their sproc code. The trigger is part of the "database infrastructure" acting behind the scenes, so the developer can concentrate on other aspects of the logic.
At least that is the rationale I was told behind using the trigger - there is a performance penalty associated with it though, it seems...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 6, 2009 at 12:57 pm
GilaMonster (1/6/2009)
Marios Philippopoulos (1/6/2009)
DECLARE @ActionType char(1)SELECT @ActionType = CASE
WHEN EXISTS( SELECT null FROM inserted ) THEN 'U'
WHEN EXISTS( SELECT null FROM deleted ) THEN 'D'
ELSE 'X'
END
IF @ActionType = 'X'
BEGIN
RETURN
END
What's the point of all that? It's an update trigger. The action cannot be delete or insert as the trigger only fires on update.
I think all these lines can be replaced by the following:
IF @@rowcount = 0 RETURN;
I have followed up on this with our devs.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 6, 2009 at 1:06 pm
In addition your trigger should only perform the update IF the columns on the "deleted" and "inserted" tables have different values. That way you will be minimizing the impact of the update.
* Noel
January 6, 2009 at 1:12 pm
noeld (1/6/2009)
In addition your trigger should only perform the update IF the columns on the "deleted" and "inserted" tables have different values. That way you will be minimizing the impact of the update.
Interesting...
Can you explain a bit further? What is the meaning of the inserted and deleted tables having different values, and what would the code look like?
Thanks!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 6, 2009 at 1:35 pm
On the point of halving the row versions:
Let's say you have a table with people's names in it, and LastUpdate (datetime) and LastUpdateBy (varchar) columns.
So, you issue a command to the database to:
update dbo.MyTable
set LastName = 'Squared'
where ID = 5
So, you end up with a row version record for the last name change.
Let's say you add a trigger to that, which updates the LastUpdate and LastUpdateBy columns.
You will have the version where the last name is changed, AND you will have the row version where the other two columns are changed. If I'm not mistaken, that's how it works with row versions.
That means each update gets its own row version, and a row version for the audit update. Twice as many.
On the point of making sure the inserted and deleted tables are different, a trigger can look like this:
update MyTable
set LastUpdate = getdate(), LastUpdateBy = user
from dbo.MyTable
inner join inserted
on MyTable.ID = inserted.ID
inner join deleted
on MyTable.ID = deleted.ID
and (inserted.FirstName != deleted.FirstName
or
inserted.LastName != deleted.LastName)
In the second part of the join to deleted, you include all the columns you are auditing, and if LastName is updated from 'Squared' to 'Squared', with no other change, then it won't get included in the update command from the trigger.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2009 at 1:39 pm
GSquared (1/6/2009)
On the point of halving the row versions:Let's say you have a table with people's names in it, and LastUpdate (datetime) and LastUpdateBy (varchar) columns.
So, you issue a command to the database to:
update dbo.MyTable
set LastName = 'Squared'
where ID = 5
So, you end up with a row version record for the last name change.
Let's say you add a trigger to that, which updates the LastUpdate and LastUpdateBy columns.
You will have the version where the last name is changed, AND you will have the row version where the other two columns are changed. If I'm not mistaken, that's how it works with row versions.
That means each update gets its own row version, and a row version for the audit update. Twice as many.
On the point of making sure the inserted and deleted tables are different, a trigger can look like this:
update MyTable
set LastUpdate = getdate(), LastUpdateBy = user
from dbo.MyTable
inner join inserted
on MyTable.ID = inserted.ID
inner join deleted
on MyTable.ID = deleted.ID
and (inserted.FirstName != deleted.FirstName
or
inserted.LastName != deleted.LastName)
In the second part of the join to deleted, you include all the columns you are auditing, and if LastName is updated from 'Squared' to 'Squared', with no other change, then it won't get included in the update command from the trigger.
Great, thank you for the input!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 6, 2009 at 2:11 pm
GSquared (1/6/2009)
On the point of making sure the inserted and deleted tables are different, a trigger can look like this:
update MyTable
set LastUpdate = getdate(), LastUpdateBy = user
from dbo.MyTable
inner join inserted
on MyTable.ID = inserted.ID
inner join deleted
on MyTable.ID = deleted.ID
and (inserted.FirstName != deleted.FirstName
or
inserted.LastName != deleted.LastName)
In the second part of the join to deleted, you include all the columns you are auditing, and if LastName is updated from 'Squared' to 'Squared', with no other change, then it won't get included in the update command from the trigger.
Won't this SQL statement become very expensive if there are, say, 20 potential columns being updated in the table?
I'm focusing on the last part of the SQL where the inserted and deleted table columns are being compared.
Won't there be a tipping point at which using the trigger may be preferable? Thinking out loud here...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply