Tony Rogerson's ramblings on SQL Server

Did you know that when you use the INSERTED and DELETED system materialised tables in a FOR (AFTER) trigger they are actually materialised from the transaction log (LDF file). Why is this bad? Transaction log writing is sequential in nature and for optimum performance no other processes should be writing to the disk the transaction log is on (even other transaction logs for other databases), that way the disk head will be in exactly the correct position for the next write thereby reducing the latency for disk writes thereby allowing your transaction to commit faster.

But, did you also know that ALTER TABLE <blah> DISABLE TRIGGER <blah> only disables the functionality of the trigger, something else is going on that causes the same underlying additional writes to the transaction log which is the overhead of using the trigger, for instance I have a straight UPDATE, without the trigger it writes 843Kbytes to the transaction log, with the trigger it writes 80,028Kbytes – yes, that’s a tad more! Even disabling the trigger still causes the 80Mbytes to be written to the transaction log.

Note: I’m keeping this entry purely SQL Server 2000 (SP4 and SP4 with 2187 applied) tested on multiple servers; I’ll update for 2005 shortly, 2005 should use tempdb instead because of the version store.

Here is the script:-

Set up:-

CREATE DATABASE [play] ON  PRIMARY

( NAME = N'play', FILENAME = N'f:\MSSQL\MSSQL\data\play.mdf' , SIZE = 512000KB , FILEGROWTH = 10240KB )

 LOG ON

( NAME = N'play_log', FILENAME = N'f:\MSSQL\MSSQL\data\play_log.ldf' , SIZE = 512000KB , FILEGROWTH = 10240KB )

GO

EXEC dbo.sp_dbcmptlevel @dbname=N'play', @new_cmptlevel=80

GO

ALTER DATABASE [play] SET RECOVERY FULL

GO

create table mytest (

    id  int not null    identity primary key clustered,

    trade_quantity  int not null,

    pad_row char(4000) not null

)

create table summary (

    trade_quantity int not null

)

insert summary values( 0 )

set nocount on

declare @i int

set @i = 1

while @i <= 10000

begin

    insert mytest ( trade_quantity, pad_row ) values( @i, 's' )

    set @i = @i + 1

end

go

Ok, now the fun part; first lets test a straight update without the trigger.

select *

from ::fn_virtualfilestats( 20, 2 )

go

update mytest

    set trade_quantity = trade_quantity + 1,

        pad_row = cast( trade_quantity as char(4000) )

go

select *

from ::fn_virtualfilestats( 20, 2 )

go

Now, remember to substitute 20 with DB_ID() !

Results:-

--  NumberReads = 0

--  NumberWrites= 15

--  BytesWritten= 843KBytes

--  BytesRead   = 0

That’s good, we have only written to the LDF.

Now the trigger code, this is common logic, it updates a summary table; note – this additional write and materialisation from the LDF would also be there even if you are just querying the INSERTED and DELETED tables aka referential integrity triggers.

create trigger trg_test on mytest FOR update

as

begin

    update summary

        set trade_quantity = ( trade_quantity -

                             ( select sum( trade_quantity )

                               from deleted ) )

                           + ( select sum( trade_quantity )

                               from inserted )

end

go

And now the results…

select *

from ::fn_virtualfilestats( 20, 2 )

go

update mytest

    set trade_quantity = trade_quantity + 1,

        pad_row = cast( trade_quantity as char(4000) )

go

select *

from ::fn_virtualfilestats( 20, 2 )

go

--  NumberReads = 1344

--  NumberWrites= 1373

--  BytesWritten=  80,084KBytes

--  BytesRead   = 157,958KBytes

Look, we have had to do 157Mbytes of read FROM the LDF but also the write requirement has now grown from 843Kbytes to an astonishing 80Mbytes!

Now disable the trigger and try the test again…

alter table mytest disable trigger trg_test

go

select *

from ::fn_virtualfilestats( 20, 2 )

go

update mytest

    set trade_quantity = trade_quantity + 1,

        pad_row = cast( trade_quantity as char(4000) )

go

select *

from ::fn_virtualfilestats( 20, 2 )

go

--  NumberReads = 8

--  NumberWrites= 1355

--  BytesWritten= 80,082KBytes

--  BytesRead   = 32KBytes

The number of reads and bytes read has dramatically reduced now, but look at the number of bytes written – its still 80Mbytes! Stone me J I was expecting bytes written to be back to 843Kbytes!!

Lets drop the trigger and see what happens…

drop trigger trg_test

go

select *

from ::fn_virtualfilestats( 20, 2 )

go

update mytest

    set trade_quantity = trade_quantity + 1,

        pad_row = cast( trade_quantity as char(4000) )

go

select *

from ::fn_virtualfilestats( 20, 2 )

go

--  NumberReads = 0

--  NumberWrites= 15

--  BytesWritten= 843KBytes

--  BytesRead   = 0KBytes

Yep, back to normal now.

The amount of data written varies because in the non-trigger situation SQL Server is clever enough to only write to the log what needs to be changed, so it doesn't write the whole row, that is born out by running this statement multiple times, the first causes more writes because it has more 'changes' to do.

When you have a trigger, because you may require INSERTED and DELETED system materialised tables then it has to put down the whole row to the transaction log (I think it's worse than that, it needs to put down the old and new rows to the log), this is because the INSERTED and DELETED tables are in column terms a copy of the base table, they do only contain the rows being processed, but you start to see why the trigger is so much more hefty.

Why disabling the trigger is not giving the minimalistic behaviour I’m not sure, there must be some internal storage engine requirement because the trigger is there; but to summarise – if you are using triggers please be aware of the above behaviour; if you have long running batch jobs that use triggers that you disable before commencing it is best to drop the trigger first rather than disabling it.

Also - be aware of things that use triggers, for instance merge replication!