December 14, 2006 at 9:07 am
SQL SERVER 2000 - Triggers
I'm seeing in the Execution Plan that Inserted and Deleted Pseudo-Tables I/O Cost depends on the size of the Table where the trigger is located.
Is this true? Why it depends in the size? I undestand that these pseudo-tables are located in the Log File then how the size (numer of rows) of the original table affects the access to INSERTED?
No matter if INSERTED has only one record the I/O Cost is similar to run a SCAN on the original table with hundreds of row.
Could anybody explainme how this INSERTED & DELETED table in triggers are related to the Original Table?
Thanks
December 15, 2006 at 4:34 am
ah ... triggers and performance .. now there's an oxymoron !
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 18, 2007 at 3:27 am
I'm having the same issue as Luis. OK, we shouldn't be using triggers but tell that to the VP who bought a third-party system without consulting the DBAs. Even worse, the trigger has a cursor in it! EEEkkk...
Anyway, when I run the actual execution plan the IO on the select from Inserted is 7 and the sub tree cost is also 7. The number of bytes is 118. The table is fairly narrow (stored telephone details), with a few million rows.
Can anyone explain why the IO for the select from Inserted is so high?
Cheers,
Wayne
January 18, 2007 at 5:33 am
Got anything more usefull to offer Collin?
January 18, 2007 at 6:02 am
I ran a select * from the table the trigger belonged to, and the IO for select from the Inserted table in the trigger was the same as the clustered index scan for the select *.
I then ran a profiler trace, and the duration and IO for the select from Inserted where both zero.
I'm thinking there may be a bug in the execution plan?
January 18, 2007 at 6:09 am
SQL Server creates the pseudo inserted and deleted tables by reading the changes from the transaction log.
Performance is therefore going to be affected by the number of changes it has to read from the log, and the overall performance of your transaction log.
January 18, 2007 at 6:35 am
ah, from the log not memory...
the txn log is on a seperate drive, and is optimised for performance. only one row was affected, so I would expect the IO to be tiny. ?? It seems the IO is the same as a clustered index scan for the entire table.
another reason for triggers to burn in a fiery place not to be mentioned here...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply