Trigger overhead? 2

[Peager]

Can someone give me an idea as to how to figure out what the overhead is on a server due to triggers? I recently added a number of 'audit triggers' coded as follows:

CREATE TRIGGER [dbo].[UpdateDSPTRUCKAuditFields] ON [dbo].[DSPTRUCK]
    AFTER UPDATE
AS
    BEGIN
        SET NOCOUNT ON ;
        UPDATE  [dbo].[CodeMast]
        SET     Edited_By = (dbo.OperatorInitials())
               ,Edited_Time = dbo.McTimeStamp(GETDATE())
               ,Edited_Date =  dbo.MCDate(getdate())
        WHERE   Recnum IN (SELECT   Recnum
                           FROM     Inserted)
    END

The three functions being used probably don't help... OperatorInitals takes the UserId and looks up the user initials an a table. The two date functions just return the time or date from the system time.

I had about 15-20 of these triggers in the DB and all seemed to be going well..... this morning I added about 50 more and things came crawling to thier knees. Deadlocks all over the place....

These fields are supposed to be set by our application developers so thier use is a 'belt and suspenders' approach at best.

Is there some way of seeing what % of the server load is spent on triggers? Is there another approach that would work better?

Paul

 

[bborissov]

Just try to NOT use UDF in your triggers. Add ONE datetime field and store GETDATE() there. Do not split the date and time portions. Also use JOIN instead of IN ():

CREATE TRIGGER [dbo].[UpdateDSPTRUCKAuditFields] ON [dbo].[DSPTRUCK]
    AFTER UPDATE
AS
    BEGIN
        SET NOCOUNT ON ;
        UPDATE  CodeMast
        SET     Edited_By = (dbo.OperatorInitials())
               ,Edited_DateTime = GETDATE()
        FROM [dbo].[CodeMast] CodeMast
        INNER JOIN Inserted ON CodeMast.Recnum = Inserted.Recnum
    END

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

OperatorInitials is yet another function.

 

[bborissov]

Yes, but I don't know what this function do and how to avoid it

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Peager]

I have already started to replace OperatorInitials with USERID to avoid that UDF. I got stuck with the two field date/time structure due to legacy code.... I can push that issue.

Using the 'JOIN' instead of an 'IN' is new to me. Thank you so much for the tip.

 

[SQLSister]

Join is generally much faster than in especially if multiple records are being updated at once. You get kudos though for creating triggers that didn't assume only one record in the inserted table or try to loop through them!

"NOTHING is more important in a database than integrity." ESquared

 

[harryrich]

I can't give you anything directly constructive because, neither triggers nor user defined functions have given me much trouble on utilitization. But, I expect your problem is somehow due to contention for update and not resource utilization.

One obvious possibility is that you may have a some series of triggers which cascades in a circle.

The fact that you take a suspenders and belt approach puts you in the position of fighting with the apps for tables that you are both updating. A clear divsion of responsibility removing updates from the apps as you cover them with triggers would also reduce contention.

 

[Peager]

Some benchmarking results......
.... I found this VERY interesting.

		Audit Trigger Benchmarks.	
			
			
Elaps Diff  TriggerCode	                                %Slower

41.1  34.2  SET Edited_By = [dbo].[OperatorInitials]),
                Edited_Time = dbo.MCTimeStamp(GETDATE()),
                Edited_Date = dbo.MCDate(GETDATE()),
            WHERE ID IN (SELECT ID FROM Inserted)           496%

40.3  33.4  SET Edited_By = [dbo].[OperatorInitials](),
                Edited_Time = dbo.MCTimeStamp(GETDATE()),
                Edited_Date = dbo.MCDate(GETDATE()),  
            FROM test T INNER JOIN inserted I ON I.ID = T.ID   484%

37.5  30.6  SET Edited_By = [dbo].[OperatorInitials](),
                Edited_Date = dbo.MCDate(GETDATE())
            FROM test T INNER JOIN inserted I ON I.ID = T.ID   443%

37.3   30.4 SET Edited_By = [dbo].[OperatorInitials](),
                Edited_Date = GETDATE()  
            FROM test T INNER JOIN inserted I ON I.ID = T.ID   441%

08.9   02.0 SET Edited_By = USER_ID(),
                Edited_Date = dbo.MCDate(GETDATE())
            FROM test T INNER JOIN inserted I ON I.ID = T.ID    29%

06.9   00.0 SET edited_By = USER_ID(),
                Edited_Date = GETDATE()
            FROM test T INNER JOIN inserted I ON I.ID = T.ID	0%

For futher edification the UDF follow: (They ain't pretty but they work.)

CREATE FUNCTION [dbo].[MCDate] (@InDate DATETIME)
RETURNS DATETIME
AS BEGIN
    DECLARE @sHoldDate VARCHAR(23)
       ,@OutDate DATETIME  --return value
    SET @sHoldDate = CONVERT (VARCHAR,@InDate,21)
    SET @sHoldDate = LEFT(@sHoldDate,10)
    SET @sHoldDate = (@sHoldDate + ' 00:00:00.000')
    SET @OutDate = CONVERT (DATETIME,@sHoldDate,121)
    RETURN @OutDate
   END

CREATE FUNCTION [dbo].[MCTimeStamp] (@InDate DATETIME)
RETURNS VARCHAR(8)
AS BEGIN
    DECLARE @sTime VARCHAR(8) --return value
    DECLARE @sHoldDate VARCHAR(23)
    SET @sHoldDate = CONVERT (VARCHAR,@Indate,21)
    SET @sTime = SUBSTRING(@sHoldDate,12,8)
    RETURN @sTime
   END

ALTER FUNCTION [dbo].[OperatorInitials] ()
RETURNS CHAR(3)
AS BEGIN
    DECLARE @OperatorInitials CHAR(3)
    SELECT  @OperatorInitials = (Select Initials
                                 from   Operator
                                 WHERE  UserName = (Upper(REPLACE(SYSTEM_USER,'LDM\','')))
                                )
    RETURN @OperatorInitials
   END

 

[gmmastros]

Clearly you were able to get it from 41.1 to 6.9 (congratulations, by the way). What are the units for your test? Milliseconds or seconds? Also, how many rows were affected by this trigger?

If your Id column is not indexed, you would likely get further performance improvements by creating an index. If you're not sure about the index, then run this (and post the results here).

sp_helpindex 'YourTableNameHere'



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Peager]

The table had 50k rows of random data in it when I started.... Just an ID primary key.

I wasn't really trying to go for total speed, just the delta... how much slower/faster. I updated all 50k records twice in each section. The sripts I used follow:

USE [MC_Live]
GO
/****** Object:  Trigger [dbo].[TestUpdate]    Script Date: 05/19/2009 12:41:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE #Results(
	Elapsed DATETIME,
	TriggerCode VARCHAR(MAX))
GO
IF OBJECT_ID('dbo.TestUpdate') IS NOT NULL
	DROP TRIGGER dbo.TestUpdate
	
GO	
CREATE TRIGGER [dbo].[TestUpdate]
   ON  [dbo].[test]
   AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
    UPDATE  [Test]
    SET     Edited_By = [dbo].[OperatorInitials]()
           ,Edited_Time = dbo.MCTimeStamp(GETDATE())
           ,Edited_Date = dbo.MCDate(GETDATE())
    WHERE   ID IN (SELECT ID FROM Inserted)
END
GO

INSERT INTO #Results
EXEC TestTrigger 'SET Edited_By = [dbo].[OperatorInitials](),
Edited_Time = dbo.MCTimeStamp(GETDATE()),
Edited_Date = dbo.MCDate(GETDATE()),
WHERE ID IN (SELECT ID FROM Inserted)'
GO

ALTER TRIGGER [dbo].[TestUpdate]
   ON  [dbo].[test]
   AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
    UPDATE  [Test]
    SET     Edited_By = [dbo].[OperatorInitials]()
           ,Edited_Time = dbo.MCTimeStamp(GETDATE())
           ,Edited_Date = dbo.MCDate(GETDATE())
    FROM test T INNER JOIN inserted I ON I.ID = T.ID
END
GO

INSERT INTO #Results
EXEC TestTrigger 'SET Edited_By = [dbo].[OperatorInitials](),
Edited_Time = dbo.MCTimeStamp(GETDATE()),
Edited_Date = dbo.MCDate(GETDATE()),
FROM test T INNER JOIN inserted I ON I.ID = T.ID'
GO
ALTER TRIGGER [dbo].[TestUpdate]
   ON  [dbo].[test]
   AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
    UPDATE  [Test]
    SET     Edited_By = [dbo].[OperatorInitials]()
           ,Edited_Date = dbo.MCDate(GETDATE())
    FROM test T INNER JOIN inserted I ON I.ID = T.ID
END
GO

INSERT INTO #Results
EXEC TestTrigger  'SET Edited_By = [dbo].[OperatorInitials](),
Edited_Date = dbo.MCDate(GETDATE())
FROM test T INNER JOIN inserted I ON I.ID = T.ID'
GO

ALTER TRIGGER [dbo].[TestUpdate]
   ON  [dbo].[test]
   AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
    UPDATE  [Test]
    SET     Edited_By = USER_ID()
           ,Edited_Date = dbo.MCDate(GETDATE())
    FROM test T INNER JOIN inserted I ON I.ID = T.ID
END
GO

INSERT INTO #Results
EXEC TestTrigger  'SET Edited_By = USER_ID(),
Edited_Date = dbo.MCDate(GETDATE())
FROM test T INNER JOIN inserted I ON I.ID = T.ID'
GO

ALTER TRIGGER [dbo].[TestUpdate]
   ON  [dbo].[test]
   AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON;

    UPDATE  [Test]
    SET     Edited_By = [dbo].[OperatorInitials]()
           ,Edited_Date = GETDATE()
    FROM test T INNER JOIN inserted I ON I.ID = T.ID
END
GO

INSERT INTO #Results
EXEC TestTrigger  'SET Edited_By = [dbo].[OperatorInitials](),
Edited_Date = GETDATE()
FROM test T INNER JOIN inserted I ON I.ID = T.ID'
GO

ALTER TRIGGER [dbo].[TestUpdate]
   ON  [dbo].[test]
   AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
    UPDATE [Test]
      SET edited_By = USER_ID(),
		Edited_Date = GETDATE()
	FROM test T INNER JOIN inserted I ON I.ID = T.ID
END
GO

INSERT INTO #Results
EXEC TestTrigger  'SET edited_By = USER_ID(), 
Edited_Date = GETDATE() 
FROM test T INNER JOIN inserted I ON I.ID = T.ID'
GO

SELECT * FROM [#Results] AS R
ORDER BY [Elapsed] DESC

IF OBJECT_ID('dbo.TestUpdate') IS NOT NULL
	DROP TRIGGER dbo.TestUpdate
	
GO	
DROP TABLE #Results
GO

The actual update procedure follows:

ALTER PROCEDURE [dbo].[TestTrigger]
	@TestString VARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @Start DATETIME,
			@End DATETIME
	SET @Start = GETDATE()
	UPDATE test
	SET col1 = CONVERT(CHAR(12),ID)
	UPDATE test
	SET col1 = Edited_by
	SELECT CONVERT(VARCHAR(20),GETDATE() - @Start,114) AS Elapsed, @TestString AS [Trigger Code]
END

Again, the code ain't pretty and I wouldn't put it into production but it told me what I wanted to know.

 

[gmmastros]

Ok. I get it.

What you have discovered (and Boris correctly pointed out) is that the overhead of calling a user defined function can have drastic results on performance.

The point that I was trying to make is that indexes can also have a big effect on performance. Most people incorrectly think that indexes only affect performance of select queries. This is clearly not true. In this case, you are joining the inserted table with a real table. If the join column is indexed, your performance will likely improve.

In your test, you were updating all the rows in the table, so you wouldn't see performance improvements by indexing the join column. In your real table, when you only have a small number of rows (usually) that are updated, the performance difference can be more dramatic. Of course, if you are already joining based on the primary key column (which is indexed by default), then you won't benefit from an additional index.

Lastly, I would like to point on this little known phenomenon. You already know about triggers and how they are fired. What most people don't know is that a trigger will fire even if the data hasn't changed. For example:

Update TableName
Set    IntegerColumn = IntegerColumn + 0

This will not *actually* change the data in the table (any number + 0 = original number). However, this will cause the trigger to fire once. The inserted table will have as many rows as your table has. Instead, if you do....

Update TableName
Set    IntegerColumn = IntegerColumn + 0
Where  IntegerColumn <> IntegerColumn + 0

Your where clause will prevent any rows from updating, but more importantly, the trigger will not fire either.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Peager]

Hmmmm.... hard won gems of wisdom you are sharing here.... Thanks for the tip.