Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Costly update trigger -70,000,000 logical reads for 30,000 rows updated!


Costly update trigger -70,000,000 logical reads for 30,000 rows updated!

Author
Message
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47273 Visits: 44392
Nested triggers means that an update to a table in a trigger can fire triggers on that table, providing it's not the same table. So, say you have Table1 that has an update trigger that inserts into table2. Table2 has an insert trigger that nserts into table3. If nested triggers is on and you update table1, then the trigger on table1 will insert into table2, will fire that trigger and will insert into tbl3. If it's off, the insert trigger on table2 won't fire.

Recursive trigger controls if a trigger may fire itself due to data changes. If it's off, your trigger will not be called due to the updates that it does.


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Marios Philippopoulos
Marios Philippopoulos
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1954 Visits: 3740
GilaMonster (2/2/2009)
Nested triggers means that an update to a table in a trigger can fire triggers on that table, providing it's not the same table. So, say you have Table1 that has an update trigger that inserts into table2. Table2 has an insert trigger that nserts into table3. If nested triggers is on and you update table1, then the trigger on table1 will insert into table2, will fire that trigger and will insert into tbl3. If it's off, the insert trigger on table2 won't fire.

Recursive trigger controls if a trigger may fire itself due to data changes. If it's off, your trigger will not be called due to the updates that it does.


Thanks, so that means I'm OK from that aspect, ie. this trigger will not fire itself repeatedly, since my db setting is off.

That cleared it up.

From http://msdn.microsoft.com/en-us/library/ms190739(SQL.90).aspx

An AFTER trigger does not call itself recursively unless the RECURSIVE_TRIGGERS database option is set.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5970 Visits: 8313
Other than the ol' trigger-firing-itself-due-to-update-inside-trigger-ploy, 2 other questions:

1) any OTHER triggers on this table?

2) is the join column a primary key or unique index value? Looks like cartesian product if not.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47273 Visits: 44392
TheSQLGuru (2/2/2009)
2) is the join column a primary key or unique index value? Looks like cartesian product if not.


Agreed. That's one reason I want see the actual exec plan, see what the row counts really are at various points of the query.


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Marios Philippopoulos
Marios Philippopoulos
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1954 Visits: 3740
TheSQLGuru (2/2/2009)
Other than the ol' trigger-firing-itself-due-to-update-inside-trigger-ploy, 2 other questions:

1) any OTHER triggers on this table?

2) is the join column a primary key or unique index value? Looks like cartesian product if not.



Yes, that's the only trigger on this table.

The join column is a unique clustered primary key.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Marios Philippopoulos
Marios Philippopoulos
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1954 Visits: 3740
Here is the "actual" exec plan obtained from a server-side trace (see attached).

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Attachments
UpdTable_Trigger.zip (11 views, 2.00 KB)
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5970 Visits: 8313
Marios Philippopoulos (1/31/2009)
I have a trigger that appears to execute a very large number of logical reads; on one occasion it has hit 73,421,728 logical reads!

There are two puzzling aspects about this:

(1) Only about 30,000 rows are being updated in the base table (as can be seen in the exec plan)

(2) The dominant operator (86% of total workload) is an index seek on an index that contains a seemingly unrelated column (a column that does not appear anywhere in the trigger body).

I have captured the number of logical reads and the execution plan by polling the sys.dm_db_exec_requests DMV once a minute.

How can such a huge number of logical reads of the trigger be reconciled to the relatively modest number of rows updated in the underlying table?


Given the ACTUAL plan, I can't see what your issues are with the number of reads:

1) The clustered index update is 1.46M rows, NC index update 2.93M rows.

2) You are doing a table scan of 1.46M row inserted and 39.9M row updtable

3) You are hash matching the above

4) then doing a sort of the hash result

No idea where your statement of only updating 30K rows is coming from, but it is irrelevant. THAT query you gave the plan for is a beast and that is all there is to it. Smile

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Marios Philippopoulos
Marios Philippopoulos
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1954 Visits: 3740
TheSQLGuru (2/3/2009)
Marios Philippopoulos (1/31/2009)
I have a trigger that appears to execute a very large number of logical reads; on one occasion it has hit 73,421,728 logical reads!

There are two puzzling aspects about this:

(1) Only about 30,000 rows are being updated in the base table (as can be seen in the exec plan)

(2) The dominant operator (86% of total workload) is an index seek on an index that contains a seemingly unrelated column (a column that does not appear anywhere in the trigger body).

I have captured the number of logical reads and the execution plan by polling the sys.dm_db_exec_requests DMV once a minute.

How can such a huge number of logical reads of the trigger be reconciled to the relatively modest number of rows updated in the underlying table?


Given the ACTUAL plan, I can't see what your issues are with the number of reads:

1) The clustered index update is 1.46M rows, NC index update 2.93M rows.

2) You are doing a table scan of 1.46M row inserted and 39.9M row updtable

3) You are hash matching the above

4) then doing a sort of the hash result

No idea where your statement of only updating 30K rows is coming from, but it is irrelevant. THAT query you gave the plan for is a beast and that is all there is to it. Smile



It is a beast, no doubt.

The 30k-record number came from the estimated-plan info, which - clearly - is hugely inaccurate.

The developers have had enough of my whining, and they are eliminating the trigger alltogether.

They will embed the update code directly in the sprocs where the base table is itself updated.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
noeld
noeld
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6320 Visits: 2048
They will embed the update code directly in the sprocs where the base table is itself updated.

WISE decision!


* Noel
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search